sql left outer join with a constraining column

Question

Here is the SQL, 'aal_county_zip' has entry for 2 zipcodes whereas 'us_zip' has 15 zipcodes. The requirement is to get 15 rows with only 2 rows having data from 'aal_county_zip'. It works like a normal join. How can I make change the SQL/Table structure to make this work. I also want to add the condition that is commented below.

SELECT DISTINCT a.zcta5ce10 AS zipcode, 
                c.report_year, 
                c.aal 
FROM   aal_county_zip c 
       RIGHT OUTER JOIN us_zip a 
                     ON ( c.zip = a.zcta5ce10 ) 
WHERE  Lower(c.name) = Lower('alachua') 
--and c.report_year=2009 
ORDER  BY c.report_year DESC

Please explain what do you mean by "does not work" (not enough results, too many results, some rows are incorrect, etc.) — Sergey Kalinichenko, Jan 17 '14 at 10:17

score 1 · Accepted Answer · answered Jan 17 '14 at 11:01

The WHERE Lower(c.name) = Lower('alachua') in your query turns the outer join into an inner join, since it prevents c.name from being NULL.

Consider using a left join instead, as they're often more natural to write. And in any event, apply that condition to the join clause rather than to the where clause, so as to avoid turning it into an inner join.

Borrowing and amending @dasblinkenlight's query:

SELECT DISTINCT
    a.zcta5ce10 AS zipcode
,   c.report_year
,   c.aal 
FROM  us_zip a
LEFT OUTER JOIN aal_county_zip c
      ON c.zip = a.zcta5ce10
     AND c.report_year=2009 
     AND LOWER(c.name) = LOWER('alachua') 
ORDER BY c.report_year DESC

That should fix your "only two rows returned" problem. That said, the query is likely missing some additional criteria (and ordering criteria) on us_zip.

@patb23 Which is weird in a sense, since you _claimed_ it worked with that line as it was; you were complaining about the commented line. Please test and descrive your questions better in the future. — SQB, Jan 17 '14 at 12:08

score 0 · Answer 2 · answered Jan 17 '14 at 10:24

SELECT DISTINCT a.zcta5ce10 AS zipcode, 
                c.report_year, 
                c.aal 
FROM   aal_county_zip c 
       RIGHT OUTER JOIN us_zip a 
                     ON ( c.zip = a.zcta5ce10 ) 
WHERE  Lower(c.name) = Lower('alachua') 
  AND  COALESCE(c.report_year, 2009)=2009
ORDER  BY c.report_year DESC

or

SELECT DISTINCT a.zcta5ce10 AS zipcode, 
                c.report_year, 
                c.aal 
FROM   aal_county_zip c 
       RIGHT OUTER JOIN us_zip a 
                     ON ( c.zip = a.zcta5ce10 AND c.report_year=2009) 
WHERE  Lower(c.name) = Lower('alachua') 
ORDER  BY c.report_year DESC

You're doing a RIGHT OUTER JOIN, so your first table, aal_county_zip, may contain nulls. So either you account for those nulls by using COALESCE, or you make it part of the join condition.

Both gives me the same result like of a normal join (returning 2 rows). 'aal_county_zip' has only data for 2 zips whereas there are 15 zipcodes in 'us_zip', just noticed that my outer join(with commented line) is not correct. — patb23, Jan 17 '14 at 10:36

sql left outer join with a constraining column

2 Answers2