Mike Eghtebas
asked on
SQL Query per requirement
Requirement: Write a query that returns all customers, but match orders only if they were placed in February 2008.
Question: Which of the following queries meets the above requirement and why one you choose but not the other?
Query 1 (110 records):
The book says that: Because both the comparison between the customer’s customer ID and the order’s customer ID, and the date range are considered part of the matching logic, both should appear in the ON clause, as follows.
I think this is not correct; we need to filter dates in WHERE clause but of course have custId with ON (the way Query 1 has them). What do you think?
Not schoolwork. EXAM 70-461
Question: Which of the following queries meets the above requirement and why one you choose but not the other?
Query 1 (110 records):
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
WHERE C.custid = O.custid
AND o.orderdate >='20080101' AND o.orderdate <'20080301'
Query 2 (143 records):
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON C.custid = O.custid
AND o.orderdate >='20080101' AND o.orderdate <'20080301'
The book says that: Because both the comparison between the customer’s customer ID and the order’s customer ID, and the date range are considered part of the matching logic, both should appear in the ON clause, as follows.
I think this is not correct; we need to filter dates in WHERE clause but of course have custId with ON (the way Query 1 has them). What do you think?
Not schoolwork. EXAM 70-461
ASKER
I had a typo in Query1, just to let you know:
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'
Also the book continues to say: This query returns 110 rows; here’s a portion of the output. but it returns 143 the one with 110 records is the one with WHERE clause.
This is copied right from the book:
Query2 returns the following result which includes null dates. Is the acceptable at all?
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'
Also the book continues to say: This query returns 110 rows; here’s a portion of the output. but it returns 143 the one with 110 records is the one with WHERE clause.
This is copied right from the book:
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';
This query returns 110 rows; here’s a portion of the output.
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
...
If you specify the date range
Query2 returns the following result which includes null dates. Is the acceptable at all?
1 Customer NRZBB 10835 2008-01-15 00:00:00.000
2 Customer MLTDN NULL NULL
3 Customer KBUDE 10856 2008-01-28 00:00:00.000
4 Customer HFBZG 10864 2008-02-02 00:00:00.000
5 Customer HGVLZ 10857 2008-01-28 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
5 Customer HGVLZ 10837 2008-01-16 00:00:00.000
6 Customer XHXJV 10853 2008-01-27 00:00:00.000
7 Customer QXVLA 10826 2008-01-12 00:00:00.000
8 Customer QUHWH NULL NULL
9 Customer RTXGC 10827 2008-01-12 00:00:00.000
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
12 Customer PSNMQ 10819 2008-01-07 00:00:00.000
13 Customer VMLOG NULL NULL
14 Customer WNMAF NULL NULL
15 Customer JUWXK NULL NULL
16 Customer GYBBY 10848 2008-01-23 00:00:00.000
17 Customer FEVNN 10825 2008-01-09 00:00:00.000
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
20 Customer THHDP 10836 2008-01-16 00:00:00.000
20 Customer THHDP 10854 2008-01-27 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 10824 2008-01-09 00:00:00.000
24 Customer CYZTN 10902 2008-02-23 00:00:00.000
25 Customer AZJED 10859 2008-01-29 00:00:00.000
26 Customer USDBG 10860 2008-01-29 00:00:00.000
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 10911 2008-02-26 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
31 Customer YJCBX NULL NULL
32 Customer YSIQX 10816 2008-01-06 00:00:00.000
33 Customer FVXPQ NULL NULL
34 Customer IBVRG 10886 2008-02-13 00:00:00.000
34 Customer IBVRG 10903 2008-02-24 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 10912 2008-02-26 00:00:00.000
37 Customer FRXZL 10897 2008-02-19 00:00:00.000
38 Customer LJUCA 10829 2008-01-13 00:00:00.000
39 Customer GLLAG 10817 2008-01-06 00:00:00.000
39 Customer GLLAG 10849 2008-01-23 00:00:00.000
39 Customer GLLAG 10893 2008-02-18 00:00:00.000
40 Customer EFFTC 10858 2008-01-29 00:00:00.000
41 Customer XIIWM 10832 2008-01-14 00:00:00.000
42 Customer IAIJK 10810 2008-01-01 00:00:00.000
43 Customer UISOJ NULL NULL
44 Customer OXFRU 10862 2008-01-30 00:00:00.000
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 10823 2008-01-09 00:00:00.000
46 Customer XPNIK 10899 2008-02-20 00:00:00.000
47 Customer PSQUZ 10811 2008-01-02 00:00:00.000
47 Customer PSQUZ 10838 2008-01-19 00:00:00.000
47 Customer PSQUZ 10840 2008-01-19 00:00:00.000
48 Customer DVFMB 10867 2008-02-03 00:00:00.000
48 Customer DVFMB 10883 2008-02-12 00:00:00.000
49 Customer CQRAA 10818 2008-01-07 00:00:00.000
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 10808 2008-01-01 00:00:00.000
55 Customer KZQZT 10855 2008-01-27 00:00:00.000
56 Customer QNIVZ 10833 2008-01-15 00:00:00.000
57 Customer WVAXS NULL NULL
58 Customer AHXHT NULL NULL
59 Customer LOLJO 10844 2008-01-21 00:00:00.000
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 10878 2008-02-10 00:00:00.000
63 Customer IRRVL 10865 2008-02-02 00:00:00.000
63 Customer IRRVL 10845 2008-01-21 00:00:00.000
64 Customer LWGMD 10828 2008-01-13 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
65 Customer NYUHS 10820 2008-01-07 00:00:00.000
65 Customer NYUHS 10852 2008-01-26 00:00:00.000
66 Customer LHANT 10812 2008-01-02 00:00:00.000
66 Customer LHANT 10908 2008-02-26 00:00:00.000
67 Customer QVEPD 10813 2008-01-05 00:00:00.000
67 Customer QVEPD 10851 2008-01-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 10831 2008-01-14 00:00:00.000
70 Customer TMXGN 10909 2008-02-26 00:00:00.000
71 Customer LCOUJ 10894 2008-02-18 00:00:00.000
71 Customer LCOUJ 10847 2008-01-22 00:00:00.000
71 Customer LCOUJ 10882 2008-02-11 00:00:00.000
71 Customer LCOUJ 10815 2008-01-05 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 10821 2008-01-08 00:00:00.000
76 Customer SFOGW 10846 2008-01-22 00:00:00.000
76 Customer SFOGW 10841 2008-01-20 00:00:00.000
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 10842 2008-01-20 00:00:00.000
80 Customer VONTK 10915 2008-02-27 00:00:00.000
81 Customer YQQWW 10839 2008-01-19 00:00:00.000
81 Customer YQQWW 10830 2008-01-13 00:00:00.000
81 Customer YQQWW 10834 2008-01-15 00:00:00.000
82 Customer EYHKM 10822 2008-01-08 00:00:00.000
83 Customer ZRNDE NULL NULL
84 Customer NRCSK 10814 2008-01-05 00:00:00.000
84 Customer NRCSK 10843 2008-01-21 00:00:00.000
84 Customer NRCSK 10850 2008-01-23 00:00:00.000
85 Customer ENQZT NULL NULL
86 Customer SNXOJ NULL NULL
87 Customer ZHYOS NULL NULL
88 Customer SRQVM 10809 2008-01-01 00:00:00.000
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
89 Customer YBQTI 10861 2008-01-30 00:00:00.000
90 Customer XBBVR 10879 2008-02-10 00:00:00.000
90 Customer XBBVR 10873 2008-02-06 00:00:00.000
90 Customer XBBVR 10910 2008-02-26 00:00:00.000
91 Customer CCFIZ 10906 2008-02-25 00:00:00.000
91 Customer CCFIZ 10870 2008-02-04 00:00:00.000
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the requirement is ALL customers:
i.e. select COUNT(*) as [ThisIsTheNumberOfCustomer s] from customers
so, any query that produces less than that many rows cannot meet the requirement
Query 1 (corrected)
This contains a IMPLICIT INNER JOIN
because EVERY ROW MUST MEET THESE CRITERIA o.orderdate >='20080101' AND o.orderdate <'20080301'
NULL does NOT meet those criteria!
Query 1 can be modified to permit unmatched records e.g.
I cannot vouch for the books' content. I suggest you build your own tests, it does not take many rows of data to do this.
i.e. select COUNT(*) as [ThisIsTheNumberOfCustomer
so, any query that produces less than that many rows cannot meet the requirement
Query 1 (corrected)
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'
This contains a IMPLICIT INNER JOIN
because EVERY ROW MUST MEET THESE CRITERIA o.orderdate >='20080101' AND o.orderdate <'20080301'
NULL does NOT meet those criteria!
Query 1 can be modified to permit unmatched records e.g.
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE ( o.orderdate >='20080101' AND o.orderdate <'20080301' )
OR O.custid IS NULL
I cannot vouch for the books' content. I suggest you build your own tests, it does not take many rows of data to do this.
I disagree with Ramanhp's analysis (sorry Ramanhp)
the requirement is to list ALL customers
and, if they exist, match to orders they MIGHT HAVE placed during Feb. 2008 (so this needs an outer join)
the requirement is to list ALL customers
and, if they exist, match to orders they MIGHT HAVE placed during Feb. 2008 (so this needs an outer join)
ASKER
Basically, our date predicate could be True/False/Unknown for date part.
Show if o.orderdate >='20080101' AND o.orderdate <'20080301' is True
Don't Show if o.orderdate >='20080101' AND o.orderdate <'20080301' is False
Unknown: Show because?
All customers ought to be displayed unless the predicate is explicitly false? Which is not (it is unknown, it is null?)
Mike
Show if o.orderdate >='20080101' AND o.orderdate <'20080301' is True
Don't Show if o.orderdate >='20080101' AND o.orderdate <'20080301' is False
Unknown: Show because?
All customers ought to be displayed unless the predicate is explicitly false? Which is not (it is unknown, it is null?)
Mike
If you disagree with my response,, then why you are pulling out the order details in your both queries.
If you rightly understand the question, then it says to get the list of Customers AND NOT THE DETAILS OF THEIR ORDERS, Further, it restricts to filter out only those ones that have made an order in Feb 2008.
What do you mean if they exists.... strange. You have your Customers database, and they exists there. And if they are the customers, then this means there is atleast one instance of order in your Orders DB.
Well, if you are sticking to the LEFT JOIN Logic. Then there is no need to write a complex query in this case.
Just have a simple Select Query on the Customers DB that will list out all the Customers details.
But if the concern is to get info some what like below...
Customer ID, Order in Feb2008
-------------------------- ---------- ---------
Customer1, Yes
Customer2, No
Customer3, Yes.....
..and so on.. then there stands the INNER JOIN.
If you rightly understand the question, then it says to get the list of Customers AND NOT THE DETAILS OF THEIR ORDERS, Further, it restricts to filter out only those ones that have made an order in Feb 2008.
What do you mean if they exists.... strange. You have your Customers database, and they exists there. And if they are the customers, then this means there is atleast one instance of order in your Orders DB.
Well, if you are sticking to the LEFT JOIN Logic. Then there is no need to write a complex query in this case.
Just have a simple Select Query on the Customers DB that will list out all the Customers details.
But if the concern is to get info some what like below...
Customer ID, Order in Feb2008
--------------------------
Customer1, Yes
Customer2, No
Customer3, Yes.....
..and so on.. then there stands the INNER JOIN.
ASKER
Thank you for all the comments.
FYI, I am trying to see why
I have to use: ON C.custid = O.custid AND o.orderdate >='20080101' AND o.orderdate <'20080301'
not this: ON C.custid = O.custid WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'
I am not trying to modify the choice of Left Join, I understand it maybe being discussed for purpose of discussion here.
The requirement: Write a query that returns all customers, but match orders only if they were placed in February 2008.
This to me if it means show the records is in Feb 2008, otherwise (if false or unknown) do not show. I will keep reading the comments over and over unless I could see why null values has to be a part of the output.
Mike
FYI, I am trying to see why
I have to use: ON C.custid = O.custid AND o.orderdate >='20080101' AND o.orderdate <'20080301'
not this: ON C.custid = O.custid WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'
I am not trying to modify the choice of Left Join, I understand it maybe being discussed for purpose of discussion here.
The requirement: Write a query that returns all customers, but match orders only if they were placed in February 2008.
This to me if it means show the records is in Feb 2008, otherwise (if false or unknown) do not show. I will keep reading the comments over and over unless I could see why null values has to be a part of the output.
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Paul,
Thank you for the patience. Finally, I understood what is going on.
Write a query that returns all customers, ... <-- only this gives 832 records
...but match orders only if they were placed in February 2008. <-- gives 110 records, match dates but doesn't include customer who has not placed an order yet.
Now, combining them we get 143 records using:
Every word you type turn to gold, to me as valuable.
Thanks,
Mike
Thank you for the patience. Finally, I understood what is going on.
Write a query that returns all customers, ... <-- only this gives 832 records
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON C.custid = O.custid
...but match orders only if they were placed in February 2008. <-- gives 110 records, match dates but doesn't include customer who has not placed an order yet.
SELECT Count(*) FROM Sales.Orders AS O
WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'
Now, combining them we get 143 records using:
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O
ON C.custid = O.custid
AND o.orderdate >='20080101' AND o.orderdate <'20080301'
Every word you type turn to gold, to me as valuable.
Thanks,
Mike
if you reference an outer joined table in the where clause you produce the effect of an inner join - OR - you have to provide for the ability to allow NULLs
So your Query1 is the same as an INNER JOIN (try it) example of "implicit inner join" here