SQL Standard Regarding Left Outer Join and Where Conditions

Question

I am getting different results based on a filter condition in a query based on where I place the filter condition. My questions are:

• Is there a technical difference between these queries?
• Is there anything in the SQL standard that explains the different recordsets from the queries?

Given the simplified scenario:

--Table: Parent  Columns: ID, Name, Description
--Table: Child   Columns: ID, ParentID, Name, Description

--Query 1
SELECT p.ID, p.Name, p.Description, c.ID, c.Name, c.Description
FROM   Parent p
   LEFT OUTER JOIN Child c ON (p.ID = c.ParentID)
WHERE  c.ID IS NULL OR c.Description = 'FilterCondition'

--Query 2
SELECT p.ID, p.Name, p.Description, c.ID, c.Name, c.Description
FROM   Parent p
   LEFT OUTER JOIN Child c
   ON (p.ID = c.ParentID AND c.Description = 'FilterCondition')

I assumed the queries would return the same resultsets and I was surprised when they didn't. I am using MS SQL2005 and in the actual queries, query 1 returned ~700 rows and query 2 returned ~1100 rows and I couldn't detect a pattern on which rows were returned and which rows were excluded. There were still many rows in query 1 with child rows with data and NULL data. I prefer the style of query 2 (and I think it is more optimal), but I thought the queries would return the same results.

Edit/Summary:

There were some great answers provided here. I had a hard time choosing to whom to award the answer. I decided to go with mdma since it was the first answer and one of the clearest. Based on the supplied answers, here is my summary:

Possible results:

• A: Parent with no children
• B: Parents with children
• |-> B1: Parents with children where no child matches the filter
• \-> B2: Parents with children where 1 or more match the filter

Query results:

• Query 1 returns (A, B2)
• Query 2 returns (A, B1, B2)

Query 2 always returns a parent because of the left join. In query 1, the WHERE clause is performed after the left join, so parents with children where none of the children match the filter are excluded (case B1).

Note: only parent information is returned in case B1, and in case B2 only the parent/child information matching the filter is returned.

HLGEM provided a good link:

http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN

Answer 1

The first query will return cases where the parent has no children or where some of the children match the filter condition. Specificaly, cases where the parent has one child, but it doesn't match the filter condition will be omitted.

The second query will return a row for all parents. If there is no match on filter condition, a NULL will be returned for all of c's columns. This is why you are getting more rows in query 2 - parents with children that don't match the filter condition are output with NULL child values, where in the first query they are filtered out.

Answer 2

Yes, there is a huge difference. When you place filters in the ON clause on a LEFT JOIN, the filter is applied before the results are joined to the outer table. When you apply a filter in the WHERE clause, it happens after the LEFT JOIN has been applied.

In short, the first query will exclude rows where there are child rows but the child description is not equal to the filter condition, whereas the second query will always return a row for the parent.

Answer 3

I notice couple of differences that can make the results vary.In the first query, you have LEFT OUTER JOIN Child c ON (p.ID = c.ParentID) and then in the second query you have LEFT OUTER JOIN Child c ON (p.ID = c.ParentID AND c.Description = 'FilterCondition') and this makes the second query return all parents with children satisfying your condition where as the first condition will also return the parents wit no children. Also look at the precedence of join conditions and where conditions.

Answer 4

Hi Ryan,

the parents that only have children with description != 'FilterCondition' won't appear in query 1 because the WHERE clause is evaluated after the rows are joined.

Answer 5

The first query returns fewer rows because it only returns rows that either don't have children, or have children that match the filter condition.

The WHERE clause excludes the rest (those that DO have children but don't match the filter condition.)

The 2nd query shows all three condition above.

Answer 6

For this recordset:

parent

id
1

child

id    parent filter
1     1      OtherCondition
2     1      OtherCondition

, the first query would return 0 records, while the second one would return 1 record:

WITH    parent (id) AS
        (
        SELECT  1
        ),
        child (id, parent, condition) AS
        (
        SELECT  1, 1, 'OtherCondition'
        UNION ALL
        SELECT  2, 1, 'OtherCondition'
        )
SELECT  *
FROM    parent
LEFT JOIN
        child
ON      child.parent = parent.id   

/* The children are found, so no fake NULL records returned */

1   1   1   OtherCondition
1   2   1   OtherCondition

Now adding WHERE clause:

WITH    parent (id) AS
        (
        SELECT  1
        ),
        child (id, parent, condition) AS
        (
        SELECT  1, 1, 'OtherCondition'
        UNION ALL
        SELECT  2, 1, 'OtherCondition'
        )
SELECT  *
FROM    parent
LEFT JOIN
        child
ON      child.parent = parent.id       
WHERE   child.id IS NULL OR child.condition = 'FilterCondition'

WHERE clause filters the records returned on the previous step and no record matches the condition.

While this one:

WITH    parent (id) AS
        (
        SELECT  1
        ),
        child (id, parent, condition) AS
        (
        SELECT  1, 1, 'OtherCondition'
        UNION ALL
        SELECT  2, 1, 'OtherCondition'
        )
SELECT  *
FROM    parent
LEFT JOIN
        child
ON      child.parent = parent.id       
        AND child.condition = 'FilterCondition'

1   NULL    NULL    NULL

returns a single fake record.

Answer 7

Putting the condition in the where clause converts it to an inner join (unless you are using something where where id is null which gives you records not inthe table) See this for a fuller explanation:

http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN