Difference between EXISTS and IN in SQL?

The exists keyword can be used in that way, but really it’s intended as a way to avoid counting:

--this statement needs to check the entire table
select count(*) from [table] where ...

--this statement is true as soon as one match is found
exists ( select * from [table] where ... )

This is most useful where you have if conditional statements, as exists can be a lot quicker than count.

The in is best used where you have a static list to pass:

 select * from [table]
 where [field] in (1, 2, 3)

When you have a table in an in statement it makes more sense to use a join, but mostly it shouldn’t matter. The query optimiser should return the same plan either way. In some implementations (mostly older, such as Microsoft SQL Server 2000) in queries will always get a nested join plan, while join queries will use nested, merge or hash as appropriate. More modern implementations are smarter and can adjust the plan even when in is used.

Related Posts:

  1. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  2. SQL WITH clause example [duplicate]
  3. The wait operation timed out. ASP
  4. Conversion failed when converting date and/or time from character string while inserting datetime
  5. how to fix oracle ORA-01722 invalid number error
  6. SQL query to select dates between two dates
  7. MySQL – Operand should contain 1 column(s)
  8. SQL SELECT WHERE field contains words
  9. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  10. NOT IN vs NOT EXISTS
  11. Must declare the scalar variable
  12. ORA-00979 not a group by expression
  13. How can I do a FULL OUTER JOIN in MySQL?
  14. MySQL query String contains
  15. How do I escape a single quote in SQL Server?
  16. T-SQL split string based on delimiter
  17. Finding duplicate values in a SQL table
  18. MySQL Cannot Add Foreign Key Constraint
  19. Oracle error : ORA-00905: Missing keyword
  20. How do I import an SQL file using the command line in MySQL?
  21. mysql Foreign key constraint is incorrectly formed error
  22. Can a foreign key be NULL and/or duplicate?
  23. How do I do multiple CASE WHEN conditions using SQL Server 2008?
  24. How do composite indexes work?
  25. Difference between JOIN and INNER JOIN
  26. MySQL “WITH” clause
  27. What is the difference between JOIN and UNION?
  28. MySQL Multiple Joins in one query?
  29. Error Code: 2013. Lost connection to MySQL server during query
  30. “CASE” statement within “WHERE” clause in SQL Server 2008
  31. SQL join on multiple columns in same tables
  32. ORA-00972 identifier is too long alias column name
  33. Sql query – getting rid of hard-coded values
  34. ORA-00918: column ambiguously defined in SELECT *
  35. Combining “LIKE” and “IN” for SQL Server
  36. How To Run A Github Repository?
  37. SQL Switch/Case in ‘where’ clause
  38. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  39. SQL Server WITH statement
  40. How to run a SQL query on an Excel table?
  41. SQL Server reports ‘Invalid column name’, but the column is present and the query works through management studio
  42. Oracle “Partition By” Keyword
  43. What is the equivalent of ‘describe table’ in SQL Server?
  44. Add a column with a default value to an existing table in SQL Server
  45. Remote table-Valued Function Calls are not allowed
  46. The backend version is not supported to design database diagrams or tables
  47. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  48. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  49. Difference between View and table in sql
  50. What is the purpose of using WHERE 1=1 in SQL statements?
  51. Exclude a column using SELECT * [except columnA] FROM tableA?
  52. How do you force mysql LIKE to be case sensitive?
  53. What is the difference between a stored procedure and a view?
  54. Why is SQL server throwing this error: Cannot insert the value NULL into column ‘id’?
  55. pg_ctl: no database directory specified and environment variable PGDATA unset
  56. ERROR 1148: The used command is not allowed with this MySQL version
  57. Bulk load data conversion error (truncation)
  58. How to order by with union in SQL?
  59. Difference between INNER JOIN and LEFT SEMI JOIN
  60. SQL conditional SELECT
  61. ‘CREATE PROCEDURE’ must be the only statement in the batch (Erro)
  62. PostgreSQL create table if not exists
  63. PostgreSQL visual interface similar to phpMyAdmin?
  64. DATEDIFF function in Oracle
  65. Solutions for INSERT OR UPDATE on SQL Server
  66. GROUP BY and COUNT using ActiveRecord
  67. MySQL – Get row number on select
  68. MySQL error: Unknown column in ‘where clause’
  69. What does * mean in sql?
  70. How to truncate the text returned for a column in a MySQL query
  71. INSERT VALUES WHERE NOT EXISTS
  72. Using union and order by clause in mysql
  73. How to implement LIMIT with SQL Server? [duplicate]
  74. What’s the best way to dedupe a table?
  75. Moving database with phpMyAdmin
  76. How To Write An Inner Join With WP Query
  77. How do I escape a table name or column name in SQL? esc_sql doesn’t do this
  78. Update user_login, user_nicename, and display_name
  79. Change sticky status of posts from phpMyAdmin
  80. Fetch all Posts where logged in user has commented
  81. Best Way to Merge a Dev and Live Site to Become a Staging Site?
  82. Needing to move content from postmeta to posts in sql [closed]
  83. How to import a Typo3 database to a wordpress site?
  84. What does the $posts_join filter join to?
  85. Clean up very big and very dirty database
  86. How to delete ALL comments from certain category in WordPress database?
  87. How can I convert everything from category X to have post format Link
  88. Bulk delete WordPress Post and all metadata, etc using SQL query
  89. What steps do I need to take to install a local copy of a live website?
  90. Reset post IDs with all post meta
  91. How to Add or Change Post Title
  92. Why does DROP TABLE-ing the `wp_options` reset my user session?
  93. How to refactor DB queries for better TTFB in WordPress?
  94. query sql-table and change entities
  95. Duplicate WP Migration affecting site on separate domain?
  96. Analog category_and (WP) in sql query
  97. Backtick (MySQL norm) added to SQL Server Query causing error
  98. SQL query to check whether a meta key is set or not for a post in post_meta table
  99. Firebase with WordPress instead of SQL?
  100. Want to delete woocommerce coupon in bulk from phpmyadmin based on published date

Leave a Comment