Bug #23498 | call to coalesce is not nedded in join statements | ||
---|---|---|---|
Submitted: | 20 Oct 2006 12:46 | Modified: | 25 Nov 2006 7:13 |
Reporter: | R H | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S5 (Performance) |
Version: | 5.0.26 | OS: | Windows (Windows XP) |
Assigned to: | CPU Architecture: | Any | |
Tags: | COALESCE, join, joins |
[20 Oct 2006 12:46]
R H
[25 Nov 2006 7:13]
Valeriy Kravchuk
Let's continue your example: mysql> delete from t2 where a=1; Query OK, 1 row affected (0.00 sec) mysql> select * from t1 left join t2 using(a,b); +------+------+------+------+------+------+ | a | b | c | x | c | y | +------+------+------+------+------+------+ | 1 | 1 | 1 | 1 | NULL | NULL | | 2 | 2 | 2 | NULL | 2 | 2 | | 3 | 3 | NULL | NULL | 3 | 3 | | 4 | NULL | NULL | NULL | NULL | NULL | +------+------+------+------+------+------+ 4 rows in set (0.00 sec) mysql> select * from t1 right join t2 using(a,b); +------+------+------+------+------+------+ | a | b | c | y | c | x | +------+------+------+------+------+------+ | 2 | 2 | 2 | 2 | 2 | NULL | | 3 | 3 | 3 | 3 | NULL | NULL | | 4 | 4 | 4 | 4 | NULL | NULL | +------+------+------+------+------+------+ 3 rows in set (0.01 sec) Compare with the following: mysql> select * from t1 right join t2 ON (t1.a = t2.a and t1.b = t2.b); +------+------+------+------+------+------+------+------+ | a | b | c | x | a | b | c | y | +------+------+------+------+------+------+------+------+ | 2 | 2 | 2 | NULL | 2 | 2 | 2 | 2 | | 3 | 3 | NULL | NULL | 3 | 3 | 3 | 3 | | NULL | NULL | NULL | NULL | 4 | 4 | 4 | 4 | +------+------+------+------+------+------+------+------+ 3 rows in set (0.00 sec) mysql> select * from t1 left join t2 ON (t1.a = t2.a and t1.b = t2.b); +------+------+------+------+------+------+------+------+ | a | b | c | x | a | b | c | y | +------+------+------+------+------+------+------+------+ | 1 | 1 | 1 | 1 | NULL | NULL | NULL | NULL | | 2 | 2 | 2 | NULL | 2 | 2 | 2 | 2 | | 3 | 3 | NULL | NULL | 3 | 3 | 3 | 3 | | 4 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +------+------+------+------+------+------+------+------+ 4 rows in set (0.01 sec) I hope, the (intentional) difference is explained properly in http://dev.mysql.com/doc/refman/5.0/en/join.html.