Aug 192013
 
select w.an_alternative_way,
       w.to_write_an_antijoin
from   the_rows_you_want w
left   join the_rows_you_dont d
on     w.the_condition = d.to_exclude_rows
where  d.use_of_not_exists is null;

Personally I prefer to write these queries as not exists as I find them a bit easier 
understand. In some cases an outer join with an is null predicate can perform better 
as the optimizer can use different join methods (e.g. hash outer join).

As usual, there’s a question on asktom where Tom Kyte gives his thoughts on this. For a script comparing this to the not exists version, keep reading.

-- create your tables
create table the_rows_you_want (
  an_alternative_way integer not null,
  to_write_an_antijoin integer not null,
  the_condition integer not null  
);

create table the_rows_you_dont (
  to_exclude_rows integer not null,
  use_of_not_exists integer not null
);

-- stick some data in
insert into the_rows_you_want 
  select rownum , rownum , rownum
  from   dual
  connect by level <= 1000;
  
insert into the_rows_you_dont
  select rownum , rownum 
  from   dual
  connect by level <= 100;
  
-- the outer join method for not exists
select w.an_alternative_way,
       w.to_write_an_antijoin
from   the_rows_you_want w
left   join the_rows_you_dont d
on     w.the_condition = d.to_exclude_rows
where  d.use_of_not_exists is null;

-- the "normal" not exists method
select an_alternative_way,
       to_write_an_antijoin
from   the_rows_you_want
where  not exists (
    select null 
    from   the_rows_you_dont
    where  the_condition = to_exclude_rows);
              
-- we can see these queries are equivalent as 
-- the  following returns no rows
(
select w.an_alternative_way,
       w.to_write_an_antijoin
from   the_rows_you_want w
left   join the_rows_you_dont d
on     w.the_condition = d.to_exclude_rows
where  d.use_of_not_exists is null
minus
select an_alternative_way,
       to_write_an_antijoin
from   the_rows_you_want
where  not exists (
    select null 
    from   the_rows_you_dont
    where  the_condition = to_exclude_rows)
)
union all 
(
select an_alternative_way,
       to_write_an_antijoin
from   the_rows_you_want
where  not exists (
    select null 
    from   the_rows_you_dont
    where  the_condition = to_exclude_rows)
minus
select w.an_alternative_way,
       w.to_write_an_antijoin
from   the_rows_you_want w
left   join the_rows_you_dont d
on     w.the_condition = d.to_exclude_rows
where  d.use_of_not_exists is null
);

drop table the_rows_you_dont purge;
drop table the_rows_you_want purge;

  2 Responses to “An Alternative Form Of Not Exists”

  1. Chris,

    thanks for pointing to the asktom-query initiated by me !

    Since this discussion, I usually use “not exists” whenever possible.

    Matthias aka Sokrates

    • No worries – I hadn’t noticed that the question was from you!

      I work on a database with a set of history tables which everyone uses the “outer join, is null” to get the correct results. It took me a while to realise that is because to get the current record we need one where there doesn’t exist a history record. It would have been much quicker if the queries used “not exists”!

      Still there are some cases where the outer join performs better, I haven’t seen many though.

 Leave a Reply

(required)

(required)

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