sql - multiple layers of correlated subqueries

Question

I have table A, B and C

I want to return all entries in table A that do not exist in table B and of that list do not exist in table C.

select * from table_A as a
where not exists (select 1 from table_B as b
where a.id = b.id)

this gives me the first result of entries in A that are not in B. But now I want only those entries of this result that are also not in C.

I tried flavours of:

select * from table_A as a
where not exists (select 1 from table_B as b
where a.id = b.id)
AND
where not exists (select 1 from table_C as c
where a.id = c.id)

But that isnt the correct logic. If there is a way to store the results from the first query and then select * from that result that are not existent in table C. But I'm not sure how to do that. I appreciate the help.

Answer 1

Try this:

select * from (
  select a.*, b.id as b_id, c.id as c_id 
  from table_A as a
  left outer join table_B as b on a.id = b.id
  left outer join table_C as c on c.id = a.id
) T
where b_id is null
  and c_id is null

Another implementation is this:

select a1.*
from table_A as a1
inner join (
  select a.id from table_A
  except  
  select b.id from table_B
  except
  select c.id from table_c
) as a2 on a1.id = a2.id

Note the restrictions on the form of the sub-query as described here. The second implementation, by most succinctly and clearly describing the desired operation to SQL Server, is likely to be the most efficient.

Answer 2

You have two WHERE clauses in (the external part of) your second query. That is not valid SQL. If you remove it, it should work as expected:

select * from table_A as a
where not exists (select 1 from table_B as b
                  where a.id = b.id)
AND
      not exists (select 1 from table_C as c            -- WHERE removed
                  where a.id = c.id) ;

Tested in SQL-Fiddle (thnx @Alexander)