Can an INNER JOIN offer better performance than EXISTS

by

Generally speaking, INNER JOIN and EXISTS are different things.

The former returns duplicates and columns from both tables, the latter returns one record and, being a predicate, returns records from only one table.

If you do an inner join on a UNIQUE column, they exhibit same performance.

If you do an inner join on a recordset with DISTINCT applied (to get rid of the duplicates), EXISTS is usually faster.

IN and EXISTS clauses (with an equijoin correlation) usually employ one of the several SEMI JOIN algorithms which are usually more efficient than a DISTINCT on one of the tables.

See this article in my blog:

More Related Contents:

  1. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
  2. Is it possible to write single query on two table which are not connected to each other?
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. SQL update query using joins
  5. How to update two tables in one statement in SQL Server 2005?
  6. SQL query to select dates between two dates
  7. Create a date from day month and year with T-SQL
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Selecting COUNT(*) with DISTINCT
  10. How to return multiple values in one column (T-SQL)?
  11. SQL – Subtracting a depleting value from rows
  12. Is it possible to select sql server data using column ordinal position
  13. SELECT FOR UPDATE with SQL Server
  14. How can a LEFT OUTER JOIN return more records than exist in the left table?
  15. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  16. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  17. Dynamic SQL to generate column names?
  18. Column name or number of supplied values does not match table definition
  19. How to split string using delimiter char using T-SQL?
  20. How to find consecutive rows based on the value of a column?
  21. OPENXML with xmlns:dt
  22. How to check which locks are held on a table
  23. SQL server select distinct rows using most recent value only
  24. When using GETDATE() in many places, is it better to use a variable?
  25. Eliminate and reduce overlapping date ranges
  26. Cumulative Total in MS Sql server [duplicate]
  27. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  28. Compare dates in T-SQL, ignoring the time part
  29. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  30. Memory effective way to read BLOB data in C#/SQL 2005

Leave a Comment