How can I rewrite a multi-column IN clause to work on SQLite?

Question

I've got a query that looks something like this:

SELECT   * FROM table WHERE   (col1, col2) in (     ('col1_val1', 'col2_val1'),     ('col1_val2', 'col2_val2'),     ('col1_val3', 'col2_val3'),   ) 

This works in MySQL, but fails in sqlite3 with a syntax error:

Error: near ",": syntax error 

How can I rewrite this query to an equivalent one that works in sqlite3?

Answer 1

Choose your favourite version:

http://sqlfiddle.com/#!5/6169b/9

using temporary table

CREATE TEMPORARY TABLE pair (a INTEGER, b INTEGER); INSERT INTO pair (a, b) VALUES (1, 1); INSERT INTO pair (a, b) VALUES (2, 2); .... 

data IN pairs; if pair(a,b) is not unique

SELECT * FROM data WHERE EXISTS (   SELECT NULL   FROM  pair   WHERE pair.a = data.a     AND pair.b = data.b ); 

data IN pairs; if pair(a,b) is unique

SELECT     data.* FROM       data INNER JOIN pair         ON pair.a = data.a        AND pair.b = data.b; 

data NOT IN pairs; if pair(a,b) is unique

SELECT    data.* FROM      data LEFT JOIN pair    ON pair.a = data.a   AND pair.b = data.b WHERE pair.a IS NULL    OR pair.b IS NULL; 

using inline table

data IN pairs; if pair(a,b) is not unique

SELECT * FROM data WHERE EXISTS (   SELECT NULL   FROM  (     SELECT 1 AS a, 1 AS b     UNION ALL     SELECT 2 AS a, 2 AS b     UNION ALL     SELECT 3 AS a, 3 AS b   ) AS pair   WHERE pair.a = data.a     AND pair.b = data.b ); 

data IN pairs; if pair(a,b) is unique

SELECT     data.* FROM       data INNER JOIN (   SELECT 1 AS a, 1 AS b   UNION ALL   SELECT 2 AS a, 2 AS b   UNION ALL   SELECT 3 AS a, 3 AS b ) AS pair  ON  pair.a = data.a AND  pair.b = data.b; 

data NOT IN pairs; if pair(a,b) is unique

SELECT    data.* FROM      data LEFT JOIN (   SELECT 1 AS a, 1 AS b   UNION ALL   SELECT 2 AS a, 2 AS b   UNION ALL   SELECT 3 AS a, 3 AS b  ) AS pair    ON pair.a = data.a   AND pair.b = data.b WHERE pair.a IS NULL    OR pair.b IS NULL;