Geek Answers Handbook

Sqlite query with intersection and exception

here is the sqlite query that I have to execute, but I want to execute, except for the operation first, and then the intersection. By default, an intersection gets more priority except. Is there a way I can do this?

select a, b from table1 intersect select a,b from table2 except select a, b from table3

I tried to put the queries in brackets, but this caused the error " [ near "(": syntax error ] ".

 select a, b from table1 intersect (select a,b from table2 except select a, b from table3)
+8
sqlite sqlite3
source share
1 answer

Compound queries do not support parentheses, but subqueries:

 SELECT a, b FROM table1 INTERSECT SELECT * FROM (SELECT a, b FROM table2 EXCEPT SELECT a, b FROM table3)

Please note that INTERSECT does not have a higher priority than EXCEPT , the actual rules are :

When three or more simple SELECTs are combined into a SELECT, they are grouped from left to right.

Since INTERSECT is commutative, you can simply write this specific query as:

 SELECT a, b FROM table2 EXCEPT SELECT a, b FROM table3 INTERSECT SELECT a, b FROM table1
+15
source share

More articles:


All Articles