Thread: How do you do a negative join?
There must be a standard SQL method to query multiple tables in the following way. Lets say we have two tables, 'allnames' and 'badnames'. We want to get the following result: "select name from allnames where name-is-not-in-badnames;" Clearly I'm an SQL novice but I haven't found any examples of such a query in my beginning SQL books. Thanks.
Jay, > There must be a standard SQL method to query multiple > tables in the following way. Lets say we have two > tables, 'allnames' and 'badnames'. We want to get the > following result: > > "select name from allnames where name-is-not-in-badnames;" > > Clearly I'm an SQL novice but I haven't found any examples > of such a query in my beginning SQL books. Easy, two syntaxes: SELECT allnames.name WHERE allnames.name NOT IN (SELECT badnames.name FROM badnames); OR SELECT allnames.name WHERE NOT EXISTS (SELECT badnames.name WHERE badnames.name = allnames.name); The second is better for PostgreSQL versions 7.2 and 7.3. The first is better for version 7.4. -- -Josh Berkus Aglio Database Solutions San Francisco
On Sat, Mar 20, 2004 at 13:32:08 -0800, Jay Davis <dj00302003@yahoo.com> wrote: > There must be a standard SQL method to query multiple > tables in the following way. Lets say we have two > tables, 'allnames' and 'badnames'. We want to get the > following result: > > "select name from allnames where name-is-not-in-badnames;" > > Clearly I'm an SQL novice but I haven't found any examples > of such a query in my beginning SQL books. These don't all have the same semantics, but in common cases (where name is a primary key) they will all give the same result. If there are NULLs or repeated values then you need to think about which one you want. select name from allnames where name not in (select name from badnames); select name from allnames where not exists( select 1 from badnames where allnames.name = badnames.name); select name from allnames except select name from badnames;