SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, May 23, 2007

SQL Query Help

SQL Apprentice Question
Need to learn SQL queries again (SQL 2005). Need help with the
following situation:


I have two tables, CUSTOMERS and ORDERS. One each column in these
tables should have identical data (except the name of the column is
different) which is customer's name. Because each order creates an
entry in ORDER table, each customer's name may be appearing more than
once in that table. There could be also instances where a particular
customer might not have placed an order at all and will not have any
entry in the ORDERS table, but do have one entry in CUSTOMERS table
(no duplicates in CUSTOMERS table).


I need help with a query which shows a list of customers who have
never placed any orders. This essentially means that they do not have
any entry in the ORDERS table. Here is the Pseudo Code:


select customername from CUSTOMERS table
who are NOT IN
ORDERS table


Will appreciate any quick thoughts. Thanks in advance!



Celko Answers

>> Need to learn SQL queries again (SQL 2005). <<>> I have two tables, CUSTOMERS and ORDERS. One each column in these tables should have identical data (except the name of the column is different) which is customer's name. <<>> I need help with a query which shows a list of customers who have never placed any orders. <<


Wild guess, done without DDL

SELECT C.customer_name
FROM Customers AS C
WHERE NOT EXISTS
(SELECT *
FROM Orders AS O
WHERE O.customer_name = C.customer_name);

1 comment:

Hilarion said...

Is there any chance that the person that's collecting different Joe Celko's posts in this blog could also point to original source of that post?
I also do not see any info on who is maintaining this site (the inly info is: it's not Joe Celko).