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

Question

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)

Possible duplicate of [SQL JOIN and different types of JOINs](http://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) — Tab Alleman, May 12 '16 at 13:00

score 5 · Accepted Answer · answered May 12 '16 at 11:55

5

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.

answered May 12 '16 at 11:55

Gordon Linoff

1,242,037
58
646
786

1

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. May 12 '16 at 12:06
Thank you Micheal, A.FID is the the true foreign key. Is there any text on this? – Dung Tran May 12 '16 at 12:34
You mean documentation on joins? Pinal Dave's [Introduction to SQL Joins](http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/) is a good place to start. The [Visual Representation of SQL Joins](http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins) is also a good reference. – Michael L. May 12 '16 at 13:14

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

1 Answers1