sql

How to use anti join on two similar tables

I have two tables.

tableA

ID      

001
002
003
004

tableB


ID

002
003
004
005

All I want to do is write a SQL statement that grabs all the accounts that are only in tableA. In this case it would only return

ID
001

Thank you in advance!

Solution

  • There are several ways to do that, some of them are displayed in following examples. Note that database you use might, or might not support some of them. Also, some might perform better than others (if you deal with large data sets).

    minus set operator:

    SQL> select id from tablea
  2  minus
  3  select id from tableb;

ID
---
001

    Similarly, except set operator:

    SQL> select id from tablea
  2  except
  3  select id from tableb;

ID
---
001

    Query that uses not exists:

    SQL> select id from tablea a
  2  where not exists (select null from tableb b
  3                    where b.id = a.id);

ID
---
001

    Query that uses not in:

    SQL> select id from tablea
  2  where id not in (select id from tableb);

ID
---
001

SQL>