How to increase the performance and reduce the execution time of sql query
In this article I'm going to explain how to increase the performance and reduce the execution time of sql query. To increase the performance and reduce the execution time of sql query Do not use Not In clause IN subquery use Left outer join instead.
In this article I'm going to explain how to increase the performance and reduce the execution time of sql query. To increase the performance and reduce the execution time of sql query Do not use Not In clause IN sub query use Left outer join instead.
Create tables:-
CREATE TABLE Staff(StaffID INT,StaffName VARCHAR(50))
CREATE TABLE ResginedStaff(StaffID INT,Date DATETIME)
Insert values into Created tables:-
INSERT INTO Staff VALUES(11,'Renganathan')
INSERT INTO Staff VALUES(22,'Ravi')
INSERT INTO Staff VALUES(33,'kumar')
INSERT INTO Staff VALUES(44,'Rajesh')
INSERT INTO Staff VALUES(55,'Prem')
INSERT INTO Staff VALUES(66,'Prem123')
insert into ResginedStaff VALUES(33,'2008-01-01')
insert into ResginedStaff VALUES(44,'2009-01-01')
insert into ResginedStaff VALUES(66,'2009-01-01')
Not in Query:-
SELECT * FROM Staff WHERE StaffID NOT IN (SELECT StaffID FROM ResginedStaff)
Output
StaffID StaffName
----------- --------------------------------------------------
11 Renganathan
22 Ravi
55 Prem
(3 row(s) affected)
Left outer Join Query
SELECT * FROM Staff S LEFT OUTER JOIN ResginedStaff R on s.StaffID=R.StaffID
WHERE R.DATE is NULL
Output
StaffID StaffName StaffID Date
----------- -------------------------------------------------- ----------- -----------------------
11 Renganathan NULL NULL
22 Ravi NULL NULL
55 Prem NULL NULL
(3 row(s) affected)