示意图
sql join.png
事前准备
create table Table_A(id int,value VARCHAR(50));
create table Table_B(id int,value VARCHAR(50));
insert Table_A VALUES(1,'FOX');
insert Table_A VALUES(2,'COP');
insert Table_A VALUES(3,'TAXI');
insert Table_A VALUES(4,'LINCION');
insert Table_A VALUES(5,'ARIZONA');
insert Table_A VALUES(6,'WASHINGTON');
insert Table_A VALUES(7,'DELL');
insert Table_A VALUES(10,'LUCENT');
insert Table_B VALUES(1,'TROT');
insert Table_B VALUES(2,'CAR');
insert Table_B VALUES(3,'CAB');
insert Table_B VALUES(6,'MONUMENT');
insert Table_B VALUES(7,'PC');
insert Table_B VALUES(8,'MICROSOFT');
insert Table_B VALUES(9,'APPLE');
insert Table_B VALUES(11,'SCOTCH');
INNER JOIN （内连接）
INNER_JOIN.png
示例查询
SELECT A.id AS A_PK, B.id AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
INNER JOIN Table_B B
ON A.id = B.id;
结果如下
+------+------+------------+----------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+------------+----------+
| 1 | 1 | FOX | TROT |
| 2 | 2 | COP | CAR |
| 3 | 3 | TAXI | CAB |
| 6 | 6 | WASHINGTON | MONUMENT |
| 7 | 7 | DELL | PC |
+------+------+------------+----------+
5 rows in set (0.00 sec)
LEFT JOIN （左连接）
LEFT_JOIN.png
SELECT A.id AS A_PK, B.id AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
LEFT JOIN Table_B B
ON A.id = B.id;
结果如下
+------+------+------------+----------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+------------+----------+
| 1 | 1 | FOX | TROT |
| 2 | 2 | COP | CAR |
| 3 | 3 | TAXI | CAB |
| 4 | NULL | LINCION | NULL |
| 5 | NULL | ARIZONA | NULL |
| 6 | 6 | WASHINGTON | MONUMENT |
| 7 | 7 | DELL | PC |
| 10 | NULL | LUCENT | NULL |
+------+------+------------+----------+
8 rows in set (0.00 sec)
RIGHT JOIN （右连接）
RIGHT_JOIN.png
SELECT A.id AS A_PK, B.id AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
RIGHT JOIN Table_B B
ON A.id = B.id;
结果如下
+------+------+------------+----------+
+------+------+------------+-----------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+------------+-----------+
| 1 | 1 | FOX | TROT |
| 2 | 2 | COP | CAR |
| 3 | 3 | TAXI | CAB |
| 6 | 6 | WASHINGTON | MONUMENT |
| 7 | 7 | DELL | PC |
| NULL | 8 | NULL | MICROSOFT |
| NULL | 9 | NULL | APPLE |
| NULL | 11 | NULL | SCOTCH |
+------+------+------------+-----------+
8 rows in set (0.00 sec)
OUTER JOIN （外连接）
FULL_OUTER_JOIN.png
SELECT A.id AS A_PK, B.id AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
OUTER JOIN Table_B B
ON A.id = B.id;
结果如下
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 'FULL OUTER JOIN Table_B B
ON A.PK = B.PK' at line 4
注：我当前示例使用的 MySQL 不支持FULL OUTER JOIN。
应当返回的结果（使用 UNION 模拟）：
SELECT Table_A.id AS A_PK, Table_B.id AS B_PK,
Table_A.Value AS A_Value, Table_B.Value AS B_Value
FROM
Table_A
LEFT JOIN Table_B ON Table_A.id = Table_B.id
UNION ALL
SELECT
Table_A.id AS A_PK, Table_B.id AS B_PK,
Table_A.Value AS A_Value, Table_B.Value AS B_Value
FROM
Table_A
RIGHT JOIN Table_B ON Table_A.id = Table_B.id;
+------+------------+------+-----------+
| PK | Value | PK | Value |
+------+------------+------+-----------+
| 1 | FOX | 1 | TROT |
| 2 | COP | 2 | CAR |
| 3 | TAXI | 3 | CAB |
| 4 | LINCION | NULL | NULL |
| 5 | ARIZONA | NULL | NULL |
| 6 | WASHINGTON | 6 | MONUMENT |
| 7 | DELL | 7 | PC |
| 10 | LUCENT | NULL | NULL |
| NULL | NULL | 8 | MICROSOFT |
| NULL | NULL | 9 | APPLE |
| NULL | NULL | 11 | SCOTCH |
+------+------------+------+-----------+
11 rows in set (0.00 sec)
LEFT EXCULDING JOIN
LEFT_EXCLUDING_JOIN.png
SELECT A.id AS A_PK, B.id AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
LEFT JOIN Table_B B
ON A.id = B.id
WHERE B.id is null;
结果如下
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
| 4 | NULL | LINCION | NULL |
| 5 | NULL | ARIZONA | NULL |
| 10 | NULL | LUCENT | NULL |
+------+------+---------+---------+
3 rows in set (0.00 sec)
RIGHT EXCULDING JOIN
RIGHT_EXCLUDING_JOIN.png
SELECT A.id AS A_PK, B.id AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
RIGHT JOIN Table_B B
ON A.id = B.id
WHERE A.id is null;
结果如下
+------+------+---------+-----------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+-----------+
| NULL | 8 | NULL | MICROSOFT |
| NULL | 9 | NULL | APPLE |
| NULL | 11 | NULL | SCOTCH |
+------+------+---------+-----------+
3 rows in set (0.00 sec)
OUTER EXCULDING JOIN
FULL_OUTER_JOIN.png
SELECT A.id AS A_PK, B.id AS B_PK,
A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.id = B.id
WHERE A.id is null
and B.id is null;
结果如下
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 'FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL' at line 4
应当返回的结果（用 UNION 模拟）：
SELECT
Table_A.id AS A_PK,
Table_B.id AS B_PK,
Table_A.
VALUE
AS A_Value,
Table_B.
VALUE
AS B_Value
FROM
Table_A
LEFT JOIN Table_B ON Table_A.id = Table_B.id
WHERE
Table_B.id IS NULL
UNION ALL
SELECT
Table_A.id AS A_PK,
Table_B.id AS B_PK,
Table_A.
VALUE
AS A_Value,
Table_B.
VALUE
AS B_Value
FROM
Table_A
RIGHT JOIN Table_B ON Table_A.id = Table_B.id
Table_A.id IS NULL;
+------+---------+------+-----------+
| PK | Value | PK | Value |
+------+---------+------+-----------+
| 4 | LINCION | NULL | NULL |
| 5 | ARIZONA | NULL | NULL |
| 10 | LUCENT | NULL | NULL |
| NULL | NULL | 8 | MICROSOFT |
| NULL | NULL | 9 | APPLE |
| NULL | NULL | 11 | SCOTCH |
+------+---------+------+-----------+
6 rows in set (0.00 sec)
网友评论