Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Emulate nested set operators in databases that don't support them natively #3579

Closed
lukaseder opened this issue Aug 22, 2014 · 0 comments
Closed

Emulate nested set operators in databases that don't support them natively #3579

lukaseder opened this issue Aug 22, 2014 · 0 comments
Labels
C: DB: Access C: DB: Derby C: DB: Firebird C: DB: MariaDB C: DB: MySQL C: DB: SQLite C: DB: Sybase ASE C: Functionality P: Medium R: Fixed T: Enhancement
Milestone
Version 3.5.0

Comments

@lukaseder
Copy link
Member

In standard SQL, set operators can be nested as such:

SELECT 1
UNION (
   SELECT 2
   INTERSECT (
      SELECT 2
      UNION ALL
      SELECT 3
   )
)

This is essentially specified by the SQL standard as such:

<query expression body> ::=
    <query term>
  | <query expression body> UNION [ ALL | DISTINCT ]
      [ <corresponding spec> ] <query term>
  | <query expression body> EXCEPT [ ALL | DISTINCT ]
      [ <corresponding spec> ] <query term>

<query term> ::=
    <query primary>
  | <query term> INTERSECT [ ALL | DISTINCT ]
      [ <corresponding spec> ] <query primary>

<query primary> ::=
    <simple table>
  | <left paren> <query expression body>
      [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
      <right paren>

Where a <query primary> can be simply another <query expression> wrapped in parentheses, and in fact ORDER BY, OFFSET, and FETCH are allowed too, this way.

Some databases, however, do not support parentheses around subselects at all, in the context of set operations. These are:

  • Derby
  • Firebird
  • MariaDB
  • MS Access
  • MySQL
  • SQLite
  • Sybase ASE

Support can be emulated via derived tables, which are pretty well supported across vendors:

SELECT 1
UNION 
SELECT * FROM (
   SELECT 2
   INTERSECT 
   SELECT * FROM (
      SELECT 2
      UNION ALL
      SELECT 3
   )
)
@lukaseder lukaseder added this to the Version 3.5.0 milestone Aug 22, 2014
@lukaseder lukaseder mentioned this issue Aug 22, 2014
Open
@lukaseder lukaseder mentioned this issue Oct 7, 2014
Closed
lukaseder added a commit that referenced this issue Oct 10, 2014
@lukaseder
[#3579] [#3664] Emulate nested set operators in databases that don't …
568f012 
…support them natively
@lukaseder lukaseder mentioned this issue Oct 10, 2014
Closed
lukaseder added a commit that referenced this issue Oct 15, 2014
@lukaseder
[#3579] More meaningful API
d77e776
lukaseder added a commit that referenced this issue Oct 15, 2014
@lukaseder
[#3579] Fixed UNION implementation for databases that support LIMIT /…
cf00bd9 
… OFFSET
@lukaseder lukaseder mentioned this issue Jan 23, 2017
Closed
This was referenced Feb 26, 2018
Closed
Closed
lukaseder added a commit that referenced this issue Mar 20, 2018
@lukaseder
[#3579] [#6431] [#7222] Fix nested set operations for SQLite
a496614
sebasjm pushed a commit to sebasjm/jOOQ that referenced this issue Mar 27, 2018
@lukaseder
[jOOQ#3579] [jOOQ#6431] [jOOQ#7222] Fix nested set operations for SQLite
510bd2d
@lukaseder lukaseder mentioned this issue Mar 5, 2021
Closed
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C: DB: Access C: DB: Derby C: DB: Firebird C: DB: MariaDB C: DB: MySQL C: DB: SQLite C: DB: Sybase ASE C: Functionality P: Medium R: Fixed T: Enhancement
Projects
None yet
Milestone
Version 3.5.0
Development

No branches or pull requests
1 participant
@lukaseder