Curtis J. Malainey Curtis J. Malainey - 1 month ago 12
SQL Question

sqlite IN check set coverage

Can SQLite check set coverage using the

IN
operator?

ie
(SELECT n from nums where n < 4) IN (1,2,3,4)

(where nums is the set of whole numbers) would return true

I have searched the documents but can only find documents where they are only using a single value on the left of
IN
.
Tests have returned that it can, but I need to confirm this is a valid use case and not a side effect like the ability for SQLite to return aggregate queries without proper
GROUP BY
statements.

CL. CL.
Answer

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);