CODEWITHSUNDEEP
t-sql
David Folksman
David Folksman

Reputation: 235

Why do these two queries return a different number of rows

I'm using adventureworks2012:

The first query returns 43 rows and no nulls, whereas the second returns over 19 thousand and lots of nulls.

I thought an outer join returned rows from the left side of the query even if the condition is not met, and therefore the two queries should be equivalent?

--1

SELECT c.CustomerID, s.SalesOrderID, s.OrderDate
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader AS s ON c.CustomerID = s.CustomerID
WHERE s.OrderDate = '2005/07/01';

--2

WITH orders AS (
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate = '2005/07/01'
)
SELECT c.CustomerID, orders.SalesOrderID, orders.OrderDate
FROM Sales.Customer AS c
LEFT OUTER JOIN orders ON c.CustomerID = orders.CustomerID
ORDER BY orders.OrderDate DESC;

Upvotes: 1

Views: 57

Answers (2)

paparazzo
paparazzo

Reputation: 45096

You are killing the outer in the first by referencing a table in the where
Just move that condition into the join 

SELECT c.CustomerID, s.SalesOrderID, s.OrderDate
 FROM Sales.Customer AS c
 LEFT OUTER JOIN Sales.SalesOrderHeader AS s 
   ON c.CustomerID = s.CustomerID
  AND s.OrderDate = '2005/07/01';

Upvotes: 2

John Woo
John Woo

Reputation: 263723

It's because of WHERE clause in query 1. The WHERE clause filters the final result of the two tables being joined. You need to move the condition from WHERE clause into ON, which filters the records (on table Sales.SalesOrderHeader) first before joining it to another table (Sales.Customer), you can also find it here.

SELECT c.CustomerID, s.SalesOrderID, s.OrderDate
FROM   Sales.Customer AS c
       LEFT OUTER JOIN Sales.SalesOrderHeader AS s 
           ON c.CustomerID = s.CustomerID  AND 
              s.OrderDate = '2005/07/01';

Upvotes: 2

Related Questions