How to subtract the results of one table from another in sql?

Posted on 2008-10-15
Last Modified: 2010-04-21
I have two tables:



Each table has a customer id.  How do I show New customers who are not also old customers?  That is I want to take the Customers_New table and subtract out the Customers_Old. I tried a few different types of joins but none of them seemed to be doing anything that I expected.

I'm using SQl server 2000.
Question by:BostonMA
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Author Comment

ID: 22721885
I may have stumbled on it. Is this it:

select * from customers_new
inner join customers_old on customers_new.customerid = customer_old.customerid
LVL 39

Accepted Solution

BrandonGalderisi earned 500 total points
ID: 22721890
Assuming you have a common customer_id between the two.  Otherwise, change the ON and where line line to what is common

select cn.* from customers_new cn
left outer join customers_old co
on cn.customer_id = co.customer_id
where co.customer_id is null

LVL 39

Assisted Solution

BrandonGalderisi earned 500 total points
ID: 22721899
no... you need a left outer join.  The "outer" is optional and implied when you use left, right or full.

Author Closing Comment

ID: 31506332
Thanks guys.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

635 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