Select Non Existent Record Without (NOT) Keyword for EXISTS/IN
Select Non Existent Record Without (NOT) Keyword for EXISTS/IN
By Kasim Wirama, MCDBA, MVP SQL Server
Usually, I am taught to display non existent record on one table by referencing some columns to the other tables with NOT EXISTS/NOT IN help.
From performance perspective, it gives suboptimal performance, because it doesn’t give efficient index usage (index scan).
So, how to avoid index scan, and change to better query to populate index seek instead?
Let’s consider this scenario below :
Table A contains records (1,’a’), (2,’b’), (3,’c’) with primary key first column (1,2 and 3).
Table B contains records (1,’a’), (2,’b’), (3,’c’), (4,’d’) with primary key first column (1,2,3,4)
I am asked to display records that exist in table B, but it does not exist in table A.
As my bad habit without got aware before, I give solution:
select b.cola, b.colb from “table B” where not exists (select cola from “table A” on “table A”.cola = “table B”.cola);
The same question has ever been asked in technical interview, I had no idea at that time. J
Finally, my colleague suggested to use left/right join, I think it was other alternative besides using NOT EXISTS/NOT IN, I came up with this query:
Select b.cola, b.colb from “table B” left outer join “table A” on “table A”.cola = “table B”.cola where “table A”.cola is null;
Be careful when you think, as I thought before, that the third query below will give same results as second query does.
Select b.cola, b.colb from “table B” left outer join “table A” on “table A”.cola = “table B”.cola and “table A”.cola is null;
Remember that filtering happens after all joining process among tables is done, if you use WHERE, otherwise all condition is processed before WHERE will be finalised.
Just aware about sequence of query engine process a query, it will help you avoid incorrect result between second and third query.
Without compromise query result, the second query gives better alternative compared to first one.