[thelist] SQL: Negative Join?

Joshua Olson joshua at waetech.com
Sun Sep 28 15:53:32 CDT 2003


----- Original Message ----- 
From: "Frank" <frank at frankmarion.com>
Sent: Sunday, September 28, 2003 4:16 PM


> I've got two tables each with client names and phone numbers. One table is
> a new list, the other is a list of clients to delete. Is there a way of
> doing a "negative join"? Something like
>
> SELECT k.columns
> FROM keepers k
> LEFT JOIN tossers t
>     ON k.client <> t.client;

Frank,

Based on your explanation I'm not certain what it is you want to do.  But
here's a guess:

Return data for all clients who do not have a matching table in the tossers
table.

There are two ways to do this:

1.  Brute force.  Use a subselect to make sure the record doesn't exist:

SELECT k.columns
FROM keepers k
WHERE k.client NOT IN (SELECT client FROM tossers)

2.  A little finesse.  Use an OUTER JOIN:

SELECT k.columns
FROM keepers k
LEFT OUTER JOIN tossers t
ON k.client = t.client
WHERE t.client IS NULL

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168



More information about the thelist mailing list