Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Lexical and logical SELECT clause order
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
SQL has a lexical and a logical order of SELECT clauses. The lexical order of SELECT clauses is inspired by the English language. As SQL statements are commands for the database, it is natural to express a statement in an imperative tense, such as "SELECT this and that!".
Logical SELECT clause order
The logical order of SELECT clauses, however, does not correspond to the syntax. In fact, the logical order is this:
- The FROM clause: First, all data sources are defined and joined
- The WHERE clause: Then, data is filtered as early as possible
- The CONNECT BY clause: Then, data is traversed iteratively or recursively, to produce new tuples
- The GROUP BY clause: Then, data is reduced to groups, possibly producing new tuples if grouping functions like ROLLUP(), CUBE(), GROUPING SETS() are used
- The HAVING clause: Then, data is filtered again, based on aggregate functions
- The WINDOW clause: Then, window specifications and window functions are evaluated
- The QUALIFY clause: Then, data is filtered again, based on window functions
- The SELECT clause: Only now, the projection is evaluated.
- The DISTINCT clause: Duplicate projected rows are removed
-
UNION, INTERSECT and EXCEPT clauses: Optionally, the above is repeated for several
UNION-connected subqueries. Unless this is aUNION ALLclause, data is further reduced to remove duplicates - The ORDER BY clause: Now, all remaining tuples are ordered
- The LIMIT clause: Then, a paginating view is created for the ordered tuples
- The FOR clause: Transformation to XML or JSON
- The FOR UPDATE clause: Finally, pessimistic locking is applied
The SQL Server documentation also explains this, with slightly different clauses:
-
FROM -
ON -
JOIN -
WHERE -
GROUP BY -
WITH CUBEorWITH ROLLUP -
HAVING -
SELECT -
DISTINCT -
ORDER BY -
TOP
As can be seen, databases have to logically reorder a SQL statement in order to determine the best execution plan.
Alternative syntaxes: LINQ, SLICK
Some "higher-level" abstractions, such as C#'s LINQ or Scala's Slick try to inverse the lexical order of SELECT clauses to what appears to be closer to the logical order. The obvious advantage of moving the SELECT clause to the end is the fact that the projection type, which is the record type returned by the SELECT statement can be re-used more easily in the target environment of the internal domain specific language.
A LINQ example:
// LINQ-to-SQL looks somewhat similar to SQL // AS clause // FROM clause From p In db.Products // WHERE clause Where p.UnitsInStock <= p.ReorderLevel AndAlso Not p.Discontinued // SELECT clause Select p
A Slick example:
// "for" is the "entry-point" to the DSL
val q = for {
// FROM clause WHERE clause
c <- Coffees if c.supID === 101
// SELECT clause and projection to a tuple
} yield (c.name, c.price)
While this looks like a good and idiomatic idea at first, jOOQ's take here is that this only complicates translation to more advanced SQL statements while impairing readability for those users that are used to writing SQL. This is specifically true for Slick, which not only changed the SELECT clause order, but also heavily "integrated" SQL clauses with the Scala language.
jOOQ is designed to look just like SQL. For these reasons, the jOOQ DSL API is modelled in SQL's lexical order.
Feedback
Do you have any feedback about this page? We'd love to hear it!