Why is, in SQLite, `SELECT (SELECT 1 UNION SELECT 2 UNION SELECT 3) INTERSECT SELECT 3 UNION SELECT-CodePudding

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.

CodePudding user response：

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