How can the LEFT JOIN return more results than the unjoined query?

Question

Consider the following queries:

mysql> SELECT count(*) FROM list l WHERE l.source='blink';
+----------+
| count(*) |
+----------+
|     3372 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----------+
| count(*) |
+----------+
|     5116 |
+----------+
1 row in set (2.47 sec)

There were no INSERT or other queries run on the database between these two queries running. The second query seems more restrictive than the first query, so how could it return more rows? Note that the list table has over 2 million rows, and the cardinal table has about half a million rows.

EDIT: Adding EXPLAIN output:

mysql> EXPLAIN SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref         | rows   | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | c     | ALL    | NULL              | NULL    | NULL    | NULL        | 536258 | Using where |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,id,source | PRIMARY | 8       | direct.c.id |      1 | Using where |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select count(*) from list;
+----------+
| count(*) |
+----------+
|  2165664 |
+----------+
1 row in set (1.32 sec)

mysql> select count(*) from cardinal;
+----------+
| count(*) |
+----------+
|   537007 |
+----------+
1 row in set (0.23 sec)

Thank you Phil, but the WHERE clause of l.source='blink' still applies to the left table in the second query. — dotancohen, Dec 09 '13 at 13:22
Sorry, I'm not awake yet and the answer is obvious. It's a 1<>many relationship between the two tables — Philᵀᴹ, Dec 09 '13 at 13:31
I'm brewing coffee :) I'm not seeing how adding more restrictions increases the results size. Both queries are restricted to WHERE l.source='blink'. The second query is additionally restricted to WHERE c.resolution IN ('Left','Right','Up'). I don't think that the JOIN will add records as it is a LEFT or INNER JOIN (I tried both, same result). — dotancohen, Dec 09 '13 at 13:37
Each row in the first table may match more than one row in the second table. You can check this with SELECT l.id,count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink' group by l.id having count(*)>1; — Philᵀᴹ, Dec 09 '13 at 13:43

score 6 · Accepted Answer · answered Dec 09 '13 at 13:50

Each row in the first table may match more than one row in the second table.

You can check this with:

SELECT l.id,count(*) 
FROM list l 
LEFT JOIN cardinal c ON l.id=c.id 
WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink' 
group by l.id 
having count(*)>1;

A little demo is on SQL Fiddle here.

score 3 · Answer 2 · answered Dec 09 '13 at 13:42

3

Your problem is not linked to you adding restrictions but with the JOIN.

When a row from table l has for instance 3 corresponding rows in c, three rows would be added to the result set.

answered Dec 09 '13 at 13:42

Twinkles

2,361
1
17
25

How can the LEFT JOIN return more results than the unjoined query?

2 Answers2

Linked