MYSQL8多表联合查询

MYSQL8多表联合查询

• 多表联合查询
• 常用连接查询

• 多表查询--数据准备
• 交叉连接查询
• 内连接查询：

• 隐式内连接
• 显示内连接
• 内连接示例

• 外连接：

• 左外连接
• 右外连接:

• 外连接

• 使用using子句
• 相互关联子查询
• WITH AS子句
• 满外连接

• 不加`all`会去重
• `UNION all`数据会重复

• 自关联查询

• 进行关联查询

• 自关联查询示例：

多表联合查询

通常情况下，SQL多表查询(Join)比子查询效率高。这是因为在使用子查询时，需要执行多个查询语句，而在使用关联查询时只需要执行单个查询语句。

当使用子查询时，子查询语句将被执行一次，并返回一个结果集，这个结果集将用于主查询中的条件判断。如果主查询中有多个条件需要使用子查询返回的结果集进行比较，那么就需要执行多个子查询语句，这会大大降低查询效率。

相比之下，使用关联查询时，所有的查询条件都包含在单个查询语句中。这样可以减少查询的次数，提高查询效率。此外，关联查询还可以利用索引来加速查询，进一步提高查询效率。

尽管关联查询通常比子查询效率更高，但在某些特定情况下，子查询可能会更加适合。例如，当需要在查询结果中动态计算数据或者需要使用聚合函数来汇总数据时，使用子查询会更加方便和灵活。

常用连接查询

多表查询–数据准备

交叉连接查询

会把左右表都for循环一遍，数据冗余，叫做笛卡尔积；

但我们实际需要的只是指定条件的值。

内连接查询：

求两张表交集的部分.

隐式内连接

-- 隐式内连接
-- 查询每个部门的所属员工
SELECT *
FROM dept3 as a,
    emp3 as b
WHERE a.deptno = b.dept_id;

显示内连接

-- 显示内连接
SELECT *
FROM dept3 as a
    JOIN emp3 as b ON a.deptno = b.dept_id;

-- 示例2
SELECT *
FROM a_table
    INNER JOIN b_table ON a_table.a_id = b_table.b_id;

内连接示例

外连接：

左外连接

-- 左连接,以左表为基础，展示出左表全部数据和右表交集部分数据
SELECT *
FROM a_table
    LEFT JOIN b_table ON a_table.a_id = b_table.b_id;

右外连接:

-- 右连接,以右表为基础，展示出右全部数据和左表交集部分数据
SELECT *
FROM a_table
    RIGHT JOIN b_table ON a_table.a_id = b_table.b_id ；

外连接

载SQL99标准中，两个表连接时，仅返回匹配的记录是内连接；

如果两个表进行连接时，返回的记录不仅有匹配的记录，而且还有不匹配的记录，这种连接被称为左外连接或右外连接；

如果两个表进行连接时，返回的记录不仅有匹配的记录，还有左连接和有连接的记录，这种连接被称为全外连接或满外连接。

左外连接：把右表中没有但左表中有的数据进行显示；

右外连接：把左表中没有但右表中有的数据进行显示；

使用using子句

假设我们有两个表：employees和departments。在employees表中，我们有一个名为department_id的列，它存储了每个员工所属的部门。在departments表中，我们有一个名为department_id的列，它存储了每个部门的唯一标识符。
现在，我们想要联接这两个表，并且只返回那些在两个表中都存在的部门。我们可以使用以下查询：

SELECT *
FROM employees
INNER JOIN departments
USING (department_id);

在上面的查询中，使用USING子句来指定要联接的列为department_id。这将只返回在两个表中都存在的部门。

请注意，USING子句只能用于联接相同名称的列。如果您要联接不同名称的列，则需要使用ON子句来指定要联接的列。

USING子句可以指定只匹配一列，也可以指定匹配多个列。

相互关联子查询

相互关联子查询用于行–执行–行的处理，主查询的每一行字查询都要执行一次。

WITH AS子句

注意：MySQL5.7之前没有with as子句，从5.7开始引入

满外连接

UNION合并表–full joinfull join是oracle数据库的独有指令，mysql不兼容该指令，可以通过UNION来合并左连接表和右连接表，实现full join的功能。

不加all会去重

-- 不加all会去重
SELECT * FROM student_copy UNION SELECT * FROM student;

UNION all数据会重复

-- all数据会重复
SELECT * FROM student_copy UNION all SELECT * FROM student;

自关联查询

-- 创建表，并建立自关联约束
create table t_sanguo(
    eit INT PRIMARY KEY,
    ename VARCHAR(20),
    manager_id INT,
    ---外键列
    FOREIGN KEY(manager_id) REFERENCES t_sanguo(eid) --添加自关联约束
);

进行关联查询

-- 进行关联查询
-- 1. 查询每个三国人物及他的上级信息
-- manage_id 是上级id
-- eid是员工id，上级也是员工，所有上级也有eid
SELECT *
FROM t_sanguo as a,
    t_sanguo as b
WHERE a.manager_id = b.eid;

自关联查询示例：