SQLite Bracket "don't work"

sql, sqlite

Solution

Seems like SQLite doesn't like combined (sub)queries (those with `UNION`, `UNION ALL`, `EXCEPT` or `INTERSECT`) to be bracketed:

this doesn't work:

(SELECT 1 AS v
UNION
SELECT 2)
EXCEPT
SELECT 1

this doesn't work either:

SELECT 1 AS v
UNION
(SELECT 2
EXCEPT
SELECT 1)

(But both work in SQL Server.)

And without brackets, the individual subselects are combined sequentially, i.e. there's no inherent priority to any of the operators like in some other SQL products. (For instance, this

SELECT 1 AS v
UNION
SELECT 2
INTERSECT
SELECT 3

returns `1` in SQL Server (because `INTERSECT` is performed first) and nothing in SQLite.)

The only workaround seems to be to use the parts you want to combine, as subqueries, like this:

SELECT *
FROM (
  SELECT * FROM A
  UNION
  SELECT * FROM B
)
EXCEPT
SELECT *
FROM (
  SELECT * FROM A
  INTERSECT
  SELECT * FROM B
)

Problem

In SQLite version 3.7.12.1 (console) this query does not work: ``` (SELECT * FROM A UNION SELECT * FROM B) EXCEPT (SELECT * FROM A INTERSECT SELECT * FROM B); ``` Error message Error: near line 1: near "(": syntax error This query works in SQL Server Management Studio. Other queries with brackets do work as expected. Am I missing something? Edit: to clarify: ``` SELECT * FROM A; <-- works (SELECT * FROM A); <-- does not work [Error: near line 1: near "(": syntax error] SELECT * FROM A WHERE A.id IN (SELECT B.id FROM B); <-- works, so no fundamental issues with brackets and sqlite... ```

Original source