Quick Left Join Question

Question

I have this straight forward query:

SELECT f.id, f.format_type, h.newspaper_id
FROM newspapers_formats f
LEFT JOIN newspapers_formats_held h
ON (f.id = h.format_id)

This returns all format_types, format ids, and the newspaper id that we have for that format. What I need is to then limit it to only newspaper_id = x, however, if I do this with a WHERE, like this:

SELECT f.id, f.format_type, h.newspaper_id
FROM newspapers_formats f
LEFT JOIN newspapers_formats_held h
ON (f.id = h.format_id)
WHERE h.newspaper_id=3

Then I'm only getting the formats that we have in that newspaper. I still need all formats, regardless if that newspaper has it.

Hopefully this makes sense. Ask for clarification if needed.

Answer 1

You can change the query to

SELECT f.id, f.format_type, h.newspaper_id
    FROM newspapers_formats f
    LEFT JOIN newspapers_formats_held h
    ON (f.id = h.format_id)
    AND h.newspaper_id=3

Answer 2

SELECT f.id, f.format_type, h.newspaper_id
    FROM newspapers_formats f
        LEFT JOIN newspapers_formats_held h ON (f.id = h.format_id)
    WHERE h.newspaper_id = 3
        OR h.format_id IS NULL

Answer 3

You need to put your filter criteria in your ON clause. Like this:

SELECT f.id, f.format_type, h.newspaper_id FROM newspapers_formats f LEFT JOIN newspapers_formats_held h ON (f.id = h.format_id) AND h.newspaper_id=3

Answer 4

Dewayne's answer is great.

Maybe using distinct will be even better.

Answer 5

This is a great example of the difference in query behavior between criteria in the ON clause vs where clause. For the WHERE clause, records returned must match. This excludes rows with no match newspaper_formats because newspaper id for these rows is NULL, and NULL != 3.

Format_type  newspaper_id   
1            NULL           Exclude because null != 3
1            3              Include because 3=3
1            2              Exclude because 2 !=3

To include all format types, you want to put that criteria in the join condition. The standard definition of left outer join: "Retrieve all records from the table on the left side of the join and only those records that match the join criteria from the table on the right side of the join." The join criteria must be in ON, not WHERE.

SELECT f.id, f.format_type, h.newspaper_id
FROM newspapers_formats f
LEFT JOIN newspapers_formats_held h
ON (f.id = h.format_id AND h.newspaper_id=3)