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.
Best Answer
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:
and combine them with UNION ALL:
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:
You may also add brackets to make the intention absolutely unambiguous:
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:
The
p
derived table returns the argument values as a row set. That row set is joined againstFOO
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:
However, you can rewrite that using a CTE (Common Table Expression, also known as the WITH clause), like this:
But, perhaps, the most elegant solution would have been this:
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):
Or you can compare the tuples using the
=
predicate: