Menu

SQL Multiple-Column IN Clause

The IN clause is handy. I use it all of the time.

SELECT  
  * 
FROM  
  cars
WHERE  
  make IN ('Ford', 'Subaru') AND
  price < 5000;

+--------+---------+------+--------+-------+
| make   | model   | year | color  | price |
+--------+---------+------+--------+-------+
| Ford   | Focus   | 2007 | Black  |  1100 |
| Ford   | Mustang | 2005 | Yellow |  4000 |
| Ford   | Fiesta  | 2011 | Yellow |  4500 |
| Subaru | Legacy  | 2003 | Yellow |   900 |
| Subaru | Impreza | 2005 | Red    |  1400 |
+--------+---------+------+--------+-------+

Today I came across a two-column IN clause in a code review:

SELECT  
  * 
FROM  
  cars
WHERE  
  (make, color) IN (
    ('Ford', 'Yellow'), 
    ('Subaru', 'Red')
  ) AND
  price < 5000;

+--------+---------+------+--------+-------+
| make   | model   | year | color  | price |
+--------+---------+------+--------+-------+
| Ford   | Mustang | 2005 | Yellow |  4000 |
| Ford   | Fiesta  | 2011 | Yellow |  4500 |
| Subaru | Impreza | 2005 | Red    |  1400 |
+--------+---------+------+--------+-------+

How’d I go my whole life without this? Good stuff.