联结查询-原创手记-慕课网

联结查询

关系数据库的最大特点就是：把数据分解成多个表，一类数据一个表，而各表通过主键互相关联。
如果想用一条SELECT语句就检索出数据，就需要使用联结查询，在一条SELECT语句中关联多个表。

表别名

SQL除了可以对列名和计算字段使用别名，还允许给表名起别名。

Students表：

| id  | class_id | name | gender | score |
| --- | -------- | ---- | ------ | ----- |
| 1   | 1        | 小明 | M      | 90    |
| 2   | 1        | 小红 | F      | 95    |
| 3   | 1        | 小军 | M      | 88    |
| 4   | 1        | 小米 | F      | 73    |
| 5   | 2        | 小白 | F      | 81    |
| 6   | 2        | 小兵 | M      | 55    |
| 7   | 2        | 小林 | M      | 85    |
| 8   | 3        | 小新 | F      | 91    |
| 9   | 3        | 小王 | M      | 89    |
| 10  | 3        | 小丽 | F      | 88    |

Classes表：

| id | name |
|----|------|
| 1  | 一班  |
| 2  | 二班  |
| 3  | 三班  |
| 4  | 四班  |

使用表别名：

Students AS S
-- Students S

Classes AS C
-- Classes C

内联结（INNER JOIN）

只返回两个表中匹配的数据。

SELECT
  S.name,
  C.name class_name,
  S.score
FROM
  Students S,
  Classes C
WHERE
  S.class_id = C.id
ORDER BY
  S.score DESC
  
| name | class_name | score |
|------|------------|-------|
| 小红  | 一班       |  95   |
| 小新  | 三班       |  91   |
| 小明  | 一班       |  90   |
| 小王  | 三班       |  89   |
| 小军  | 一班       |  88   |
| 小丽  | 三班       |  88   |
| 小林  | 二班       |  85   |
| 小白  | 二班       |  81   |
| 小米  | 一班       |  73   |
| 小兵  | 二班       |  55   |

SELECT语句指定要检索的列名，
FROM子句列出了两个表：Students和Classes，它们就是这条SELECT语句联结的两个表的名字，
WHERE子句作为联结条件，实际要做的是将第一个表中的每一行与第二个表中的每一行进行匹配，将满足匹配条件的检索出来。

INNER JOIN语法

内联结除了上面这种简单的写法，也有自己的标准规则：INNER JOIN。

SELECT
  S.name,
  C.name class_name,
  S.score
FROM
  students S
  INNER JOIN classes C ON S.class_id = C.id
ORDER BY
  S.score DESC
  
| name | class_name | score |
|------|------------|-------|
| 小红  | 一班       |  95   |
| 小新  | 三班       |  91   |
| 小明  | 一班       |  90   |
| 小王  | 三班       |  89   |
| 小军  | 一班       |  88   |
| 小丽  | 三班       |  88   |
| 小林  | 二班       |  85   |
| 小白  | 二班       |  81   |
| 小米  | 一班       |  73   |
| 小兵  | 二班       |  55   |

FROM子句中，两个表之间的关系由INNER JOIN指定，在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。

自联结（SELF JOIN）

通过将表与其自身进行比较来查询表中的数据。

SELECT
  S1.name,
  S1.score
FROM
  Students S1,
  Students S2
WHERE
  S1.score > S2.score
  AND S2.name = '小军'
ORDER BY
  S1.score DESC;

+------+-------+
| name | score |
|------|-------|
| 小红  |  95   |
| 小新  |  91   |
| 小明  |  90   |
| 小王  |  89   |
+------+-------+

此查询中需要的两个表实际上是相同的表，因此需要使用表别名。

左外联结（LEFT JOIN 或 LEFT OUTER JOIN）

LEFT指出的是OUTER JOIN左边的表。
返回左表中的所有数据，以及右表中与左表匹配的数据。
如果右表中没有匹配，则返回NULL。

SELECT
  S.name,
  C.name AS class_name,
  S.score
FROM
  students S
  LEFT OUTER JOIN classes C ON S.class_id = C.id
ORDER BY
  S.score DESC;
  
| name | class_name | score |
|------|------------|-------|
| 小红  | 一班       |  95   |
| 小新  | 三班       |  91   |
| 小明  | 一班       |  90   |
| 小王  | 三班       |  89   |
| 小军  | 一班       |  88   |
| 小丽  | 三班       |  88   |
| 小林  | 二班       |  85   |
| 小白  | 二班       |  81   |
| 小米  | 一班       |  73   |
| 小兵  | 二班       |  55   |

右外联结（RIGHT JOIN 或 RIGHT OUTER JOIN）

RIGHT指出的是OUTER JOIN右边的表。
返回右表中的所有数据，以及左表中与右表匹配的数据。
如果左表中没有匹配，则返回NULL。

SELECT
  S.name,
  C.name AS class_name,
  S.score
FROM
  students S
  RIGHT OUTER JOIN classes C ON S.class_id = C.id
ORDER BY
  S.score DESC;
  
| name | class_name | score |
|------|------------|-------|
| 小红  | 一班       |  95   |
| 小新  | 三班       |  91   |
| 小明  | 一班       |  90   |
| 小王  | 三班       |  89   |
| 小军  | 一班       |  88   |
| 小丽  | 三班       |  88   |
| 小林  | 二班       |  85   |
| 小白  | 二班       |  81   |
| 小米  | 一班       |  73   |
| 小兵  | 二班       |  55   |
| NULL | 四班       | NULL  |