Not In operator will check for a particular value in the subquery list. Subquery will provide a list of values and our main query will check for the non availability of specific column values in the subquery list. It will use nested loops to fetch the data. We will see in details while analysing the execution plans.
SELECT NAME FROM VENKAT WHERE ID NOT IN (SELECT ID FROM VENKAT_SECOND_TABLE)
Not Exists operator will do a similar kind of operation but the matching is done with correlated join and it will utilise the index to fetch the records. So performance wise, this will give you an apt solution.
SELECT NAME FROM VENKAT V1 WHERE NOT EXISTS (SELECT ID FROM VENKAT_SECOND_TABLE V2 WHERE V2.ID=V1.ID)
Your code snippet,
DROP TABLE VENKAT
GO
CREATE TABLE VENKAT(ID INT, NAME VARCHAR(100))
INSERT INTO VENKAT VALUES(1,'VENKAT1')
INSERT INTO VENKAT VALUES(2,'VENKAT2')
INSERT INTO VENKAT VALUES(3,'VENKAT3')
GO
DROP TABLE VENKAT_SECOND_TABLE
GO
CREATE TABLE VENKAT_SECOND_TABLE(ID INT, NAME VARCHAR(100))
INSERT INTO VENKAT_SECOND_TABLE VALUES(1,'VENKAT1')
INSERT INTO VENKAT_SECOND_TABLE VALUES(2,'VENKAT2')
INSERT INTO VENKAT_SECOND_TABLE VALUES(4,'VENKAT4')
GO
SELECT NAME FROM VENKAT WHERE ID NOT IN (SELECT ID FROM VENKAT_SECOND_TABLE)
GO
SELECT NAME FROM VENKAT V1 WHERE NOT EXISTS (SELECT ID FROM VENKAT_SECOND_TABLE V2 WHERE V2.ID=V1.ID)
Both above statements were providing a similar kind of results. Let's see the execution plan for both,
From the execution plan, we can easily identify the performance comparison these two operators. Hope, you will be using not exists operator in your future queries:-)
Cheers,
Venkatesan Prabu .J
No comments:
Post a Comment