SQL JOINS

示意图

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)

SQL JOINS

示意图

事前准备

INNER JOIN （内连接）

LEFT JOIN （左连接）

RIGHT JOIN （右连接）

OUTER JOIN （外连接）

LEFT EXCULDING JOIN

RIGHT EXCULDING JOIN

OUTER EXCULDING JOIN

推荐阅读更多精彩内容