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

Here's an easy solution that works, but it might not perform well on large data sets because it can't use any of your indexes.

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

Try it in sqlfiddle

Enjoy!

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;