Recommended Free Tools
Use IN to retrieve rows whose IDs match any of several values:
SELECT * FROM mydb WHERE id IN (5, 6);WHERE id = 5 AND id = 6 fails to match because it asks one row’s id to equal both numbers at the same time.
Why does AND return no rows?
AND requires both conditions to be true for the same row. For an ordinary ID column with one value per row, an ID cannot be both 5 and 6, so id = 5 AND id = 6 is false for every row.
Use IN for a list of IDs
IN tests whether the column value matches any value in the list. Put each ID in the parentheses, separated by commas:
#1 Best Overall
SELECT * FROM mydb WHERE id IN (5, 6);This returns rows whose id is 5 or 6. Add more values to the list when the page selection contains more IDs:
SELECT * FROM mydb WHERE id IN (5, 6, 9);When is OR an alternative?
For a short list, explicit comparisons with OR are equivalent:
Rank #2
SELECT * FROM mydb WHERE id = 5 OR id = 6;IN is usually easier to scan as the list grows. Both forms mean that either comparison may match; AND does not.
How to handle IDs selected on a web page
If the IDs come from a request or other application input, do not concatenate untrusted text into SQL. Use a prepared statement and bind each ID as a separate value. A placeholder represents one data value, so a variable-length IN list needs one placeholder for every ID.
Free tools Windows power users keep installed
One-click scans. No signup required.
SELECT * FROM mydb WHERE id IN (?, ?)Bind the selected IDs to those two placeholders using your database driver’s prepared-statement API. For a different number of IDs, create the same number of placeholders and bind every ID individually. MySQL documents that prepared statements help protect against SQL injection; parameter markers are for values, not table or column names. See the MySQL 8.4 prepared statements documentation.
Quick Recap
Best Value
Keep the values and query flow correct
- Use a consistent type for the IDs in the list. MySQL applies comparison type-conversion rules when evaluating
IN; mixing types can produce unexpected matches. The MySQL 8.4 comparison operators reference describesIN()and its conversion behavior. - Do not quote a comma-separated list as one value. A list such as
'5,6'is one string, not two separate IDs. - After preparing and executing a query, fetch results from its returned result set using the API for your database driver. Fetching cannot return rows from a query that was never executed successfully.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
