SQL Standard Regarding Left Outer Join and Where Conditions
- by Ryan
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
resultsets in 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.
