Purposeful Dependencies in SQL GROUP BY – Java, SQL and jOOQ.

The SQL normal is aware of an attention-grabbing function the place you’ll be able to mission any purposeful dependencies of a major (or distinctive) key that’s listed within the GROUP BY clause with out having so as to add that purposeful dependency to the GROUP BY clause explicitly.

What does this imply? Take into account this easy schema:

CREATE TABLE writer (
  id INT NOT NULL PRIMARY KEY,
  identify TEXT NOT NULL
);

CREATE TABLE guide (
  id INT NOT NULL PRIMARY KEY,
  author_id INT NOT NULL REFERENCES writer,
  title TEXT NOT NULL
);

With the intention to depend the variety of books by writer, we have a tendency to put in writing:

SELECT a.identify, depend(b.id)
FROM writer a
LEFT JOIN guide b ON a.id = b.author_id
GROUP BY 
  a.id,  -- Required, as a result of names aren't distinctive
  a.identify -- Required in some dialects, however not in others

We’ve got to group by one thing distinctive on this case, as a result of if two authors are known as John Doe, we nonetheless need them to supply separate teams. So GROUP BY a.id is a given.

We’re used to additionally GROUP BY a.identify, particularly in these dialects that require this, since we listing a.identify within the SELECT clause:

• Db2
• Derby
• Exasol
• Firebird
• HANA
• Informix
• Oracle
• SQL Server

However is it actually required? It isn’t as per the SQL normal, as a result of there’s a purposeful dependency between writer.id and writer.identify. In different phrases, for every worth of writer.id, there’s precisely one potential worth of writer.identify, or writer.identify is a operate of writer.id

Which means that it doesn’t matter if we GROUP BY each columns, or solely the first key. The end result should be the identical in each circumstances, therefore that is potential:

SELECT a.identify, depend(b.id)
FROM writer a
LEFT JOIN guide b ON a.id = b.author_id
GROUP BY a.id

Which SQL dialects help this?

Not less than the next SQL dialects help this language function:

• CockroachDB
• H2
• HSQLDB
• MariaDB
• MySQL
• PostgreSQL
• SQLite
• Yugabyte

It’s noteworthy that MySQL used to easily ignore whether or not a column might be projected unambiguously or not, within the presence of GROUP BY. Whereas the next question was rejected in most dialects, it was not, in MySQL, previous to the introduction of the ONLY_FULL_GROUP_BY mode:

SELECT author_id, title, depend(*)
FROM writer
GROUP BY author_id

What ought to we show for writer.title, if an writer has written a couple of guide? It doesn’t make sense, but MySQL nonetheless used to permit it, and would simply mission any arbitrary worth from the group.

In the present day, MySQL solely permits for projecting columns with a purposeful dependency on the GROUP BY clause, as is permitted by the SQL normal.

Execs & Cons

Whereas the shorter syntax that avoids the additional columns could be simpler to take care of (simple to mission extra columns, if required), there’s some threat of queries breaking in manufacturing, particularly when underlying constraints are disabled, e.g. for a migration. Whereas it’s unlikely {that a} major secret is disabled in a reside system, it might nonetheless be the case, and with out the important thing, a beforehand legitimate question will not be legitimate for a similar cause why MySQL’s previous interpretation was invalid: There’s not a assure of purposeful dependency.

Different syntax

Ranging from jOOQ 3.16, and #11834, it is going to be potential to reference tables instantly within the GROUP BY clause, as an alternative of particular person columns. For instance:

SELECT a.identify, depend(b.id)
FROM writer a
LEFT JOIN guide b ON a.id = b.author_id
GROUP BY a

The semantics will likely be:

• If the desk has a major key (composite or not), use that within the GROUP BY clause, as an alternative
• If the desk doesn’t have a major key, listing all of the columns from the desk as an alternative.

Since not one of the RDBMS supported by jOOQ at the moment helps this syntax, it’s a purely artificial jOOQ feature.