Sometimes, the easy solution is not the best one. I saw this problem happening several times. The query returns duplicates, and the first reaction of the developer is to fix it with DISTINCT.
Let's look at an example. Given the data below:
select * from people;
+-----------+-------+
| person_id | name |
+-----------+-------+
| 1 | Joe |
| 2 | Mary |
| 3 | Frank |
+-----------+-------+
3 rows in set (0.00 sec)
select * from projects;
+------------+-------------+-----------+
| project_id | descr | person_id |
+------------+-------------+-----------+
| 1 | Joe First | 1 |
| 2 | Joe second | 1 |
| 3 | Mary First | 2 |
| 4 | Mary second | 2 |
| 5 | Frank first | 3 |
+------------+-------------+-----------+
5 rows in set (0.00 sec)
select * from jobs;
+--------+-----------+-----------+------------+
| job_id | job_descr | person_id | project_id |
+--------+-----------+-----------+------------+
| 1 | joe aaa | 1 | 1 |
| 2 | joe bbb | 1 | 1 |
| 3 | joe ccc | 1 | 2 |
| 4 | Mary aaa | 2 | 3 |
| 5 | Mary bbb | 2 | 3 |
| 6 | Mary ccc | 2 | 3 |
| 7 | Mary ddd | 2 | 4 |
| 8 | Frank aaa | 3 | 5 |
| 9 | Frank bbb | 3 | 5 |
+--------+-----------+-----------+------------+
9 rows in set (0.01 sec)
The problem comes with this query:
SELECT p.name, COUNT(j.job_id) AS total , job_descr
FROM people p
INNER JOIN jobs j ON p.person_id = j.person_id
INNER JOIN projects pr ON pr.person_id = j.person_id
GROUP BY p.person_id ORDER BY total DESC,p.name;
+-------+-------+-----------+
| name | total | job_descr |
+-------+-------+-----------+
| Mary | 8 | Mary aaa |
| Joe | 6 | joe aaa |
| Frank | 2 | Frank aaa |
+-------+-------+-----------+
As you can easily see, the query reports twice the amount of jobs for Mary and Joe. The lazy solution is this
SELECT p.name, COUNT(DISTINCT j.job_id) AS total , job_descr
FROM people p
INNER JOIN jobs j ON p.person_id = j.person_id
INNER JOIN projects pr ON pr.person_id = j.person_id
GROUP BY p.person_id ORDER BY total DESC,p.name;
+-------+-------+-----------+
| name | total | job_descr |
+-------+-------+-----------+
| Mary | 4 | Mary aaa |
| Joe | 3 | joe aaa |
| Frank | 2 | Frank aaa |
+-------+-------+-----------+
However, this query does not tackle the real problem, which is that the query is joining two tables (projects and jobs) using a non-primary key column. And this "solution" also ignores the even more serious problem that the
person_id is redundant, and should not be in the jobs table in the first place. Joining with a pair of primary/foreign key is the right cure:
SELECT p.name, COUNT(j.job_id) AS total, job_descr
FROM people p
INNER JOIN jobs j ON p.person_id = j.person_id
INNER JOIN projects pr ON pr.project_id = j.project_id
GROUP BY p.person_id ORDER BY total DESC,p.name ;
+-------+-------+-----------+
| name | total | job_descr |
+-------+-------+-----------+
| Mary | 4 | Mary aaa |
| Joe | 3 | joe aaa |
| Frank | 2 | Frank aaa |
+-------+-------+-----------+
The result is the same, but if you apply these queries on a couple of heavily populated tables, the first lazy query can be 5 times slower than the second one. The reason is simple: since the join was done on a non primary key column, the query performs a Cartesian product of projects and jobs, followed by a costly sort to remove the duplicates. The second query, instead, filters off the duplicates efficiently on the first step, thus delivering the wanted result faster.
