CODEWITHSUNDEEP
sql
joon
joon

Reputation: 13

Left Join And vs Where on Second Table

Is there any difference in the output between these two queries?

Both tables only have one column - id.

Table A: {1,2,3,4,5}; Table B: {5,6,7,8,9}

Select *
from tableA
left join tableB on tableA.id = tableB.id
where tableB.id = 5;

Select *
from tableA
left join tableB on tableA.id = tableB.id and tableB.id = 5;

Upvotes: 1

Views: 273

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13139

Yes, the difference is that on the first one, if the left join doesn't find a match for A in B, it will leave the row, and then remove it using the WHERE clause. In other words, it will be equivalent to an INNER JOIN, because B.id is always null if there is no match.

For the second query, by contrast, if the query doesn't find a match for A in B using both conditions, it will leave the A row and put nulls in B.id for all non-matching rows.

WITH a(id) AS (VALUES (1), (2), (3), (4), (5)),
     b(id) AS (VALUES (5), (6), (7), (8), (9))
SELECT *
FROM a
LEFT JOIN b ON a.id = b.id
WHERE b.id = 5;

WITH a(id) AS (VALUES (1), (2), (3), (4), (5)),
     b(id) AS (VALUES (5), (6), (7), (8), (9))
SELECT *
FROM a
LEFT JOIN b ON a.id = b.id AND b.id = 5;

Upvotes: 3

Related Questions