Should I use OUTER JOIN instead of NOT EXISTS?

NOT EXISTS in a query can readily be replaced by an OUTER JOIN.
Here is how

CREATE TABLE Emp(EmpID INT, EmpName VARCHAR(20))
INSERT INTO Emp(EmpID, EmpName)
SELECT 1, 'Amit' UNION
SELECT 2, 'Sachin' UNION
SELECT 3, 'Sonam'

CREATE TABLE OnLeave(OnLeaveID INT, EmpID INT)
INSERT INTO OnLeave(OnLeaveID, EmpID)
SELECT 1, 2 UNION
SELECT 2, 3

--Using NOT EXISTS
SELECT Emp.EmpName FROM Emp
WHERE NOT EXISTS
	(SELECT 1 FROM OnLeave 
	WHERE OnLeave.EmpID = Emp.EmpID)

--Using LEFT OUTER JOIN
SELECT Emp.EmpName FROM Emp
LEFT OUTER JOIN OnLeave ON OnLeave.EmpID = Emp.EmpID
WHERE OnLeave.OnLeaveID IS NULL

This is because of the fact that in an LEFT OUTER JOIN every row in the left table is represented in the result set, whether or not there are any matching rows from the right table. If for a particular row of the left table there is no matching row from the right table, then the columns in that row which would have come from the right table will be NULL. The test for NULL should be made on a column that you are sure it wont contain a NULL value; for example primary key of the right table. Also it should be noted that a record will be repeated if there are more than one match in the right table. In that case we will have to use DISTINCT in the select line.

If proper indexes are in place, the OUTER JOIN method and WHERE EXISTS method are basically identical when it comes to performance. However there have been many case when I have replaced a NOT EXISTS with an OUTER JOIN and got reduced execution time. People keep on telling that both have almost identical query plan. And you should opt for NOT EXISTS as it is more readable. But I feel LEFT OUTER JOINT method is also equally readable. For example

UPDATE TblOne SET Errors = 1
FROM TblOne
WHERE NOT EXISTS
	(SELECT 1 FROM TblTwo
	WHERE TblTwo.TblOneId = TblOne.TblOneId
	AND TblTwo.Col1 = TblOne.Col1
	AND TblTwo.Chk = 'Y' )

UPDATE TblOne SET Errors = 1
FROM TblOne
LEFT OUTER JOIN TblTwo
	ON TblTwo.TblOneId = TblOne.TblOneId
	AND TblTwo.Col1 = TblOne.Col1
	AND TblTwo.Chk = 'Y'
WHERE TblTwo.TblTwoId IS NULL

What I do is, I keep both options handy. For queries with a very low execution time I go for NOT EXISTS just for the fact that it is easier for others to later understand what I am doing in the query. For heavier queries which have higher execution time I try both options and keep the one which gives better execution time. In general I have noticed that replacing NOT EXISTS with OUTER JOIN gives performance improvement when used in DELETE and UPDATE statements.

About these ads