Difference b/w On and Where
-
Hi,
What is the difference between filtering result set with ON and Where. For reference see the following two queries. They give me different outputs.
1. Query 1 with filter applied in On
Select
t1.Field1,
t2.Field1
From
[Table1] t1
Left Outer Join
[Table2] t1
On
t2.foreingkey = t1.id
And
t2.Field3 = 20
And
t2.Field4 = 29
2. Query 2 with filters applied in Where clause
Select
t1.Field1,
t2.Field1
From
[Table1] t1
Left Outer Join
[Table2] t1
On
t2.foreingkey = t1.id
Where
t2.Field3 = 20
And
t2.Field4 = 29
Question: Why different result sets are produced for the above two queries ??
Regards,
Hatim Ali.
-
In general, for readability ...
'On' should be used for joins between tables:
select * from tablea a inner join tableb b
on a.id = b.id
etc
'Where' should be used to filter the results that are returned by the query:
...
where a.code = 'x' and b.desc 'turnip'
So your query (2) is the way I would go. What differences did you encounter in results returned?
Regards
Phil
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
In short, it is because these are two entirely different questions asked
Just a note, for equi-joins (inner joins) it doesn't matter if the filter is in the ON clause, the WHERE clause or both. Results are always the same anyway.
However, as you've noted, when dealing with OUTER joins, this becomes highly important. Where a filter should go depends on the question posed.
The first example says: Give me all rows from t1 regardless if there is a match in t2 AND IF there is a match in t2, also bring me the values from those columns that do match (on t2.fk = t1.id)
The second example on the other hand says: Give me from BOTH tables ONLY those rows that do match (on t2.fk = t1.id)
The determining factor here is also that the filter is applied to the inner table that brings this behaviour
To illustrate this a little...
create table #outer ( id int not null, s char(1) not null )
create table #inner ( id int not null, s char(1) not null )
go
insert #outer select 1, 'a'
insert #outer select 2, 'b'
insert #outer select 3, 'c'
insert #outer select 4, 'd'
insert #outer select 5, 'e'
insert #inner select 1, 'a'
insert #inner select 2, 'b'
insert #inner select 5, 'e'
go
-- example 1
select *
from #outer o
left join #inner i
on o.id = i.id
and i.s = 'b'
-- example 2
select *
from #outer o
left join #inner i
on o.id = i.id
where i.s = 'b'
id s id s
----------- ---- ----------- ----
1 a NULL NULL
2 b 2 b
3 c NULL NULL
4 d NULL NULL
5 e NULL NULL
(5 row(s) affected)
id s id s
----------- ---- ----------- ----
2 b 2 b
(1 row(s) affected)
The most common usage of the 2nd example (filtering on the inner table in the WHERE clause) is to answers questions like: Which rows are missing from table B but do exist in table A?
To anser that, you filter on WHERE <innertable not-nullable column> IS NULL
select *
from #outer o
left join #inner i
on o.id = i.id
where i.id is null
id s id s
----------- ---- ----------- ----
3 c NULL NULL
4 d NULL NULL
(2 row(s) affected)
So, which should you use? Well, it depends
The best thing to do is to create some small testtables and play around with different variations and see what happens. After a while, I'm sure you'll learn to understand the how's and why's of outer joins.
The main thing to keep in mind, though - when writing outer joins - always test your queries against known data to make sure it's working as expected.
Good luck.
/Kenneth
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply