mysql支不支持fulljoin_MySQL不支持FULL JOIN, INTERSECT和MINUS的替代方法

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

(2014-05-13 10:19:22)

标签：

成果

精确的

不支持

检讨

基本资料：

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的成果集。

分享：

喜欢

0

赠金笔

加载中，请稍候......

评论加载中，请稍候...

发评论

登录名： 密码： 找回密码 注册记住登录状态

昵   称：

评论并转载此博文

发评论

以上网友发言只代表其个人观点，不代表新浪网的观点或立场。