5

I can match one set of conditions like so:

select ID from FOO where col1=1 and col2=2 and col3=3

But, what if I have an array, [(1,2,3), (4,5,6), (7,8,9)], how do I get the ID's in this case?

To clarify, I would like a way to match each of the tuples to the three columns. If it was broken out it would be:

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

If I have to loop, so be it, I thought there might be a more elegant way to do this.

My version of sqlite is 3.8.11, which ships with Python 3.5.1.

10

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)
;
| improve this answer | |
  • This is a nice answer, I was also looking how could I do a prepared statement with row values. With my naive testing, it seems like it's not possible to do a prepared statement with row values ( gist.github.com/Ciantic/8e6e1e077e67d51266d6495af34db6e5 ) – Ciantic May 9 '19 at 8:06
  • Yes, parametrising a dynamic IN list has always been tricky, not only in SQLite, and it doesn't have to be a tuple list either, a scalar value list would be tricky to parametrise as well. If you like, you could submit a question about this problem. I'm not much of a SQLite expert myself, can't suggest anything specific in this case (other than just building the list literally, which generally isn't considered a safe approach). But others might have good ideas. The only thing I'm unsure about is whether it's better to ask here or on SO, seeing that you are building the query at the client side. – Andriy M May 9 '19 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.