hyljava

mysql 解决全连接问题

基本资料：
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16 |
+-----------+ 
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+----+------+ 
mysql> select * from t2;
+------+-------+
| code | price |
+------+-------+
| 1 | 100 |
| 3 | 900 |
| 5 | 2500 |
+------+-------+

内连，左连，右连都正确：
mysql> select * from t1 inner join t2 on t1.id = t2.code;
+----+------+------+-------+
| id | name | code | price |
+----+------+------+-------+
| 1 | aa | 1 | 100 |
| 3 | cc | 3 | 900 |
+----+------+------+-------+ 
mysql> select * from t1 left join t2 on t1.id = t2.code
+----+------+------+-------+
| id | name | code | price |
+----+------+------+-------+
| 1 | aa | 1 | 100 |
| 2 | bb | NULL | NULL |
| 3 | cc | 3 | 900 |
+----+------+------+-------+
mysql> select * from t1 right join t2 on t1.id = t2.code;
+------+------+------+-------+
| id | name | code | price |
+------+------+------+-------+
| 1 | aa | 1 | 100 |
| 3 | cc | 3 | 900 |
| NULL | NULL | 5 | 2500 |
+------+------+------+-------+ 

全连有错：
FULL JOIN 错误一：
第一个表名不能出现在on的关联关系中，例如：
mysql> select * from t1 full join t2 on t1.id = t2.code;
ERROR 1054 (42S22): Unknown column 't1.id' in 'on clause' 
语句更改为如下后运行不再报错：
mysql> select * from t1 full join t2 on id = t2.code;
或者：
mysql> select * from t1 full join t2 on id = code;

FULL JOIN 错误二：
上面语句运行后，结果如下：
+----+------+------+-------+
| id | name | code | price |
+----+------+------+-------+
| 1 | aa | 1 | 100 |
| 3 | cc | 3 | 900 |
+----+------+------+-------+
显然，这不是正确的full join结果集，而是inner join的结果集。替代方案如下：
mysql> select * from t1 left join t2 on id = code union select * from t1 right join t2 on id = code;
+------+------+------+-------+
| id | name | code | price |
+------+------+------+-------+
| 1 | aa | 1 | 100 |
| 2 | bb | NULL | NULL |
| 3 | cc | 3 | 900 |
| NULL | NULL | 5 | 2500 |
+------+------+------+-------+ 

INTERSECT错误：
mysql> select * from t1 left join t2 on id = code intersect select * from t1 right join t2 on id = code;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'intersect select * from t1 right join t2 on id = code' at line 1
说明MySQL不支持INTERSECT。替代方案如下：
mysql> select * from t1 inner join t2 on id = code;
+----+------+------+-------+
| id | name | code | price |
+----+------+------+-------+
| 1 | aa | 1 | 100 |
| 3 | cc | 3 | 900 |
+----+------+------+-------+ 

MINUS错误：
mysql> select * from t1 left join t2 on id = code minus select * from t1 right join t2 on id = code;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'minus select * from t1 right join t2 on id = code' at line 1
说明MySQL不支持MINUS。
而在MS SQL Server中运行（select * from t1 left join t2 on id = code except select * from t1 right join t2 on id = code;）可以得到期望结果如下：
+------+------+------+-------+
| id | name | code | price |
+------+------+------+-------+
| 2 | bb | NULL | NULL |
+------+------+------+-------+ 
运行如下语句：
mysql> select * from t1 left join t2 on id = code where (id,name,code,price) not in (select * from t1 right join t2 onid = code);
Empty set (0.00 sec)
或者：
mysql> select id,name,code,price from t1 left join t2 on id = code where (id,name,code,price) not in (select id,name,code,price from t1 left join t2 on id = code);
Empty set (0.00 sec)
都没有得到期望中的结果。
（是否因为有NULL值字段，无法用IN 和 NOT IN 来匹配啦？菜鸟在此抛砖引玉。）

换用下面语句检验用 NOT IN 替代 MINUS，能得出期望结果：
mysql> select * from t1,t2 where (id,name,code,price) not in (select * from t1,t2 where id = code);
+----+------+------+-------+
| id | name | code | price |
+----+------+------+-------+
| 2 | bb | 1 | 100 |
| 3 | cc | 1 | 100 |
| 1 | aa | 3 | 900 |
| 2 | bb | 3 | 900 |
| 1 | aa | 5 | 2500 |
| 2 | bb | 5 | 2500 |
| 3 | cc | 5 | 2500 |
+----+------+------+-------+ 
可以看到这是广义笛卡尔积减去INNER JOIN的结果集。

posted on 2014-03-03 19:30 何云隆 阅读(420) 评论(0)  编辑  收藏 所属分类: MySQL


只有注册用户登录后才能发表评论。
网站导航: