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 FreeRelational 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 around, 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 at DZone with permission of Lukas Eder. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments