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

Question

Hello,

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.

Thanks..

Answer 1

The questions has been asked several times. The often fastest way is to do this:

SELECT * FROM table1 
WHERE id in (SELECT id FROM table1 EXCEPT SELECT id FROM table2)

As the whole joining can be done on indexes, where using NOT IN it generally cannot.

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

Answer 3

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 4

There is no correct answer to this question. Every RDBMS has query optimizer that will determine best execution plan based on available indices, table statistics (number of rows, index selectivity), join condition, query condition, ...

When you have relatively simple query like in your question, there is often several ways you can get results in SQL. Every self respecting RDBMS will recognize your intention and will create same execution plan, no matter which syntax you use (subqueries with IN or EXISTS operator, query with JOIN, ...)

So, best solution here is to write simplest query that works and then check execution plan.
If that solution is not acceptable then you should try to find better query.