SQL GROUP BY and Functional Dependencies: A Very Useful Feature

Posted on by lukaseder
Relational databases define the term “Functional Dependency” as such (from Wikipedia):
In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.
In SQL, functional dependencies appear whenever there is a unique constraint (e.g. a primary key constraint). Let’s assume the following: 

CREATE TABLE actor (
  actor_id BIGINT NOT NULL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL
);
It can be said that both FIRST_NAME and LAST_NAME each have a functional dependency on the ACTOR_ID column.

Nice. So what?

This isn’t just some mathematical statement that can be applied to unique constraints. It’s extremely useful for SQL. It means that for every ACTOR_ID value, there can be only one (functionally dependent) FIRST_NAME and LAST_NAME value. The other way round, this isn’t true. For any given FIRST_NAME and/or LAST_NAME value, we can have multiple ACTOR_ID values, as we can have multiple actors by the same names. Because there can be only one corresponding FIRST_NAME and LAST_NAME value for any given ACTOR_ID value, we can omit those columns in the GROUP BY clause. Let’s assume also: 

CREATE TABLE film_actor (
  actor_id BIGINT NOT NULL,
  film_id BIGINT NOT NULL,
  
  PRIMARY KEY (actor_id, film_id),
  FOREIGN KEY (actor_id) REFERENCS actor (actor_id),
  FOREIGN KEY (film_id) REFERENCS film (film_id)
);
Now, if we want to count the number of films per actor, we can write: 

SELECT
  actor_id, first_name, last_name, COUNT(*)
FROM actor
JOIN film_actor USING (actor_id)
GROUP BY actor_id
ORDER BY COUNT(*) DESC
This is extremely useful as it saves us from a lot of typing. In fact, the way GROUP BY semantics is defined, we can put all sorts of column references in the SELECT clause, which are any of:
  • Column expressions that appear in the GROUP BY clause
  • Column expressions that are functionally dependent on the set of column expressions in the GROUP BY clause
  • Aggregate functions

Unfortunately, not everyone supports this

If you’re using Oracle, for instance, you can’t make use of the above. You’ll need to write the classic, equivalent version where all the non-aggregate column expressions appearing in the SELECT clause must also appear in the GROUP BY clause 

SELECT
  actor_id, first_name, last_name, COUNT(*)
FROM actor
JOIN film_actor USING (actor_id)
GROUP BY actor_id, first_name, last_name
--                 ^^^^^^^^^^  ^^^^^^^^^ unnecessary
ORDER BY COUNT(*) DESC
Further reading:

Published by lukaseder

I made jOOQ

6 thoughts on “SQL GROUP BY and Functional Dependencies: A Very Useful Feature”

  1. Really helped me! Until today I used to think of “functional dependency” a term that was best left to college textbooks. But as a developer trying to improve his skills, I appreciate this concept now, thanks to your explanation.

    Reply

    1. Glad it helped. It’s a really useful feature, but also a dangerous one. If the foreign key constraint is deferred, or even disabled (e.g. for a migration), then the query might break.

      Reply

      1. Hey, thanks for the reply! Do you mind explaining why this might break the query? Is it because the absence of foreign key constraint will make MySQL fail at determining functional dependencies and it will refuse to run the queries? Or something else (like loss of data integrity) is also possible? An example will be really great. :-)

        Reply

        1. Classic MySQL (without strict mode) simply doesn’t care about GROUP BY correctness. If there’s no guarantee about functional dependencies, you’ll simply get random values from the group if you project (e.g. SELECT) illegal expressions.

          I was referring to PostgreSQL, which allows using functional dependencies in GROUP BY only in the presence of a primary key (not foreign key, my bad). Once the key is turned off, the parser will reject the query. Check out this SQLFiddle:
          http://sqlfiddle.com/#!17/8cb32/1

          Reply

          1. Makes sense. I’m on the newer, strict-version MySQL, so don’t need to worry a lot, I guess. Is it a common practice to disable constraints during migrations? I’ve never migrated databases but it doesn’t seem necessary to me. Why not simply populate the tables in the order they were created and all will be fine?

            Reply

Leave a Reply