Sql server left join strange behaviour

Question

Hello
I' ve done this table:

CREATE TABLE [dbo].[temp](
[Data] [datetime] NULL,
[TMax] [decimal](4, 1) NULL,
[TMin] [decimal](4, 1) NULL
) ON [PRIMARY]
GO

and this query:

select * from [dbo].[temp] as a
left outer join [dbo].[temp] as b
on a.[data]='19940101'
and b.[data]='19950101'
and a.tmax=b.tmax

I expect that there are only record with 19940101 from first table [a], instead the query extract the entire table.
I've the correct result if I write:

select * from [dbo].[temp] as a
left outer join [dbo].[temp] as b
on b.[data]='19950101'
and a.tmax=b.tmax
where a.[data]='19940101'

Perhaps there is something that escapes me?

Answer 1

That's the way Left Outer Join's work. A left join effectively first does what an inner join would (find all the matching rows) and then adds to the result all the rows in the table on the left (a in your case) which did not have a match. These added rows will have NULL in all of the columns from the table on the right (b in your case). So when you do a LEFT JOIN without a WHERE clause you will always get at least one row from the table on the left.

When you move the a.[data]='19940101' to the WHERE clause, the WHERE is processed after the FROM and any JOINs are processed. So in your second example the left join gets every row from a, but the WHERE clause then discards every row where a.[data] is not equal to '19940101'.

If you are only doing INNER JOINs, it doesn't make much difference whether you put conditions in the ON clause or the WHERE clause. But when you are doing OUTER JOIN's it is critical to put them in the right place to get the result you want.

Tom