SQL WHERE ID IN (id1, id2, …, idn)

by

Option 1 is the only good solution.

Why?

  • Option 2 does the same but you repeat the column name lots of times; additionally the SQL engine doesn’t immediately know that you want to check if the value is one of the values in a fixed list. However, a good SQL engine could optimize it to have equal performance like with IN. There’s still the readability issue though…

  • Option 3 is simply horrible performance-wise. It sends a query every loop and hammers the database with small queries. It also prevents it from using any optimizations for “value is one of those in a given list”

More Related Contents:

  1. Whats wrong in this sql query? It throws error [closed]
  2. How do I UPDATE from a SELECT in SQL Server?
  3. SQL update from one Table to another based on a ID match
  4. SQL/mysql – Select distinct/UNIQUE but return all columns?
  5. COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better? [duplicate]
  6. SQL SELECT WHERE field contains words
  7. JOIN two SELECT statement results
  8. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  9. SQL SELECT speed int vs varchar
  10. Return a value if no record is found
  11. How to get a list of column names on Sqlite3 database?
  12. How to delete from select in MySQL?
  13. How can I select from list of values in Oracle
  14. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  15. Fastest way to determine if record exists
  16. How do I select all the columns from a table, plus additional columns like ROWNUM?
  17. Best way to do nested case statement logic in SQL Server
  18. How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
  19. SQL: how to select a single id (“row”) that meets multiple criteria from a single column
  20. MySQL: Select DISTINCT / UNIQUE, but return all columns?
  21. SQL column reference “id” is ambiguous
  22. SQL SELECT from multiple tables
  23. Can select * usage ever be justified?
  24. Select distinct from multiple fields using sql
  25. SQL: Two select statements in one query
  26. Check if two “select”s are equivalent
  27. SQL: Select rows with a column value that occurs at least N times?
  28. “SELECT *” vs “SELECT ” for performance
  29. Finding unmatched records with SQL
  30. Combine Two Tables in Select (SQL Server 2008)

Leave a Comment