There are various ways to obtain multiple IDs with a single query in your situation.
First, you can just take the series of single SELECTs:
SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3;
SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6;
SELECT ID FROM FOO WHERE col1=7 AND col2=8 AND col3=9;
and combine them with UNION ALL:
SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3
UNION ALL
SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6
UNION ALL
SELECT ID FROM FOO WHERE col1=7 AND col2=8 AND col3=9;
Now you have a single statement returning all the matching IDs. It has to pass over the table as many times as you have tuples, but if the number is not big, that should not be a concern.
Another way is to use the OR operator to combine the conditions:
SELECT
ID
FROM
FOO
WHERE col1=1 AND col2=2 AND col3=3
OR col1=4 AND col2=5 AND col3=6
OR col1=7 AND col2=8 AND col3=9
;
You may also add brackets to make the intention absolutely unambiguous:
WHERE (col1=1 AND col2=2 AND col3=3)
OR (col1=4 AND col2=5 AND col3=6)
OR (col1=7 AND col2=8 AND col3=9)
though AND has higher precedence than OR, so the result would be the same either way.
While much simpler, the second method may not necessarily be more efficient. Internally, each member of the OR-ed expression may be implemented as a separate query, all of them being combined with UNION. So, under the covers the actual query executed could be very similar to the previous query, except UNION is not quite the same as UNION ALL, because it has the additional overhead of de-duplication of the results. If you expect your results to be unique anyway, de-duplication is certainly unnecessary.
In that case you might want to consider this method:
SELECT
f.ID
FROM
FOO AS f
INNER JOIN
(
SELECT 1 AS col1, 2 AS col2, 3 AS col3
UNION ALL
SELECT 4, 5, 6
UNION ALL
SELECT 7, 8, 9
) AS p ON f.col1 = p.col1
AND f.col2 = p.col2
AND f.col3 = p.col3
;
The p derived table returns the argument values as a row set. That row set is joined against FOO and thus serves as a filter.
Although the last query looks more verbose even than the first one, it may turn out most efficient of the three methods.
It would have looked slightly more elegant, though, if SQLite had let you use the VALUES row constructor in the FROM clause:
SELECT
f.ID
FROM
FOO AS f
INNER JOIN
(
VALUES
(1, 2, 3),
(4, 5, 6),
(7, 8, 9)
) AS p (col1, col2, col3)
ON f.col1 = p.col1
AND f.col2 = p.col2
AND f.col3 = p.col3
;
However, you can rewrite that using a CTE (Common Table Expression, also known as the WITH clause), like this:
WITH p (col1, col2, col3) AS
(
VALUES
(1, 2, 3),
(4, 5, 6),
(7, 8, 9)
)
SELECT
f.ID
FROM
FOO AS f
INNER JOIN p ON f.col1 = p.col1
AND f.col2 = p.col2
AND f.col3 = p.col3
;
But, perhaps, the most elegant solution would have been this:
SELECT
ID
FROM
FOO
WHERE
(col1,col2,col3) IN ((1,2,3), (4,5,6), (7,8,9))
;
if that syntax were supported in SQLite.
(Kudos to ypercubeᵀᴹ for the last two suggestions from the standard SQL.)
As of version 3.15, though, SQLite does support tuple comparison, as commented by a_horse_with_no_name, just not the specific syntax in the previous query. You can use the IN predicate with it only if you are matching against a subquery rather than an explicitly specified tuple list (thanks CL. for the suggestion):
SELECT
ID
FROM
FOO
WHERE
(col1,col2,col3) IN (VALUES (1,2,3), (4,5,6), (7,8,9))
;
Or you can compare the tuples using the = predicate:
SELECT
ID
FROM
FOO
WHERE
(col1,col2,col3) = (1,2,3)
OR
(col1,col2,col3) = (4,5,6)
OR
(col1,col2,col3) = (7,8,9)
;
