Find Records That Are Not in Either Table

Suppose you have one table that has names and phone numbers of people you have contacted and another table which contains names and phone numbers of your contacts.

Phone Calls Made – Table Name: Callsmade

Name Phone
Fred Flinstone 1-800-333-2321
Barney Ruble 1-800-373-1234
Wilma Flinstone 1-800-333-2322
Betty Ruble 1-800-373-1235

Contact List – Table Name: Contacts

Name Phone
Fred Flinstone 1-800-333-2321
Barney Ruble 1-800-373-1234
Mr. Slate 1-800-373-3345
Bamm-Bamm Rubble 1-800-373-2345

Now you want to find those records that you have made calls to that are not in your contact list AND those records that you have never made a phone call to but are in your contact list. I know you can do something like this.

--Gets the Calls made but not in Contacts
SELECT *
FROM   Callsmade
WHERE  NOT EXISTS
  (SELECT *
   FROM   Contacts
   WHERE  Contacts.Phone = Callsmade.phone)

UNION ALL

--Gets the Contacts but no calls made
SELECT *
FROM   Contacts
WHERE  NOT EXISTS
  (SELECT *
   FROM   Callsmade
   WHERE  Callsmade.Phone = Contacts.phone)

But this seems a little confusing and inefficient. Instead you can use something like this.

Select fnl.callsmade, fnl.contactlist, tmp.Phone from (
  Select max(tmp.callsmade) as callsmade, max(tmp.contactlist) as contactlist ,tmp.Phone from
    (Select ' ' as callsmade, Names as contactlist, Phone as Phone from Contacts
    Union ALL
    Select Name as callsmade, ' ' as contactlist, Phone as Phone from CallsMade) tmp
  group by  tmp.Phone,tmp.callsmade, tmp.contactlist) fnl where fnl.contactlist =' ' or fnl.callsmade=' '

The result will be a list that answers the initial question. You can also change your where clause to show where they match and where the data is in one table but not the other. This query gives you a little more flexibility than using the NOT EXIST, especially if you want to look at the data in different ways.

I realize that there are times that this type of query is not appropriate but I have actually found it to work just fine in cases with less than 300K records. Once again this just proves there any many ways to answer a question especially in SQL.

If you have a different way, please share!

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


7 + 2 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>