SQL Left Join

[TheFaQQer]

If the column in one of the tables is nullable, and in the other table is not nullable, then Oracle uses the same execution plan regardless of whether you use NOT EXISTS or a join:

SQL > create table cuk1(id number,
  2     constraint cuk1_pk primary key(id));

Table created.

SQL > create table cuk2(id number);

Table created.

SQL > begin
  2    for i in 1..100 loop
  3      insert into cuk1 values(i);
  4    end loop;
  5    commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL > begin
  2    for i in 1..100000 loop
  3      insert into cuk2
  4        values(mod(i,97));
  5    end loop;
  6    commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL > create index cuk2_idx on cuk2(id);

Index created.

SQL > exec dbms_stats.gather_table_stats(USER,'CUK1');

PL/SQL procedure successfully completed.

SQL > exec dbms_stats.gather_table_stats(USER,'CUK2');

PL/SQL procedure successfully completed.

SQL > -- Not exists check.....
SQL > explain plan for
  2    select id from cuk1 a
  3     where not exists
  4      (select 1 from cuk2 b where b.id=a.id);

Explained.

SQL > select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1819530608

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     4 |    24 |    50   (4)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |         |     4 |    24 |    50   (4)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | CUK1_PK |   100 |   300 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| CUK2    |   100K|   292K|    48   (3)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."ID"="A"."ID")

15 rows selected.

SQL > -- Outer join check
SQL > explain plan for
  2    select a.id
  3    from cuk1 a
  4    left outer join cuk2 b on (a.id = b.id)
  5    where b.id is null;

Explained.

SQL > select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1819530608

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     4 |    24 |    50   (4)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |         |     4 |    24 |    50   (4)| 00:00:01 |
|   2 |   INDEX FULL SCAN  | CUK1_PK |   100 |   300 |     1   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| CUK2    |   100K|   292K|    48   (3)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID"="B"."ID")

15 rows selected.

SQL > drop table cuk1;

Table dropped.

SQL > drop table cuk2;

Table dropped.

[TheFaQQer]

Wouldn't using a not exists clause work better?

eg: select a* from tableA a
where not exists (select * from TableB b where b.id=a.id)

One more thing....

You'd need to include a check for both columns being null in the two tables, if the database doesn't use an equals operator on nulls (e.g. Oracle)

SELECT a.*
FROM   tableA a
WHERE  NOT EXISTS ( SELECT *
                    FROM   tableB b
                    WHERE  b.id = a.id
                    OR     ( b.id IS NULL and a.id IS NULL )
                  )

[wurzel]

It depends on whether the column is nullable or not, and (probably) which database you are using.

In MySQL, for example, left join performs well where both columns are not-nullable. However, it performs badly where one or both of the columns is nullable, in which case an EXISTS / NOT EXISTS would perform better.

You could also use a NOT IN instead of NOT EXISTS, which (I think!) would work best, although if the column is nullable then you would need to include an additional check for nulls.

Using SQl Server 2008 here. Will run both variants through Query Analyzer & look at execution plans when I've got some time.