SELECT D.* 
FROM Doctors D
INNER JOIN ZipCodes Z ON
    (D.MainZip IS NOT NULL AND D.MainZip = Z.ZipCode) OR 
    (D.MainZip IS NULL AND D.AlternateZip = Z.ZipCode)

DECLARE @Doctors TABLE (name VARCHAR(10), MainZip VARCHAR(5), AlternateZip VARCHAR(5))

INSERT INTO @Doctors
SELECT 'Dr. Dwain', '12345', NULL
UNION ALL SELECT 'Dr. Jeff', NULL, '12346'
UNION ALL SELECT 'Dr. Chris', '12345', '12347'
UNION ALL SELECT 'Dr. Paul', '22222', '33333'

DECLARE @ZipCodes TABLE (ZipCode VARCHAR(5))

INSERT INTO @ZipCodes
SELECT '12345' UNION ALL SELECT '12346' UNION ALL SELECT '12347'

-- Returns 2 results of Dr. Chris
SELECT D.* 
FROM @Doctors D
INNER JOIN @ZipCodes Z 
ON D.MainZip = Z.ZipCode
OR D.AlternateZip = Z.ZipCode