asked
Alternative to left (or right) outer join
Hi, Could someone explain to me why the following queries return different result sets ? I know they are syntaxically (is that a word ?!?!?) different but are they logically different? I am working on SQL Server 2005 SP3. Thanks SELECT a.Col1, b.Col2, c.Col3 FROM atable a LEFT OUTER JOIN btable b ON
a.id =
b.id LEFT OUTER JOIN ctable c ON a.id2 =
c.id WHERE a.Value = 0 SELECT a.Col1, b.Col2, c.Col3 FROM atable a, btable b, ctable c WHERE (
a.id =
b.id OR
b.id IS NULL) AND (a.id2 =
c.id OR
c.id IS NULL) AND a.Value = 0
Yes, they are logically different. Here's a simpler example of why: create table #t (id int) create table #t2 (id int) insert into #t (id) values(1) select
a.id,
b.id from #t a, #t2 b where (
a.id=
b.id or
b.id is null) select
a.id,
b.id from #t a left outer join #t2 b on
a.id=
b.id drop table #t drop table #t2 In the first query, you'd get no results, because there are no rows in #t2, either with id being equal to #
t.id or null. In the second query, you specify with the left join that you want all rows from #t, and if there are any rows in #t2 that matches #
t.id=#
t2.id you want them as well, otherwise you just want the row from #t, and with columns from #t2 nulled. **EDIT** In older flavours of SQL Server, you could use ***=** in the WHERE clause to specify an outer join, but that's not valid anymore. **
a.id=
b.id or
b.id is null** to simulate outer join on the other hand has never worked. The way it *USED TO WORK* was like this: SELECT
a.id,
b.id FROM #t a, #t2 b WHERE
a.id *=
B.id But that requires a compatibility level that you don't want to use anymore, plus non ansi joins WILL produce very unpredictable results, so you shouldn't use it even if it would be available :) **END EDIT**
I have updated my answer. There used to be ways to do that, but you don't want to do that. JOINs should be specified as JOINs and WHEREs as WHEREs. I can't come up with a really good example from the top of my head, but there are many situations where SQL Server will produce unpredictable results when putting joins in the WHERE clause, simply because there is no way to tell what's a join condition and what's a filtering condition.
@Magnus Ahlkvist Thanks for the explaination I am fast approaching the realisation that I may be trying to achieve the impossible. Is there a way to 'shift' the join criteria to the where clause ?
