Can SQLite check set coverage using the
(SELECT n from nums where n < 4) IN (1,2,3,4)
The documentation says:
The IN and NOT IN operators take a single scalar operand on the left
So if you use a subquery on the left side, it is treated as a scalar subquery, which does not behave as you want it to:
The result of the expression is the value of the only column in the first row returned by the SELECT statement. If the SELECT yields more than one result row, all rows after the first are ignored.
To check for set coverage, you have to check if there is any element in the left set that is not in the right set:
WITH a(n) AS ( SELECT n FROM nums WHERE n < 4 ), b(n) AS ( VALUES (1), (2), (3), (4) ) SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.n = a.n);