SQL performance on LEFT OUTER JOIN vs NOT EXISTS

by

Joe’s link is a good starting point. Quassnoi covers this too.

In general, if your fields are properly indexed, OR if you expect to filter out more records (i.e. have a lots of rows EXIST in the subquery) NOT EXISTS will perform better.

EXISTS and NOT EXISTS both short circuit – as soon as a record matches the criteria it’s either included or filtered out and the optimizer moves on to the next record.

LEFT JOIN will join ALL RECORDS regardless of whether they match or not, then filter out all non-matching records. If your tables are large and/or you have multiple JOIN criteria, this can be very very resource intensive.

I normally try to use NOT EXISTS and EXISTS where possible. For SQL Server, IN and NOT IN are semantically equivalent and may be easier to write. These are among the only operators you will find in SQL Server that are guaranteed to short circuit.

More Related Contents:

  1. SQL Server dynamic PIVOT query?
  2. SQL Server Process Queue Race Condition
  3. SQL Server 2005 Pivot on Unknown Number of Columns
  4. SQL – Subtracting a depleting value from rows
  5. SQL query to split column data into rows
  6. Calculating number of full months between two dates in SQL
  7. Dynamic update statement with variable column names
  8. SQL Server (TSQL) – Is it possible to EXEC statements in parallel?
  9. Difference of two date time in sql server
  10. SQL Server SELECT INTO @variable?
  11. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  12. How do I change db schema to dbo
  13. How to create a Date in SQL Server given the Day, Month and Year as Integers
  14. Select a column if other column is null
  15. How to subtract 30 days from the current date using SQL Server
  16. Add a row number to result set of a SQL query
  17. Count records for every month in a year
  18. Is there a severe performance hit for using Foreign Keys in SQL Server?
  19. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  20. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  21. Backup SQL Schema Only?
  22. Date serial in SQL?
  23. How to get the next number in a sequence
  24. SQL Server: +(unary) operator on non-numeric Strings
  25. Use one CTE many times
  26. Parameterise table name in .NET/SQL?
  27. How to Select Every Row Where Column Value is NOT Distinct
  28. How to insert into a table with just one IDENTITY column?
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment