【Java从0到架构师】SQL 多表查询

妖狐艹你老母 2022-08-31 06:58 179阅读 0赞

### 多表查询 ###

*  多表查询
 *  多表查询核心概念
 *  多表查询 SQL 示例
 *   *  INNER JOIN
     *  LEFT JOIN
     *  LEFT JOIN EXCLUDING INNER JOIN
     *  RIGHT JOIN
     *  RIGHT JOIN EXCLUDING INNER JOIN
     *  FULL OUTER JOIN
     *  FULL OUTER JOIN EXCLUDING INNER JOIN
 *  更多的表
 *  排序、分页
 *  子查询

> Java 从 0 到架构师目录：[【Java从0到架构师】学习记录][Java_0]

# 多表查询 #

**内连接**：`INNER JOIN`、`CROSS JOIN`、`JOIN`

> 在 MySQL 中，上述内连接等价；但在标准 SQL 中，它们并不等价

**外连接**：`LEFT [OUTER] JOIN`、`RIGHT [OUTER] JOIN`

**并集**：`UNION`

> MySQL 并不支持标准 SQL 的 `FULL [OUTER] JOIN`，但可以用 `UNION` 来替代实现

参考教程：[【官方教程】JOIN语法][JOIN]

--------------------

`ON` 和 `WHERE` 后面都可以跟着条件，它们的区别是：

*  `ON`：配合 `JOIN` 语句使用，用以指定如何连接表的条件
 *  `WHERE`：限制哪些记录出现在结果集中

`INNER JOIN` 和 逗号`,` 在没有连接条件的情况下，语义上是等价的

*  都在指定的表之间产生 **笛卡儿乘积 (Cartesian Product)**
 *  也就是说，第一个表中的每一行都连接到第二个表中的每一行

# 多表查询核心概念 #

`LEFT JOIN` 对应的 `ON` 条件其实就是两个表共同的部分  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 2]

# 多表查询 SQL 示例 #

目前有两张表：

*  customer：顾客信息表  
    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 3][]
 *  company：公司信息表  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 4]

> **有顾客的公司信息（附带顾客信息）** = **有公司的顾客信息（附带公司信息）**

## INNER JOIN ##

**内连接**：查询出所有有公司信息的顾客信息（附带公司信息）

*  等价于 所有有顾客信息的公司信息（附带顾客信息）  
    ![在这里插入图片描述][141f9d3b5cba462b9baa624cc7742209.png]

# l ∩ r
    SELECT * FROM 
    	customer l
    INNER JOIN 
    	company r
    ON
    	l.company_id = r.id;

下面的写法等价于上面的内连接：

SELECT * FROM 
    	customer l, company r
    WHERE	l.company_id = r.id;

## LEFT JOIN ##

**左外连接**：查询出所有有公司和没有公司的顾客信息（附带公司信息）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 5]

# l ∪ (l ∩ r)
    SELECT * FROM 
    	customer l
    LEFT JOIN 
    	company r
    ON
    	l.company_id = r.id;

## LEFT JOIN EXCLUDING INNER JOIN ##

**有 WHERE 的左外连接**：查询出所有没有公司的顾客信息（附带公司信息）

*  排除了有公司的顾客信息（附带公司信息）  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 6]

# l - (l ∩ r)
    SELECT * FROM 
    	customer l
    LEFT JOIN 
    	company r
    ON
    	l.company_id = r.id
    WHERE
    	r.id IS NULL;

## RIGHT JOIN ##

**右外连接**：查询出所有有顾客和没有顾客的公司信息（附带顾客信息）  
![在这里插入图片描述][a542cd0354754a6b991799e39f1eda84.png]

# r ∪ (l ∩ r)
    SELECT * FROM 
    	customer l
    RIGHT JOIN 
    	company r
    ON
    	l.company_id = r.id;

## RIGHT JOIN EXCLUDING INNER JOIN ##

**带 WHERE 的右外连接**：查询出所有没有顾客的公司信息（附带顾客信息）

*  排除了有顾客的公司信息（附带顾客信息）  
    ![在这里插入图片描述][bd0002bd4bf440a79b0a28df97987ef7.png]

# r - (l ∩ r)
    SELECT * FROM 
    	customer l
    RIGHT JOIN 
    	company r
    ON
    	l.company_id = r.id
    WHERE
    	l.company_id IS NULL;

> SQL 规范中定义了 **FULL OUTER JOIN（全外连接）**，MySQL 中实现需要依靠 `UNION`

## FULL OUTER JOIN ##

**全外连接**：查询出所有：

*  有顾客的公司信息（附带顾客信息）  
    等价于 有公司的顾客信息（附带公司信息）
 *  没有顾客的公司信息（附带顾客信息）
 *  没有公司的顾客信息（附带公司信息）  
    ![在这里插入图片描述][30085f59342a414aad9aa3a590a4de84.png]

# l ∪ r
    (
    SELECT * FROM 
    	customer l
    LEFT JOIN 
    	company r
    ON
    	l.company_id = r.id
    )
    UNION
    (
    SELECT * FROM 
    	customer l
    RIGHT JOIN 
    	company r
    ON
    	l.company_id = r.id
    );

## FULL OUTER JOIN EXCLUDING INNER JOIN ##

**排除内连接的全连接**，查询出所有：

*  没有顾客的公司信息（附带顾客信息）
 *  没有公司的顾客信息（附带公司信息）  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 7]

# (l ∪ r) - (l ∩ r)
    (
    SELECT * FROM 
    	customer l
    LEFT JOIN 
    	company r
    ON
    	l.company_id = r.id
    WHERE
    	r.id IS NULL
    )
    UNION
    (
    SELECT * FROM 
    	customer l
    RIGHT JOIN 
    	company r
    ON
    	l.company_id = r.id
    WHERE
    	l.company_id IS NULL
    
    );

# 更多的表 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 9]

# 排序、分页 #

**排序**：

ORDER BY 字段 [ASC | DESC]

**分页**：

LIMIT {
       [offset,] row_count | row_count OFFSET offset}

*  offset 是记录的偏移量（最小值是 0），从哪一条记录开始选择
 *  row\_count 是希望选择的记录数量

代码示例：从第 10 条记录开始，选择 20 条记录

LIMIT 10, 20
    
    LIMIT 20 OFFSET 10

# 子查询 #

当一个查询是另一个查询的条件时，称之为**子查询**

代码示例：

SELECT * FROM customer WHERE company_id = (
    	SELECT id FROM company WHERE name = '腾讯'
    )

[Java_0]: https://blog.csdn.net/weixin_43734095/article/details/119067034
[JOIN]: http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/join.com.coder114.cn.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70]: /images/20220829/675198e8528d4781affac7d4069098f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 1]: /images/20220829/638c7c51e42f4fa0b9e885b3ce428f9d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 2]: /images/20220829/965ca69ff6a14603b671aeffb0824da0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 3]: /images/20220829/81cbf135637f48f48cc27e89c6b974f2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 4]: /images/20220829/bdfb0d686d7c4c7e81459af0d8cddf48.png
[141f9d3b5cba462b9baa624cc7742209.png]: /images/20220829/32a7dfd9ece7423fb781c9400d7933d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 5]: /images/20220829/19f2a3d4f6dc404cbb6e8b98ae8a3b77.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 6]: /images/20220829/7ed7beed21f64432a0be823d681c0368.png
[a542cd0354754a6b991799e39f1eda84.png]: /images/20220829/30b4428874914df78a964905c7d383ce.png
[bd0002bd4bf440a79b0a28df97987ef7.png]: /images/20220829/f8d77edcae5d411da7eb4d740a8799ca.png
[30085f59342a414aad9aa3a590a4de84.png]: /images/20220829/d2364e19eafe4363a2fe3176665bb16f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 7]: /images/20220829/948500e62b2c476282e12bcb624096e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 8]: /images/20220829/95f0d184f8824cf0bc968525bb383097.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70 9]: /images/20220829/31f2f6fe240f4c4590ecfdec74e675c5.png