Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

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):
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'

Open in new window

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'

Open in new window


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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Query 2 is correct (for that requirement)

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
Avatar of Mike Eghtebas

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:
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

Open in new window


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

Open in new window

SOLUTION
Avatar of Ramanhp
Ramanhp
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the requirement is ALL customers:
i.e. select COUNT(*) as [ThisIsTheNumberOfCustomers] from customers

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'

Open in new window


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

Open in new window


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)
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
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.
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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

Open in new window


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

Open in new window


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' 

Open in new window


Every word you type turn to gold, to me as valuable.

Thanks,

Mike