MySQL不支持FULL JOIN, INTERSECT和MINUS(except)的替代方法

基本资料：
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的结果集。

=======================================================================================================

SQL中intersect、union、minus和except 运算符

1、intersect运算符
intersect运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (intersect  all)，不消除重复行。
2、minus运算符
minus运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 all随 minus一起使用时 (minus all)，不消除重复行。
3、union运算符

union运算符是将两个或更多查询的结果组合为单个结果集 

table1:
f_name  f_date
name1   2009-6-1
name2   2009-6-2

table2:
f_name  f_date
name3   2009-6-2
name4   2009-6-3

select f_date from table1 intersect select date from table2
结果：
name2   2009-6-2


select f_date from table1 union select f_date from table2
结果：

2009-6-1
2009-6-2
2009-6-3

注：except
只能用于SQLserver
在SQLserver中：
select f_date from table1 except select f_date from table2
结果：
2009-6-1
在oracle中用minus：
select f_date from table1 minus select f_date from table2
结果：
2009-6-1

SQL语句中的三个关键字:MINUS(减去),INTERSECT(交集)和UNION ALL(并集);

 

关于集合的概念,中学都应该学过,就不多说了.这三个关键字主要是对数据库的查询结果进行操作,正如其中文含义一样:两个查询,MINUS是从第一个查询结果减去第二个查询结果,如果有相交部分就减去相交部分;否则和第一个查询结果没有区别. INTERSECT是两个查询结果的交集,UNION ALL是两个查询的并集;