HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
leftcantheunjoinedreturnthanmorequeryjoinhow

Problem

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)

Solution

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.

Code Snippets

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;

Context

StackExchange Database Administrators Q#54701, answer score: 6

Revisions (0)

No revisions yet.