SQL GROUP BY and Functional Dependencies: a Very Useful Feature
In this article, I'll explain the use-case for SQL GROUP BY and functional dependencies. Read on to learn more.
Join the DZone community and get the full member experience.Join For Free
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
LAST_NAME each have a functional dependency on the
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)
LAST_NAME value. The other way around, this isn’t true. For any given
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
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
- Column expressions that are functionally dependent on the set of column expressions in the
- 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
Published at DZone with permission of Lukas Eder. See the original article here.
Opinions expressed by DZone contributors are their own.