Search code examples
sqlsqlitesubqueryunionintersect

Why is, in SQLite, `SELECT (SELECT 1 UNION SELECT 2 UNION SELECT 3) INTERSECT SELECT 3 UNION SELECT 4` equal 4 and not 3?

In SQLite, if I type:

SELECT (SELECT 1 UNION SELECT 2 UNION SELECT 3) INTERSECT SELECT 3 UNION SELECT 4

I get the result 4. How is that possible?

SELECT 1 UNION SELECT 2 SELECT 3 is (1, 2, 3), right? And SELECT 3 UNION SELECT 4 is (3, 4). So, the intersect should be 3, right? What am I getting wrong?

EDIT: Saying that INTERSECT is evaluated first does not answer my question, as ((1,2,3) INTERSECT (3)) UNION (4) is (3,4), rather than 4.

Solution

  • If you write your statement like this:

    SELECT (SELECT 1 UNION SELECT 2 UNION SELECT 3) 
INTERSECT 
SELECT 3 
UNION 
SELECT 4

    you can see that you are combining 3 SELECT statements with the operators UNION and INTERSECT.
    All 3 statements should return the same number of columns.
    Your 1st statement:

    SELECT (SELECT 1 UNION SELECT 2 UNION SELECT 3)

    actually returns only 1 row with 1 column, try it, which is the 1st row and the result is 1.

    So your code is equivalent to:

    SELECT 1 
INTERSECT 
SELECT 3 
UNION 
SELECT 4

    which returns nothing for INTERSECT and finally UNION returns 4.

    If you meant to write:

    SELECT * FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3) 
INTERSECT 
SELECT 3 
UNION 
SELECT 4

    then the result would be (3, 4).