When did this T-SQL became incorrect? Or have it always?

Question

Why does the following (two last) statements return different results. I'm almost 100% sure that they 'used' to return the same result (does anyone recall how this used to be in SQL server 6/2000?).

CREATE TABLE [dbo].[color](
    [colorid] [int] NOT NULL,
    [title] [varchar](255) NOT NULL
) ON [PRIMARY]
INSERT color SELECT 1,'Red' UNION ALL SELECT 2,'Green' UNION ALL SELECT 3,'Blue'

CREATE TABLE [dbo].[membercolor](
    [memberid] [int] NOT NULL,
    [colorid] [int] NOT NULL
) ON [PRIMARY]
INSERT membercolor SELECT 10,1 UNION ALL SELECT 10,2 UNION ALL SELECT 10,3 UNION ALL SELECT 11,1 UNION ALL SELECT 11,2 UNION ALL SELECT 12,1

SELECT * FROM color
SELECT * FROM membercolor

-- red, green & blue
SELECT * FROM color a LEFT JOIN membercolor b 
    ON a.colorid=b.colorid AND b.memberid=11

-- red, green - but why?    
SELECT * FROM color a LEFT JOIN membercolor b 
    ON a.colorid=b.colorid WHERE b.memberid IS NULL OR b.memberid=11

Answer 1

The is null check only works for colors that have no matching membercolor at all.

If any membercolor matches, you end up with a result set like:

color blue memberid 8
color blue memberid 9
color blue memberid 10

Applying the where clause filters out all blue rows:

memberid IS NULL OR b.memberid=11

If no membercolor matches, the result set looks like:

color blue memberid NULL

The where clause will allow this to pass.

Answer 2

Consider the FROM clause as a separate part of the processing than the WHERE clause (which every product has to pretend it is, from a standards perspective).

During a LEFT JOIN, the system will take each row from the left table, and attempt to find matches in the right table. If there are matches, then it will produce one row in the output for each matching row in the right table. If there are no matches, then it will produce a single row, with all columns from the right table set to NULL.

In your first query, this is indeed what happens. But in your second query, during the FROM clause processing, it manages to produce a row which matches the JOIN condition:

3   Blue      10    3

So it never produces a row with NULL values for those columns from the right table.

Now, when the WHERE clause is processed, your conditions exclude this row from the final result set.

As others have said, you may be remembering the pre-ANSI join syntax, which could produce surprising results.

Answer 3

It was always this way (since 2000 at least) - to see why, check this other statement:

SELECT * FROM color a LEFT JOIN membercolor b 
    ON a.colorid=b.colorid 

On the other hand, what you might have intended is:

SELECT * FROM color a LEFT JOIN membercolor b 
    ON a.colorid=b.colorid AND b.memberid=11 WHERE b.memberid IS NULL OR b.memberid=11