Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
    
Thank you Phil, but the WHERE clause of l.source='blink' still applies to the left table in the second query. –  dotancohen Dec 9 '13 at 13:22
    
Testing with INNER JOIN gives the same result of 5116. –  dotancohen Dec 9 '13 at 13:26
    
Sorry, I'm not awake yet and the answer is obvious. It's a 1<>many relationship between the two tables –  Phil Dec 9 '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 9 '13 at 13:37

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Thank you Phil. The SQL query example is very helpful. –  dotancohen Dec 9 '13 at 14:23

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.