OUTER JOINs with ON clause conditions

A friend who I’ve worked with on projects sent me some code the other day that had the below syntax; I had to quickly point out that she needed to be careful when using this type of construct.  Set up the minimal example by running the following code. We’ll create two tables and then OUTER join them, and will look at the problem that you need to be aware of. Run this (using SQL Server 2008 R2. Less than SQL 2008 you’ll have to create separate INSERT statements):

BEGIN TRY
    DROP TABLE Customer, CustOrder
END TRY
BEGIN CATCH
END CATCH
GO

CREATE TABLE Customer
    (CustId int PRIMARY KEY
    ,CustType char(1)
    ,StartDt datetime
    ,EndDt Datetime    
    )

CREATE TABLE CustOrder
    (OrderId int IDENTITY PRIMARY KEY
    ,CustId int
    ,OrderQty int
    ,OrderAmt money
    ,CONSTRAINT xx  FOREIGN KEY (CustId)
        REFERENCES Customer (CustId)
    )
GO
INSERT Customer VALUES (100,'a',GETDATE(),'12/31/9999'), (200,'b',GETDATE(),'12/31/9999')
INSERT CustOrder VALUES (100,10,2500)

Now execute the following and review the results:

SELECT *
FROM Customer C
LEFT OUTER JOIN CustOrder CO
    ON C.CustId = CO.CustId
WHERE C.CustType='a'

SELECT *
FROM Customer C
LEFT OUTER JOIN CustOrder CO
    ON C.CustId = CO.CustId
    AND C.CustType='a'

Wut up?  Why did we get a correct result in the first one, but for the second, something that doesn’t look right?  We did specify C.CustType=’a’, right?  The problem is created because of the way that the query processor looks at the code – it consumes each portion of the statement via the following. Goes kinda like this;

/*
Order of execution by query processor
--------------------------------------
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
*/

Using an additional predicate can also change the results, assuming that you wanted them as such. Bottom line - be careful with this syntax.

Thanks,

Lee Everest

------------------------------

“Hi, Peter. What's happening? We need to talk about your TPS reports.”