EXISTS vs JOIN and use of EXISTS clause

by

EXISTS is used to return a boolean value, JOIN returns a whole other table

EXISTS is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation.

In your example, the queries are semantically equivalent.

In general, use EXISTS when:

  • You don’t need to return data from the related table
  • You have dupes in the related table (JOIN can cause duplicate rows if values are repeated)
  • You want to check existence (use instead of LEFT OUTER JOIN...NULL condition)

If you have proper indexes, most of the time the EXISTS will perform identically to the JOIN. The exception is on very complicated subqueries, where it is normally quicker to use EXISTS.

If your JOIN key is not indexed, it may be quicker to use EXISTS but you will need to test for your specific circumstance.

JOIN syntax is easier to read and clearer normally as well.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Insert an image in a column of a table that already has 5 columns
  3. How to avoid the “divide by zero” error in SQL?
  4. SET versus SELECT when assigning variables?
  5. Querying data by joining two tables in two database on different servers
  6. sql query to return differences between two tables
  7. Real life example, when to use OUTER / CROSS APPLY in SQL
  8. How to print VARCHAR(MAX) using Print Statement?
  9. Concatenate values based on ID
  10. Splitting delimited values in a SQL column into multiple rows
  11. How to find current transaction level?
  12. Restrict an SQL Server connection to a specific IP address
  13. Cumulative Total in MS Sql server [duplicate]
  14. How do I find the data directory for a SQL Server instance?
  15. The multi-part identifier could not be bound
  16. Select statement to find duplicates on certain fields
  17. Is it possible to select sql server data using column ordinal position
  18. How to insert multiple records and get the identity value?
  19. How can a LEFT OUTER JOIN return more records than exist in the left table?
  20. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  21. How to copy a huge table data into another table in SQL Server
  22. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  23. Alternate of lead lag function in SQL Server 2008
  24. SQL server select distinct rows using most recent value only
  25. Script to save varbinary data to disk
  26. Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”
  27. Generate random int value from 3 to 6
  28. What is the internal representation of datetime in sql server?
  29. Eliminate and reduce overlapping date ranges
  30. Why do SQL Server Scalar-valued functions get slower?

Leave a Comment