SQL: Performance comparison for exclusion (Join vs Not in)

Question

I am curious on the most efficient way to query exclusion on sql. E.g. There are 2 tables (tableA and tableB) which can be joined on 1 column (col1). I want to display the data of tableA for all the rows which col1 does not exist in tableB.

(So, in other words, tableB contains a subset of col1 of tableA. And I want to display tableA without the data that exists in tableB)

Let's say tableB has 100 rows while tableA is gigantic (more than 1M rows). I know 'Not in (not exists)' can be used but perhaps there are more efficient ways (less comp. time) to do it.? I don't maybe with outer joins?

Code snippets and comments are much appreciated.

Answer 1

Depends on the RDBMS. For Microsoft SQL Server NOT EXISTS is preferred to the OUTER JOIN as it can use the more efficient Anti-Semi join.

For Oracle Minus is apparently preferred to NOT EXISTS (where suitable)

You would need to look at the execution plans and decide.

Answer 2

I prefer to use

Select a.Col1
From TableA a
Left Join TableB b on a.Col1 = b.Col1
Where b.Col1 Is Null

I believe this will be quicker as you are utilising the FK constraint (providing you have them of course)

Sample data:

create table #a
(
Col1 int
)
Create table #b
(
col1 int
)

insert into #a
Values (1)
insert into #a
Values (2)
insert into #a
Values (3)
insert into #a
Values (4)

insert into #b
Values (1)
insert into #b
Values (2)


Select a.Col1
From #a a 
Left Join #b b on a.col1 = b.Col1
Where b.Col1 is null