Changing SELECT .. FROM Into FROM .. SELECT Does Not “Fix” SQL

Every now and then, I see folks lament the SQL syntax’s peculiar disconnect between the lexical order of operations (SELECT .. FROM) the logical order of operations (FROM .. SELECT) Most recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let’s look at why jOOQ didn’t fall into this trap of trying … Continue reading Changing SELECT .. FROM Into FROM .. SELECT Does Not “Fix” SQL →

Changing SELECT .. FROM Into FROM .. SELECT Does Not “Fix” SQL

Posted on May 31, 2022 by lukaseder

Every now and then, I see folks lament the SQL syntax’s peculiar disconnect between

• the lexical order of operations (SELECT .. FROM)
• the logical order of operations (FROM .. SELECT)

Most recently here in a Youtube comment reply to a recent jOOQ/kotlin talk. Let’s look at why jOOQ didn’t fall into this trap of trying to “fix” this, and why this is even a trap.

The English Language

SQL has a simple syntax model. All commands start with a verb in imperative form, as we “command” the database to execute a statement. Common commands include:

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• TRUNCATE
• CREATE
• ALTER
• DROP

All of these are verbs in imperative form. Think about adding an exclamation mark everywhere, e.g. INSERT [this record]!

The Order of Operations

We can argue that natural languages are very poor inspiration for computer programming languages, which tend to be more mathematical (some more than others). A lot of criticism about the SQL language is that it does not “compose” (in its native form).

We can argue, that it would be much better for a more composable SQL language to start with FROM, which is the first operation in SELECT according to the logical order of operations. E.g.

FROM book
WHERE book.title LIKE 'A%'
SELECT book.id, book.title

Yes, that would be better in the sense that it would be more logical. First, we declare the data source, predicates, etc. and only in the end would we declare the projection. With the Java Stream API, we would write:

books.stream()
.filter(book -> book.title.startsWith("A"))
.map(book -> new B(book.id, book.title))

The benefits of this would be:

• No disconnect between syntax and logic
• Hence: No confusion around syntax, specifially why you can’t reference SELECT aliases in WHERE, for example.
• Better auto-completion (because you don’t write stuff that isn’t declared yet, first)

In a way, this ordering would be consistent with what some RDBMS implemented when RETURNING data from DML statements, such as:

• Firebird
• Oracle
• PostgreSQL

INSERT INTO book (id, title)
VALUES (3, 'The Book')
RETURNING id, created_at

With DML statements, the command (“imperative”) is still INSERT, UPDATE, DELETE, i.e. a verb that clearly tells the database what to do with the data. The “projection” is more of an afterthought. A utility that is occasionally useful, hence RETURNING can be placed at the end.

RETURNING seems like a pragmatic choice of syntax, and isn’t even part of the standard. The standard defines the <data change delta table>, as implemented by Db2 and H2, whose syntax is:

SELECT id, created_at
FROM FINAL TABLE (
INSERT INTO book (id, title)
VALUES (3, 'The Book')
) AS book

I mean, why not. I don’t have a strong preference for one or the other syntax (jOOQ supports both and emulates them into one another). SQL Server invented a third variant, whose syntax is probably the least intuitive (I always have to look up the exact location of the OUTPUT clause):

INSERT INTO book (id, title)
OUTPUT id, created_at
VALUES (3, 'The Book')

Cypher query language

Probably worth mentioning here is that there exists a modern query language out there that is sufficiently popular to be considered for such discussions: The Cypher Query Language from neo4j. With a simple “trick”, it both:

• Maintained the language model where a verb in imperative form starts a statement (the verb is MATCH, which is similar to FROM, but it is a verb), so it inherits SQL’s “strength” of being intuitive also for non-programmers.
• Reversed the logical order of operations within the reading statements, to be of the form MATCH .. RETURN, making RETURN the universal form of projecting things for all operations, not just SELECT.
• Reused MATCH also for writing operations, including DELETE or SET (which corresponds to SQL’s UPDATE)

While operating on a different data paradigm (the network model as opposed to the relational model), I’ve always found the Cypher Query Language to be generally superior to SQL in terms of syntax, at least on a high level. If I had to actually “fix” SQL by creating SQL 2.0, I’d take inspiration here.

Fixing this in an API like jOOQ isn’t worth it

As discussed before, SQL has some obvious shortcomings, and there exist better languages like Cypher solving the same kind of problem. But SQL is here, and it’s 50 years old, and it will stay. It won’t be fixed.

That’s something that just has to be accepted:

SQL won’t be fixed

It will be amended. It incorporates new ideas, including:

• Objects, via ORDBMS extensions (a SQL standard, the most important features are ARRAY types and the mighty MULTISET, which jOOQ emulates)
• XML via SQL/XML
• JSON via SQL/JSON

It always does so in an idiomatic, SQL style way. If you’re reading the SQL standard, or if you’re working with PostgreSQL, which is very close to the standard, you’ll feel that SQL is quite consistent as a language. Or, it is consistently weird, depending on your tastes.

For jOOQ, one of the main success factors has always been to be as close as possible to this vision of what SQL really is in terms of syntax. A lot of folks are very effective writing native SQL. Since Java has text blocks, it has become a lot more bearable to just copy paste a static SQL query from your SQL editor into your Java program, and e.g. execute it with JDBC or with jOOQ’s plain SQL templating API:

for (Record record : ctx.fetch(
"""
SELECT id, title
FROM book
WHERE title LIKE 'A%'
"""
)) {
System.out.println(record);

This approach is sufficient for very simple applications out there. If your “application” runs a total of 5 distinct SQL queries, you can do it with JDBC alone (although, once you’ve started to get a hang of jOOQ, you’ll probably use jOOQ even for those applications as well).

But jOOQ really shines when your application has 100s of queries, including many dynamic ones, and your database has 100s of tables, in case of which the type safety and model safety benefits really help. However, it can shine only when your SQL query translates 1:1 to the jOOQ API. Randomly fixing SQL to some extent in this most important statement (SELECT) won’t do the trick.

[...]

Original source