Few days ago, I needed to write an SQL query on combined unique key. The situation is that I have a table with one unique constraint on two columns. Let's call those columns A, and B. The table had these values:
+---+---+ | A | B | +---+---+ | 1 | 1 | | 1 | 2 | | 2 | 3 | | 2 | 4 | +---+---+
At first, I tried this query below:
SELECT * FROM tbl WHERE (A, B) IN ((1, 1))
It worked beautifully. MySQL
EXPLAIN command said it only needed to look at one row.
So I ventured further to put in one more value to search for:
SELECT * FROM tbl WHERE (A, B) IN ((1, 1), (1, 2))
EXPLAIN told me it needed to scan the whole table (4 rows). I thought it was odd and tried to make a comparison with the regular query:
SELECT * FROM tbl WHERE (A=1 AND B=1) OR (A=1 AND B=2)
The third query turned out to scan only two rows!
IN operator has not been optimized in this case yet so for now I've got to do with a clumsier query. I could, of course, shorten the third query to
A=1 AND B IN (1, 2) to make it less wieldy.
On a related note, SQLite does not even support tuple comparison.