mysql 全连接问题_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 何云隆 阅读(157) 评论(0)  编辑  收藏 所属分类: MySQL