Dung Tran Dung Tran - 6 months ago 8
SQL Question

SQL Logic: When joining Child table B to Parent Table A on A.FID = B.ID

I have been wondering if the results would change in multi-join tables queries.

If you have parent Table A

A B
ID|FID FID
1|2 1
2|4 2
3|5 3
4|7 4
5|8 5
6|NULL 6
7|NULL 7
8|NULL 8


does it matter which table column you specified in the WHERE clause?
For example, what is the difference between the two:

Select *
From Table A
Left Join B on A.FID = B.FID
WHERE A.FID IN (2,5,8)

Select *
From Table A
Left Join B on A.FID = B.FID
WHERE B.ID IN (2,5,8)


Thank you for the help!

EDIT:
Micheal has solved my question and I have tested it out

'Actually, while your answer is a good one (and probably the one he's looking for), since both of his queries are essentially filtering on the primary key of B (A.FID, B.ID), they actually are logically identical (assuming that A.FID is a true foreign key constraint on B). That is, both queries filter out rows in which B.ID is not 2, 5 or 8.' – Michael L.

It is only different is Table B is the main table and you queried based on B.ID as in:

SELECT *
FROM B
LEFT JOIN A ON A.FID = B.FID
WHERE B.FID IN (2,5,8)


While this will be the same as having A as the main table:

SELECT *
FROM B
LEFT JOIN A ON A.FID = B.FID
WHERE A.FID IN (2,5,8)

Answer

Yes, it does.

When you use an OUTER JOIN, values from one of the tables may be NULL. So, the second query is equivalent to:

Select *
From Table A Inner Join 
     B
     on A.FID = B.ID
WHERE B.ID IN (2, 5, 8);

because the NULL values are filtered out.

As a general rules with LEFT JOIN:

  • Filters on the first table belong in the WHERE clause.
  • Filters on the second and subsequent tables should to in the ON clause.