Where Not Exists!?!

steve_o · #1 (**permalink**) 06-29-04, 06:25

I am trying to find all items in one table that do not exist in another table.

The following query of my config table returns one row (which is '350'):
SELECT config.pos from config

The following query of my gsl table returns 47 rows (one of which is '350'):
SELECT gsl.pos from gsl

I do not understand why the follwing query returns no rows, when I would like it to return all rows (except for the one that is in the config table):
SELECT gsl.pos from gsl
WHERE NOT EXISTS (SELECT config.pos from config)

Any ideas would be much appreciated..

r937 · #2 (**permalink**) 06-29-04, 06:34

it's not working because the subquery returns all the rows in the config table and therefore the outer query doesn't return anything

there are two ways to do what you want

Code:

select gsl.pos 
  from gsl
 where not exists 
     ( select pos 
         from config
        where pos = gsl.pos )
        
select gsl.pos 
  from gsl
left outer
  join config
    on gsl.pos 
     = config.pos
 where config.pos is null