Is there a best practice between using a LEFT JOIN or a NOT EXISTS format?
What is benefit to using one over the other?
If none, which should be preferred?
SELECT *
FROM tableA A
LEFT JOIN tableB B
ON A.idx = B.idx
WHERE B.idx IS NULL
SELECT *
FROM tableA A
WHERE NOT EXISTS
(SELECT idx FROM tableB B WHERE B.idx = A.idx)
I am using queries within Access against a SQL Server database.
The biggest difference is not in the join vs not exists, it is (as written), the SELECT *.
On the first example, you get all columns from both A and B, whereas in the second example, you get only columns from A.
In SQL Server, the second variant is slightly faster in a very simple contrived example:
Create two sample tables:
CREATE TABLE dbo.A
(
A_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
);
CREATE TABLE dbo.B
(
B_ID INT NOT NULL
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
);
GO
Insert 10,000 rows into each table:
INSERT INTO dbo.A DEFAULT VALUES;
GO 10000
INSERT INTO dbo.B DEFAULT VALUES;
GO 10000
Remove every 5th row from the second table:
DELETE
FROM dbo.B
WHERE B_ID % 5 = 1;
SELECT COUNT(*) -- shows 10,000
FROM dbo.A;
SELECT COUNT(*) -- shows 8,000
FROM dbo.B;
Perform the two test SELECT statement variants:
SELECT *
FROM dbo.A
LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;
SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
FROM dbo.B
WHERE b.B_ID = a.A_ID);
Execution plans:
The second variant does not need to perform the filter operation since it can use the left anti-semi join operator.
Logically they are identical, but NOT EXISTS is closer to the AntiSemiJoin that you're asking for, and is generally preferred. It also highlights better that you can't access the columns in B, because it's only used as a filter
(as opposed to having them available with NULL values).
Many years ago (SQL Server 6.0 ish), LEFT JOIN was quicker, but that hasn't been the case for a very long time. These days, NOT EXISTS is marginally faster.
The biggest impact in Access is that the JOIN method has to complete the join before filtering it, constructing the joined set in memory. Using NOT EXISTS it checks for the row but doesn't allocate space for the columns. Plus, it stops looking once it finds a row. Performance varies a bit more in Access, but a general rule of thumb is that NOT EXISTS tends to be a little faster. I'd be less inclined to say it's "best practice", as there are more factors involved.
An exception I've noticed to the NOT EXISTS being superior (however marginally) to LEFT JOIN ... WHERE IS NULL is when using Linked Servers.
From examining the execution plans, it appears that NOT EXISTS operator gets executed in a nested loop fashion. Whereby it is executed on a per row basis (which I suppose makes sense).
Example execution plan demonstrating this behaviour:
In general, the engine will create an execution plan based essentially on:
For (4):
The "not exists" plan encourages a seek based plan on table B. This is a good choice when table A is small and table B is large (and an index exists on B).
The "antijoin" plan is a good choice when table A is very large or table B is very small or no index on B and returning a large result set.
However it is just an "encouragement", like a weighted input. A strong (1),(2),(3) often makes the choice for (4) moot.
( Ignoring the effect of your example returning different columns due to the *. ).
In Oracle
NOT IN and NOT EXISTS clauses are not the same functionally or performance wise and,therefore, should be used appropriately.
in some cases they are totally different the NOT EXISTS will return only record from table A when there is no record in B matches, but in all type of 'Joins' (left/right full join) it Wil return all records from A and the intersection between two table A and B which 'NOT EXISTS' will not do
or the only the intersection between two tables or all records from A and B plus the intersection
