SQL Server EXCEPT Clause

A few days back I had performance issues with a query in SQL Server 2005. The query was a nested query similar to the following (details omitted for brevity):

SELECT
   EmployeeID, DateAttended
FROM
   CourseDetails
WHERE
   EmployeeID NOT IN
   (
      SELECT 
         EmployeeID 
      FROM
         CourseDetails
      WHERE
         isActive = 0
   )

The above query would return all data rows where [Courses] are 'Active'. Due to the slow query, the web application was timing out. After doing some research, I finally came across the wonder EXCEPT clause in SQL Server 2005.

The EXCEPT clause returns all rows from the first query which are not present in the second query. It's more like a LEFT JOIN where rows from the left tables are returned. It is important to note that both the first and second query must have same data columns with similar data types.

Using the EXCEPT clause, we can now rewrite the above query as following:

SELECT
   EmployeeID, DateAttended
FROM
   CourseDetails
EXCEPT
   SELECT 
      EmployeeID 
   FROM
      CourseDetails
   WHERE
      isActive = 0

To my joy, the performance of the query was lightening fast. It really worked like a charm. So my advice would be to use the EXCEPT clause wherever possible and avoid nested queries.

I hope you found this blog use. Stay tuned for more.