Dung Tran - 5 months ago 5x
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)
``````

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.