Sqlite IN clause 小常識

最近遇到一個神秘的問題,症狀是我的 Query 看起來非常正常,但就是會少 Query 出一些東西。

Query 大概長這樣:

SELECT * FROM some_table         --- A
WHERE some_column IN ((
SELECT * FROM another_table --- B
));

因為 B Query 在我的 case 裡面相當複雜(有用到 WITH RECURSIVE 的巢狀 Query),因此我直覺一直認為 B Query 出了問題,但丟進去 Query 都沒任何異狀,結果相當正常,但 A Query 的結果一直都不太對勁。

找了一段時間之後才發現問題在 IN 後面的兩層括號 (( SubQuery ))

原來 IN 後面的語法本來就可接多個 expr 如 IN (expr, expr, …),如果你又多了一層括號,那麼就會當作你正在用這個語法,因此它只會 evaluate expr 中的第一個值。

expr 中的 IN 的部份

這可以從以下 sample code 中得到證實:

sqlite> CREATE TABLE temp (
...> id integer
...> );
sqlite> INSERT INTO temp VALUES(1),(2),(3);
sqlite> SELECT * FROM temp WHERE id IN (1,2,3);
id = 1
id = 2
id = 3
sqlite> SELECT * FROM temp WHERE id IN (SELECT id FROM temp);
id = 1
id = 2
id = 3
sqlite> SELECT * FROM temp WHERE id IN ((SELECT id FROM temp));
id = 1
sqlite> SELECT * FROM temp WHERE id IN ((SELECT id FROM temp), (SELECT 2 FROM temp));
id = 1
id = 2

因為在 C++ 或大多數的程式語言裡面 expression 的括號有加和沒加結果都一樣,因此這次使用上疏忽了這一塊,SQL 的括號不是想加就加,有時候結果會和你預期的完全不同。