CODEWITHSUNDEEP
sqlsqlite
amashi
amashi

Reputation: 113

How do you do an IN query that has multiple columns in sqlite

I have a need to get columns for specific rows in the database, identified by more than one column. I'd like to do this in batches using an IN query.

In the single column case, it's easy:

SELECT id FROM foo WHERE a IN (1,2,3,4)

But I'm getting a syntax error when I try multi columns

SELECT id FROM foo WHERE (a,b) IN ((1,2), (3,4), (5,6))

Is there any way to do this? I can't just do two IN clauses because it potentially returns extra rows and also doesn't use the multi-column index as well.

Upvotes: 6

Views: 2275

Answers (4)

Larry Lustig
Larry Lustig

Reputation: 51000

This command should use an index on (a, b):

 SELECT id FROM foo WHERE a = 1 and b = 2
 UNION ALL
 SELECT id FROM foo WHERE a = 3 and b = 4
 UNION ALL
 SELECT id FROM foo WHERE a = 5 and b = 6

It's tedious to write if you have a length list of pairs, but that can be automated.

Upvotes: 2

CL.
CL.

Reputation: 180161

This is Declan_K's idea:
To be able to use multi-column indexes and not having to update a separate column, you have to put the lookup values into a table which you can join:

CREATE TEMPORARY TABLE lookup(a, b);
INSERT INTO lookup VALUES ...;
CREATE INDEX lookup_a_b ON lookup(a, b);
SELECT id FROM foo JOIN lookup ON foo.a = lookup.a AND foo.b = lookup.b;

(The index is not really necessary; if you omit it, the SQLite query optimizer will be forced to look up the lookup records in the foo table, instead of doing it the other way around.)

If you want to avoid the temporary table, you could construct it dynamically using a subquery:

SELECT id
FROM foo
JOIN (SELECT 1 AS a, 2 AS b UNION ALL
      SELECT 3     , 4      UNION ALL
      SELECT 5     , 6               ) AS lookup
 ON foo.a = lookup.a
AND foo.b = lookup.b

This will still be able to use an index on foo(a,b).

Upvotes: 4

Robert Harvey
Robert Harvey

Reputation: 180858

What I think you really want is another (string) column that contains the Key/Value pairs, so that you can query the combination specifically. Something like:

SELECT id FROM foo WHERE c IN ('1,2', '3,4', '5,6')

You can populate the new column with something like:

UPDATE foo SET c = a||','||b

Upvotes: 1

Declan_K
Declan_K

Reputation: 6826

You may need to convert a and b to strings but when that is done, concatenation will be the answer.

SELECT id FROM foo WHERE a||'_'||b IN ('1_2', '3_4', '5_6')

Upvotes: -1

Related Questions