I have a JOIN vs. IN question...
The Premier Products exercise for Chpater 4, question #6 asks us to "Find the number and name of each customer that did not place an order on October 20, 2003. My initial query was as follows...
SELECT orders.customer_num, customer_name
FROM orders, customer
WHERE orders.customer_num = customer.customer_num
AND order_date <> '20-OCT-2003';
Upon running it and comparing it to the correct query which is....
SELECT customer_num, customer_name
FROM customer
WHERE customer_num not in
(SELECT customer_num
FROM orders
WHERE order_date = '20-OCT-2003');
I found that these two queries produced different results. The first query resulted in fewer rows displayed than the second, and I am curious as to why. Does it have something to do with the fact that my first query places too harsh a limit on the results because it filters out all the customers who placed an order on the 20th, irrespective of the fact that they might have placed an order on another date also? Is this what the second query is taking into consideration... that the same customer who placed an order on the 20th may have also placed an order on another day, and b/c they placed an order on another day, they should be displayed? Or, is my first query and this entire logic just totally flawed???
Comments