用mysql做一个软件,但是需要用到 全外连接 ,但是用全外连接时总是报错,在网上查了一下,说mysql 不支持全外连接,我晕。
我用的mysql版本是5.0.22,(左外连接和右外连接都很好用)问题 1:这样在不支持全外连接的情况先实现 全外连接 的查询功能?
问题 2:mysql到底不支持全外连接吗?(是不是我用的版本低的原因)
我的测试用例:表A 结构
id int(11) 主码
numa int(11)
name varchar(11)
pass varchar(11) 数据:
+----+------+------+------+
| id | numa | name | pass |
+----+------+------+------+
| 1 | 1 | a | a |
| 2 | 2 | b | b |
+----+------+------+------+表B 结构 id int(11) 主码
numb int(11)
readme varchar(11) 数据:
+----+------+---------+
| id | numb | readme |
+----+------+---------+
| 1 | 1 | 1111111 |
| 10 | 10 | 1010101 |
+----+------+---------+左外连接:
mysql> select * from a left outer join b on numa=numb;
+----+------+------+------+------+------+---------+
| id | numa | name | pass | id | numb | readme |
+----+------+------+------+------+------+---------+
| 1 | 1 | a | a | 1 | 1 | 1111111 |
| 2 | 2 | b | b | NULL | NULL | NULL |
+----+------+------+------+------+------+---------+右外连接:
mysql> select * from a right outer join b on numa=numb;
+------+------+------+------+----+------+---------+
| id | numa | name | pass | id | numb | readme |
+------+------+------+------+----+------+---------+
| 1 | 1 | a | a | 1 | 1 | 1111111 |
| NULL | NULL | NULL | NULL | 10 | 10 | 1010101 |
+------+------+------+------+----+------+---------+全外连接:
mysql> select * from a full outer join b on numa=numb;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 'outer
join b on numa=numb' at line 1
我用的mysql版本是5.0.22,(左外连接和右外连接都很好用)问题 1:这样在不支持全外连接的情况先实现 全外连接 的查询功能?
问题 2:mysql到底不支持全外连接吗?(是不是我用的版本低的原因)
我的测试用例:表A 结构
id int(11) 主码
numa int(11)
name varchar(11)
pass varchar(11) 数据:
+----+------+------+------+
| id | numa | name | pass |
+----+------+------+------+
| 1 | 1 | a | a |
| 2 | 2 | b | b |
+----+------+------+------+表B 结构 id int(11) 主码
numb int(11)
readme varchar(11) 数据:
+----+------+---------+
| id | numb | readme |
+----+------+---------+
| 1 | 1 | 1111111 |
| 10 | 10 | 1010101 |
+----+------+---------+左外连接:
mysql> select * from a left outer join b on numa=numb;
+----+------+------+------+------+------+---------+
| id | numa | name | pass | id | numb | readme |
+----+------+------+------+------+------+---------+
| 1 | 1 | a | a | 1 | 1 | 1111111 |
| 2 | 2 | b | b | NULL | NULL | NULL |
+----+------+------+------+------+------+---------+右外连接:
mysql> select * from a right outer join b on numa=numb;
+------+------+------+------+----+------+---------+
| id | numa | name | pass | id | numb | readme |
+------+------+------+------+----+------+---------+
| 1 | 1 | a | a | 1 | 1 | 1111111 |
| NULL | NULL | NULL | NULL | 10 | 10 | 1010101 |
+------+------+------+------+----+------+---------+全外连接:
mysql> select * from a full outer join b on numa=numb;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 'outer
join b on numa=numb' at line 1
将结果贴出来看看
-> select * from a right join b on numa=numb;
+------+------+------+------+------+------+---------+
| id | numa | name | pass | id | numb | readme |
+------+------+------+------+------+------+---------+
| 1 | 1 | a | a | 1 | 1 | 1111111 |
| 2 | 2 | b | b | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | 10 | 10 | 1010101 |
+------+------+------+------+------+------+---------+
呵呵,这个就是要的结果。
select * from a left join b on numa=numb union
select * from a right join b on numa=numb WHERE ISNULL(A.ID)
select * from table1 full join table2;
得到的也是全部的结果集