SQL Joins Vs SQL Subqueries (Performance)?

by

I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by “OR” (WHERE x=Y OR x=Z OR...).

As with ALL THINGS SQL though, your mileage may vary. The speed will depend a lot on indexes (do you have indexes on both ID columns? That will help a lot…) among other things.

The only REAL way to tell with 100% certainty which is faster is to turn on performance tracking (IO Statistics is especially useful) and run them both. Make sure to clear your cache between runs!

More Related Contents:

  1. Join vs. sub-query
  2. SQL JOIN: is there a difference between USING, ON or WHERE?
  3. Subqueries vs joins
  4. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  5. Multiple INSERT statements vs. single INSERT with multiple VALUES
  6. Is a JOIN faster than a WHERE?
  7. Why is a UDF so much slower than a subquery?
  8. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  9. SQL Server SELECT LAST N Rows
  10. SQL server join tables and pivot
  11. When using GETDATE() in many places, is it better to use a variable?
  12. IN vs. JOIN with large rowsets
  13. sql query joins multiple tables – too slow (8 tables)
  14. Procedurally transform subquery into join
  15. How to display data in JOIN, and return null value? [closed]
  16. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  17. How to set table name in dynamic SQL query?
  18. SQL ‘like’ vs ‘=’ performance
  19. How many rows in a database are TOO MANY?
  20. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  21. Does SparkSQL support subquery?
  22. Return pre-UPDATE column values using SQL only
  23. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  24. SQL Server 2008 – IF NOT EXISTS INSERT ELSE UPDATE
  25. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  26. Query runs slow with date expression, but fast with string literal
  27. Improving performance of cluster index GUID primary key
  28. The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range?
  29. How to execute a stored procedure inside a select query
  30. SQL Inner Join On Null Values

Leave a Comment