Filter Condition in WHERE vs ON clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Filter Condition in WHERE vs ON clause

Author

Topic

MaveriK
Starting Member

7 Posts

Posted - 2015-04-07 : 14:02:28

Hi,
I would like to understand what is the difference in using the filter condition in the WHERE and ON clause.
For example two tables with join on ID column and filtering Table_1 to get only ID=1.

SQL-1:
SELECT *
FROM TABLE_1 T1
LEFT OUTER JOIN TABLE_2 T2
ON T1.ID=T2.ID AND T1.ID=1

SQL-2:
SELECT *
FROM TABLE_1 T1
LEFT OUTER JOIN TABLE_2 T2
ON T1.ID=T2.ID
WHERE T1.ID=1

As per my understanding the First SQL filters the table_1 on ID=1 and then joins this result to Table_2. While in the second SQL the join is performed irrespective of the ID values in table_1 and then on the result set WHERE performs the filtering condition.

But either way the result should be same right? Or am I missing something here. Please enlighten.

Thanks very much in Advance.

Thanks & Regards
MaveriK

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-07 : 14:09:32

Correct, though the net result will be the same (also in terms of performance)

This is a major difference between an INNER and OUTER join. For an INNER join, the WHERE and ON clauses are essentially interchangeable. For an OUTER join, the ON clause does the matching (based on common keys in both tables) and the WHERE clause filters the results. One use would be to identify mismatched rows. e.g.


select *
from t1
left join t2
on t1.id = t2.id
where t2.id is null

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-07 : 14:20:40

CREATE TABLE #A
(
ID int identity(1,1),
TXT varchar(10)
)

CREATE TABLE #B
(
ID int,
OtherTXT varchar(10)
)

INSERT INTO #A
VALUES('RED'),('Yellow'),('green')

INSERT INTO #B
VALUES(1,'Banana'),(1,'Taxi'),(2,'Apple'),(3,'Tomato'),(3,'Cheese')

SELECT * FROM #A A
LEFT JOIN #B B ON A.ID = B.ID AND B.ID = 2

SELECT * FROM #A A
LEFT JOIN #B B ON A.ID = B.ID
WHERE B.ID = 2

The results on a left join will bring back all values for the anchor table and only corresponding values with the ID in the on clause. which means you'll get all the results of A and only conditions of B that meet that criteria in the on clause.

The were clause filters after the fact - so all IDs are joins and the result only reflects the exact occurrence where the joined IDs have and ID = to what you were looking for. Since this is the joined column - you'll get only the specific ID mention as you are filtering on the same field as used in the join.

however if you were to do something like this, the result would be the same

SELECT * FROM #A A
LEFT JOIN #B B ON A.ID = B.ID AND B.OtherTXT like '%A%'

SELECT * FROM #A A
LEFT JOIN #B B ON A.ID = B.ID
WHERE B.OtherTXT like '%A%'

Kristen
Test

22859 Posts

Posted - 2015-04-08 : 04:09:22

quote:
Originally posted by MichaelJSQL

however if you were to do something like this, the result would be the same

SELECT * FROM #A A
LEFT JOIN #B B ON A.ID = B.ID AND B.OtherTXT like '%A%'

SELECT * FROM #A A
LEFT JOIN #B B ON A.ID = B.ID
WHERE B.OtherTXT like '%A%'

but not if your queries were:


SELECT * FROM #A A 
LEFT JOIN #B B ON A.ID = B.ID AND B.OtherTXT like '%X%'

SELECT * FROM #A A 
LEFT JOIN #B B ON A.ID = B.ID 
WHERE B.OtherTXT like '%X%'

In the first the test on B.OtherTXT restricts rows that are included in the Outer Join.
In the second it restricts rows returned by the overall query - in effect converting the Outer Join to an Inner Join.

MaveriK
Starting Member

7 Posts

Posted - 2015-04-08 : 04:39:21

I think I understand this better now.
Thank you gbritton, MichaelJSQL and Kristen.

Thanks & Regards
MaveriK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources