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

user_8902065 0
Viewed 145

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.

intersect subquery union sqlite sql
1 Answers

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

user_104988280
Related