2

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
FlatAssembler
  • 667
  • 7
  • 30
  • 1
    @MitchWheat Err... That does not answer my question. I'd still expect 3 as a result, rather than 4. – FlatAssembler Jun 05 '22 at 07:09
  • 2
    @MitchWheat INTERSECT does **not** have higher precedence: https://www.sqlite.org/lang_select.html#compound_select_statements – forpas Jun 05 '22 at 07:13
  • 2
    @MitchWheat: "When three or more simple SELECTs are connected into a compound SELECT, they group from left to right" – forpas Jun 05 '22 at 07:22

1 Answers1

2

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

forpas
  • 160,666
  • 10
  • 38
  • 76