Need to select all rows from table1 that do not have a matching key in table 2

Posted on 2008-09-30
Last Modified: 2010-04-21
I have two tables ie Customers and Orders

I need to Select all customers for which there are no orders.

ie. Show the CustomerID for all orders where the CustomerID in the Customers table s not found in the CustomerID of the Orders table.
What is the correct SQL syntax ?
Question by:Dovberman
LVL 11

Accepted Solution

aaronakin earned 125 total points
ID: 22605551
  FROM Customers
  WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders)
LVL 60

Assisted Solution

chapmandew earned 125 total points
ID: 22605560
or like this

select * from customers c where not exists(select 1 from orders o where o.customerid = c.customerid)

Expert Comment

ID: 22605771
If I read that right, you want customers that have no Orders?

FROM Customers


SELECT CustomerID 

FROM Orders

Open in new window


Author Closing Comment

ID: 31501551
aaronikin and chapmandew split the points. Both returned 38 rows. adlink la returned 32 rows. Thanks to all.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using Aggregate Functions to Count 3 34
Can someone plz fix this..getting an error 3 18
create insert script based on records in a table 4 15
Sql query 107 29
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now