snippetMinor
How can the LEFT JOIN return more results than the unjoined query?
Viewed 0 times
leftcantheunjoinedreturnthanmorequeryjoinhow
Problem
Consider the following queries:
There were no
EDIT: Adding
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:
A little demo is on SQL Fiddle here.
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.