SQL IN
IN tests whether a value matches any value in a list. One IN test can check several values at once.
Match a list of cities
IN checks the city against three values. A match with any one value passes.
SELECT name, city FROM customers WHERE city IN ('Lisbon', 'Berlin', 'Dubai') ORDER BY name;Match a list of categories
The list here holds two category names. One product from each category passes.
SELECT name, category FROM products WHERE category IN ('Bags', 'Home') ORDER BY name;Reject a list with NOT IN
NOT IN flips the list test. Customers outside the USA and Canada pass.
SELECT name, country FROM customers WHERE country NOT IN ('USA', 'Canada') ORDER BY name;Example
SELECT name, category, price FROM products WHERE category IN ('Kitchen', 'Electronics') AND price < 10 ORDER BY price;Key points
- IN: a test that matches a value against a list
- The list sits inside parentheses, with commas between the values.
- Use IN to replace a chain of OR tests on one column.
- NOT IN: a test that rejects every value in the list
- Text values in the list wear single quotes.
In this example, IN keeps the Kitchen and Electronics products. The price test then keeps only products under 10. Two products pass both tests.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
What does the IN test check?