Sqlite IN clause 小常識

Ensky Lin
Ensky Lin
Apr 21, 2019 · 2 min read

最近遇到一個神秘的問題,症狀是我的 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 中的第一個值。

Image for post
Image for post
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 的括號不是想加就加,有時候結果會和你預期的完全不同。

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store