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.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2012-03-24 : 09:48:16
|
Hi. I have 2 queries:Query 1:select * fromT1 left outer join T2 on T1.C1 = T2.C2where T2.C3 = 1000 Query 2:select * from(select * fromT1 left outer join T2 on T1.C1 = T2.C2) as Twhere C3 = 1000 So it is the same query but in Query 2, the filter is moved outside the main query. The problem is that Query 2 is very slow (Query 1 is very fast). I am very surprised at the speed difference since it is essentially the same query. When I change both queries to inner join, both are fast. The issue is that I wish to make a view here: select * from T1 left outer join T2 on T1.C1 = T2.C2Let's assume I have proper indexes and stats are up to date etc...Has anyone encountered anything similar or have any suggestions? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-03-24 : 10:13:55
|
They are interpreted differently by SQL. The first query is effectively an inner join because of the presence of the WHERE clause that has a column from T2. The first query will give the same results as this:select * fromT1 INNER join T2 on T1.C1 = T2.C2where T2.C3 = 1000 If you really want to do a left join only with rows from T2 where T2.C3 = 1000, do it like this:select * fromT1 left outer join T2 on T1.C1 = T2.C2 AND T2.C3 = 1000 The inner query in the second example you posted is a real left join - which would preserve all the rows from the left table[/code] |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2012-03-24 : 10:27:58
|
| Thanks. I think this means our SQL is not quite right... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Slow left outer join query