Filtering with WHERE while using JOIN?

Question

I want to make a query that returns all customers, but match orders only if they were placed in February 2008. This requires a join of tables Customers and Orders.

I used two queries and got different answers. The second one is right, but why ? Logically speaking, I was expecting the first one to be right, but its not.

SELECT  Cust.custid, Cust.contactname, Ord.orderid, Ord.orderdate 
FROM Sales.Customers AS Cust LEFT OUTER JOIN
Sales.Orders Ord
ON Cust.custid = Ord.custid
WHERE Ord.orderdate >= '2008-02-01' 
AND Ord.orderdate < '2008-03-01' 
order by Cust.custid, Ord.orderdate asc

Second query -

SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
AND O.orderdate >= '20080201'
AND O.orderdate < '20080301';

---

Result sets for queries 1 and 2 added below. Putting the AND part in brackets of WHERE in Q1 has no effect.

Q1 -

custid,companyname,orderid,orderdate
4,Arndt, Torsten,10864,2008-02-02 00:00:00.000
5,Higginbotham, Tom,10866,2008-02-03 00:00:00.000
5,Higginbotham, Tom,10875,2008-02-06 00:00:00.000
9,Raghav, Amritansh,10871,2008-02-05 00:00:00.000
9,Raghav, Amritansh,10876,2008-02-09 00:00:00.000
12,Ray, Mike,10881,2008-02-11 00:00:00.000
18,Rizaldy, Arif,10890,2008-02-16 00:00:00.000
20,Kane, John,10895,2008-02-18 00:00:00.000
24,San Juan, Patricia,10880,2008-02-10 00:00:00.000
24,San Juan, Patricia,10902,2008-02-23 00:00:00.000
29,Kolesnikova, Katerina,10887,2008-02-13 00:00:00.000
30,Shabalin, Rostislav,10872,2008-02-05 00:00:00.000
30,Shabalin, Rostislav,10874,2008-02-06 00:00:00.000
30,Shabalin, Rostislav,10888,2008-02-16 00:00:00.000
30,Shabalin, Rostislav,10911,2008-02-26 00:00:00.000
34,Cohen, Shy,10886,2008-02-13 00:00:00.000
34,Cohen, Shy,10903,2008-02-24 00:00:00.000
35,Langohr, Kris,10863,2008-02-02 00:00:00.000
35,Langohr, Kris,10901,2008-02-23 00:00:00.000
37,Crăciun, Ovidiu V.,10897,2008-02-19 00:00:00.000
37,Crăciun, Ovidiu V.,10912,2008-02-26 00:00:00.000
39,Song, Lolan,10893,2008-02-18 00:00:00.000
44,Louverdis, George,10891,2008-02-17 00:00:00.000
45,Sunkammurali,  Krishna,10884,2008-02-12 00:00:00.000
46,Dressler, Marlies,10899,2008-02-20 00:00:00.000
48,Szymczak, Radosław,10867,2008-02-03 00:00:00.000
48,Szymczak, Radosław,10883,2008-02-12 00:00:00.000
50,Mace, Donald,10892,2008-02-17 00:00:00.000
50,Mace, Donald,10896,2008-02-19 00:00:00.000
54,Tiano, Mike,10898,2008-02-20 00:00:00.000
62,Misiec, Anna,10868,2008-02-04 00:00:00.000
62,Misiec, Anna,10913,2008-02-26 00:00:00.000
62,Misiec, Anna,10914,2008-02-27 00:00:00.000
63,Veronesi, Giorgio,10865,2008-02-02 00:00:00.000
63,Veronesi, Giorgio,10878,2008-02-10 00:00:00.000
64,Gaffney, Lawrie,10916,2008-02-27 00:00:00.000
65,Moore, Michael,10889,2008-02-16 00:00:00.000
66,Voss, Florian,10908,2008-02-26 00:00:00.000
67,Garden, Euan,10877,2008-02-09 00:00:00.000
70,Ginters, Kaspars,10909,2008-02-26 00:00:00.000
71,Navarro, Tomás,10882,2008-02-11 00:00:00.000
71,Navarro, Tomás,10894,2008-02-18 00:00:00.000
72,Welcker, Brian,10869,2008-02-04 00:00:00.000
74,O’Brien, Dave,10907,2008-02-25 00:00:00.000
76,Gulbis, Katrin,10885,2008-02-12 00:00:00.000
80,Geschwandtner, Jens,10915,2008-02-27 00:00:00.000
88,Li, Yan,10900,2008-02-20 00:00:00.000
88,Li, Yan,10905,2008-02-24 00:00:00.000
89,Smith Jr., Ronaldo,10904,2008-02-24 00:00:00.000
90,Larsson, Katarina,10873,2008-02-06 00:00:00.000
90,Larsson, Katarina,10879,2008-02-10 00:00:00.000
90,Larsson, Katarina,10910,2008-02-26 00:00:00.000
91,Conn, Steve,10870,2008-02-04 00:00:00.000
91,Conn, Steve,10906,2008-02-25 00:00:00.000

Q2 -

custid,companyname,orderid,orderdate
1,Customer NRZBB,NULL,NULL
2,Customer MLTDN,NULL,NULL
3,Customer KBUDE,NULL,NULL
4,Customer HFBZG,10864,2008-02-02 00:00:00.000
5,Customer HGVLZ,10866,2008-02-03 00:00:00.000
5,Customer HGVLZ,10875,2008-02-06 00:00:00.000
6,Customer XHXJV,NULL,NULL
7,Customer QXVLA,NULL,NULL
8,Customer QUHWH,NULL,NULL
9,Customer RTXGC,10876,2008-02-09 00:00:00.000
9,Customer RTXGC,10871,2008-02-05 00:00:00.000
10,Customer EEALV,NULL,NULL
11,Customer UBHAU,NULL,NULL
12,Customer PSNMQ,10881,2008-02-11 00:00:00.000
13,Customer VMLOG,NULL,NULL
14,Customer WNMAF,NULL,NULL
15,Customer JUWXK,NULL,NULL
16,Customer GYBBY,NULL,NULL
17,Customer FEVNN,NULL,NULL
18,Customer BSVAR,10890,2008-02-16 00:00:00.000
19,Customer RFNQC,NULL,NULL
20,Customer THHDP,10895,2008-02-18 00:00:00.000
21,Customer KIDPX,NULL,NULL
22,Customer DTDMN,NULL,NULL
23,Customer WVFAF,NULL,NULL
24,Customer CYZTN,10880,2008-02-10 00:00:00.000
24,Customer CYZTN,10902,2008-02-23 00:00:00.000
25,Customer AZJED,NULL,NULL
26,Customer USDBG,NULL,NULL
27,Customer WMFEA,NULL,NULL
28,Customer XYUFB,NULL,NULL
29,Customer MDLWA,10887,2008-02-13 00:00:00.000
30,Customer KSLQF,10888,2008-02-16 00:00:00.000
30,Customer KSLQF,10874,2008-02-06 00:00:00.000
30,Customer KSLQF,10872,2008-02-05 00:00:00.000
30,Customer KSLQF,10911,2008-02-26 00:00:00.000
31,Customer YJCBX,NULL,NULL
32,Customer YSIQX,NULL,NULL
33,Customer FVXPQ,NULL,NULL
34,Customer IBVRG,10903,2008-02-24 00:00:00.000
34,Customer IBVRG,10886,2008-02-13 00:00:00.000
35,Customer UMTLM,10901,2008-02-23 00:00:00.000
35,Customer UMTLM,10863,2008-02-02 00:00:00.000
36,Customer LVJSO,NULL,NULL
37,Customer FRXZL,10897,2008-02-19 00:00:00.000
37,Customer FRXZL,10912,2008-02-26 00:00:00.000
38,Customer LJUCA,NULL,NULL
39,Customer GLLAG,10893,2008-02-18 00:00:00.000
40,Customer EFFTC,NULL,NULL
41,Customer XIIWM,NULL,NULL
42,Customer IAIJK,NULL,NULL
43,Customer UISOJ,NULL,NULL
44,Customer OXFRU,10891,2008-02-17 00:00:00.000
45,Customer QXPPT,10884,2008-02-12 00:00:00.000
46,Customer XPNIK,10899,2008-02-20 00:00:00.000
47,Customer PSQUZ,NULL,NULL
48,Customer DVFMB,10883,2008-02-12 00:00:00.000
48,Customer DVFMB,10867,2008-02-03 00:00:00.000
49,Customer CQRAA,NULL,NULL
50,Customer JYPSC,10892,2008-02-17 00:00:00.000
50,Customer JYPSC,10896,2008-02-19 00:00:00.000
51,Customer PVDZC,NULL,NULL
52,Customer PZNLA,NULL,NULL
53,Customer GCJSG,NULL,NULL
54,Customer TDKEG,10898,2008-02-20 00:00:00.000
55,Customer KZQZT,NULL,NULL
56,Customer QNIVZ,NULL,NULL
57,Customer WVAXS,NULL,NULL
58,Customer AHXHT,NULL,NULL
59,Customer LOLJO,NULL,NULL
60,Customer QZURI,NULL,NULL
61,Customer WULWD,NULL,NULL
62,Customer WFIZJ,10868,2008-02-04 00:00:00.000
62,Customer WFIZJ,10913,2008-02-26 00:00:00.000
62,Customer WFIZJ,10914,2008-02-27 00:00:00.000
63,Customer IRRVL,10865,2008-02-02 00:00:00.000
63,Customer IRRVL,10878,2008-02-10 00:00:00.000
64,Customer LWGMD,10916,2008-02-27 00:00:00.000
65,Customer NYUHS,10889,2008-02-16 00:00:00.000
66,Customer LHANT,10908,2008-02-26 00:00:00.000
67,Customer QVEPD,10877,2008-02-09 00:00:00.000
68,Customer CCKOT,NULL,NULL
69,Customer SIUIH,NULL,NULL
70,Customer TMXGN,10909,2008-02-26 00:00:00.000
71,Customer LCOUJ,10882,2008-02-11 00:00:00.000
71,Customer LCOUJ,10894,2008-02-18 00:00:00.000
72,Customer AHPOP,10869,2008-02-04 00:00:00.000
73,Customer JMIKW,NULL,NULL
74,Customer YSHXL,10907,2008-02-25 00:00:00.000
75,Customer XOJYP,NULL,NULL
76,Customer SFOGW,10885,2008-02-12 00:00:00.000
77,Customer LCYBZ,NULL,NULL
78,Customer NLTYP,NULL,NULL
79,Customer FAPSM,NULL,NULL
80,Customer VONTK,10915,2008-02-27 00:00:00.000
81,Customer YQQWW,NULL,NULL
82,Customer EYHKM,NULL,NULL
83,Customer ZRNDE,NULL,NULL
84,Customer NRCSK,NULL,NULL
85,Customer ENQZT,NULL,NULL
86,Customer SNXOJ,NULL,NULL
87,Customer ZHYOS,NULL,NULL
88,Customer SRQVM,10905,2008-02-24 00:00:00.000
88,Customer SRQVM,10900,2008-02-20 00:00:00.000
89,Customer YBQTI,10904,2008-02-24 00:00:00.000
90,Customer XBBVR,10910,2008-02-26 00:00:00.000
90,Customer XBBVR,10873,2008-02-06 00:00:00.000
90,Customer XBBVR,10879,2008-02-10 00:00:00.000
91,Customer CCFIZ,10870,2008-02-04 00:00:00.000
91,Customer CCFIZ,10906,2008-02-25 00:00:00.000

Answer 1

When ever there is outer join ,where clause is applied on the results from outer join. Hence the difference. In the first query condition is part of where clause which means once all customers are retrieved where clause is applied where you have orders in given range.So it will miss some customers who don't have any orders.

Where as in case of Second query all customers are reported irrespective of orders present or not.If present it will check for order range date. Hence second query is correct.

Answer 2

The first query will join Customers and Orders first, then it will filter out all rows as per your WHERE clause That is, customers that don't have a record (For them, orderdate and such will be null) in that date range will be excluded.

The second query will return all Customers, but it will only join an Order record where the Order date is within the specified range.

I hope this makes sense - let me know if you need further clarification!

Answer 3

The from clause is evaluated before the where clause.

In the first case, if an order has no date in the range, then the results will be filtered out of the query. This means that there are no rows for the customer to include in the output.

In the second case, if an order has no date in the range, then it fails the on clause. The query is still doing a left join, so it keeps the row from customer. The values from the second table are NULL. There is no additional filtering, so the customer record appears in the output.