NOT IN, NOT EXISTS and MINUS: an aide-memoir

A colleague asked me whether NOT IN would return the same as MINUS. I said it would depend on whether the results contained nulls. I confess to not being clear as to how the results would be affected by the presence of nulls, but it's easy enough to knock up a test case.

We start with both tables containing nulls:

SQL> select id from a10
2 /
ID
----------
1

3

3 rows selected.

SQL> select id from a20
2 /
ID
----------
3
2

4

4 rows selected.

SQL> select * from a10
2 where id not in ( select id from a20)
3 /

no rows selected

SQL> select * from a10
2 where not exists
3 (select id from a20 where a20.id = a10.id)
4 /
ID
----------
1


2 rows selected.

SQL> select * from a10
2 minus
3 select * from a20
4 /
ID
----------
1

1 row selected.

With a null in the top table but not in the bottom table:

SQL> delete from a20 where id is null
2 /

1 row deleted.

SQL> select * from a10
2 where id not in ( select id from a20)
3 /
ID
----------
1

1 row selected.

SQL> select * from a10
2 where not exists
3 (select id from a20 where a20.id = a10.id)
4 /
ID
----------
1


2 rows selected.

SQL> select * from a10
2 minus
3 select * from a20
4 /
ID
----------
1


2 rows selected.

SQL>

With a null in the bottom table but not in the top table:


SQL> delete from a10 where id is null
2 /

1 row deleted.

SQL> insert into a20 values (null)
2 /

1 row created.

SQL> select * from a10
2 where id not in ( select id from a20)
3 /

no rows selected

SQL> select * from a10
2 where not exists
3 (select id from a20 where a20.id = a10.id)
4 /
ID
----------
1

1 row selected.

SQL> select * from a10
2 minus
3 select * from a20
4 /
ID
----------
1

1 row selected.

SQL>

With no nulls in either table:

SQL> delete from a20 where id is null
2 /

1 row deleted.

SQL> select * from a10
2 where id not in ( select id from a20)
3 /
ID
----------
1

1 row selected.

SQL> select * from a10
2 where not exists
3 (select id from a20 where a20.id = a10.id)
4 /
ID
----------
1

1 row selected.

SQL> select * from a10
2 minus
3 select * from a20
4 /
ID
----------
1

1 row selected.

SQL>