It's a bug:
<http://bugs.mysql.com/1677> <http://bugs.mysql.com/1591> <http://bugs.mysql.com/3765>
Depending on which bug report you look at, this result is either because mysql treats this as a nested join, or because mysql does not support nested joins. Frankly, I find the explanations make no sense.
Meanwhile, I believe changing your RIGHT JOIN to a LEFT JOIN will yield the results you were expecting:
SELECT * FROM B LEFT JOIN E ON e = b LEFT JOIN C ON e = c; +------+------+------+ | b | e | c | +------+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | NULL | NULL | +------+------+------+ 4 rows in set (0.13 sec)
Michael
Ian Sales wrote:
During some training on SQL recently, the following occurred. No one can explain it.
The training used five tables A, B, C , D and E, each with a single int column.
Table A has column a with values 1, 2, 3, 4, 5 Table B has column b with values 1, 2, 3, 4 Table C has column c with values 1, 2, 3, 4, 5, 6 Table D has column d with values 1, 2, 3, 4 Table E has column e with values 1, 2, 3
When running the following SQL...
select * from E left join C on e = c right join B on e = b;
we got the following results:
+------+------+------+ | e | c | b | +------+------+------+ | 1 | 1 | 1 | | 2 | NULL | 1 | | 3 | NULL | 1 | | 1 | NULL | 2 | | 2 | 2 | 2 | | 3 | NULL | 2 | | 1 | NULL | 3 | | 2 | NULL | 3 | | 3 | 3 | 3 | | 1 | NULL | 4 | | 2 | NULL | 4 | | 3 | NULL | 4 | +------+------+------+ 12 rows in set (0.00 sec)
Common sense expects the number of rows returned to be four. So where's the Cartesian coming from?
- ian
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
