Why do these two queries return a different number of rows

Question

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;

Answer 1

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';

Answer 2

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';