A Beginner’s Guide to the True Order of SQL Operations – Java, SQL and jOOQ.

Skip to content

https://blog.jooq.org/wp-content/uploads/2019/05/cropped-jooq-logo-white-transparent-750&#215;750-padded-2.png<br>" data-image-caption="" data-large-file="https://i0.wp.com/blog.jooq.org/wp-content/uploads/2019/05/cropped-jooq-logo-white-transparent-750x750-padded-2.png?fit=700%2C700&ssl=1" /><br>Java, SQL and jOOQ.

Best Practices and Lessons Learned from Writing Awesome Java and SQL Code. Get some hands-on insight on what's behind developing jOOQ.

The SQL language is very intuitive. Until it isn’t.

Over the years, a lot of people have criticised the SQL language for a variety of reasons. For instance: IDEs cannot easily guess what auto completion options to offer, because as long as you don’t specify the FROM clause, there are no tables in scope (yet):

-- Don't you wish this would be completed to first_name?<br>SELECT first_na...

-- Aaah, now it works:<br>SELECT first_na...<br>FROM customer

These things are weird, because the lexical order of operations does not match the logical order of operations. We humans may sometimes (often) intuitively understand this ordering difference. E.g. we know that we’re about to select from the customer table. But the IDE doesn’t know this.

GROUP BY contributes the most confusion

When a junior developer / SQL beginner starts working with SQL, quite quickly, they will find out about aggregation and GROUP BY. And they’ll quickly write things like:

SELECT count(*)<br>FROM customer

Yay, we have 200 customers!

And then:

SELECT count(*)<br>FROM customer<br>WHERE first_name = 'Steve'

Wow, 90 of them are called Steve! Interesting. Let’s find out how many we have per name…

SELECT first_name, count(*)<br>FROM customer<br>GROUP BY first_name

Ahaa!

FIRST_NAME COUNT<br>Steve 90<br>Jane 80<br>Joe 20<br>Janet 10

Very nice. But are they all the same? Let’s check out the last name, too

SELECT first_name, last_name, count(*)<br>FROM customer<br>GROUP BY first_name

Oops!

ORA-00979: not a GROUP BY expression

Jeez, what does it mean? (note, unfortunately, MySQL users that do not use the STRICT mode will still get a result here with arbitrary last names!, so a new MySQL user won’t understand their mistake)

How do you easily explain this to a SQL newbie? It seems obvious to "pros", but is it really obvious? Is it obvious enough that you can explain it easily to a junior? Think about it. Why are each one of these statements semantically correct or wrong?

-- Wrong<br>SELECT first_name, count(*)<br>FROM customer<br>WHERE count(*) &gt; 1<br>GROUP BY first_name

-- Correct<br>SELECT first_name, count(*)<br>FROM customer<br>GROUP BY first_name<br>HAVING count(*) &gt; 1

-- Correct<br>SELECT first_name, count(*)<br>FROM customer<br>GROUP BY first_name<br>ORDER BY count(*) DESC

-- Wrong<br>SELECT first_name, last_name, count(*)<br>FROM customer<br>GROUP BY first_name

-- Correct<br>SELECT first_name, MAX(last_name), count(*)<br>FROM customer<br>GROUP BY first_name

-- Wrong<br>SELECT first_name || ' ' || last_name, count(*)<br>FROM customer<br>GROUP BY first_name

-- Correct<br>SELECT first_name || ' ' || MAX(last_name), count(*)<br>FROM customer<br>GROUP BY first_name

-- Correct<br>SELECT MAX(first_name || ' ' || last_name), count(*)<br>FROM customer<br>GROUP BY first_name

The problem is syntax related

The SQL syntax works in a similar way like the English language. It is a command. We start commands with verbs. The verb is SELECT (or INSERT, UPDATE, DELETE, CREATE, DROP, etc. etc.)

Unfortunately, human language is incredibly ill-suited for the much more formal world of programming. While it offers some consolation to new users (possibly non-programmers) who are absolute beginners, it just makes stuff hard for everyone else. All the different SQL clauses have extremely complex interdependencies. For instance:

In the presence of a GROUP BY clause, only expressions built from GROUP BY expressions (or functional dependencies thereof), or aggregate functions can be used in HAVING, SELECT, and ORDER BY clauses.

For simplicity reasons, let’s not even talk about GROUPING SETS

In fact, there are even a few cases in which GROUP BY is implied. E.g. if you write a "naked" HAVING clause

A single aggregate function in the SELECT clause (in the absence of GROUP BY) will force aggregation into a single row

In fact, this can also be implied by putting that aggregate function in ORDER BY (for whatever reason)

You can ORDER BY quite a few expressions that reference any columns from the FROM clause without SELECTing them. But that’s no longer true if you write SELECT DISTINCT

The list is endless. If you’re interested, you can read the SQL standard documents and check out how many weird and complicated inter-dependencies there exist between the many clauses of the SELECT statement.

Can this ever be understood?

Luckily, yes! There’s a simple trick, which I’m always explaining to the delegates that visit my SQL Masterclass. The lexical (syntactical) order of SQL operations (clauses) does not correspond at all to the...