联合查询(详细篇)

实际开发中往往数据来自不同的表 所以需要多表联合查询 多表查询是对多张表的数据取笛卡尔积

笛卡尔积

什么是笛卡尔积呢？

简单来说 笛卡尔积是两个表的乘积 结果集中的每一行都是第一个表的每一行与第二个表的每一行的组合

简单理解：

假设有两个表A和B 表A有m行 表B有n行 当对这两个表进行笛卡尔积操作时 生成的结果集将包含m*n个记录 这是因为结果集中的每一行都是表A中的一行与表B中的一行的组合

示例：

假设有两个表，一个是学生信息表（student），另一个是班级信息表（class）

学生信息表（student）：

班级信息表（class）：

对这两个表进行笛卡尔积操作，生成的结果集如下：

注意，这里的结果集中有两个名为“classid”的列，这是因为它们分别来自学生信息表和班级信息表。在实际应用中，为了避免混淆，通常会使用表名或别名来区分这些列

笛卡尔积的注意事项

1.性能问题：笛卡尔积操作会生成大量的数据，特别是当涉及的表很大时。这可能导致查询性能下降，甚至导致数据库崩溃。因此，在实际应用中应尽量避免无意的笛卡尔积操作。

2.无效数据：笛卡尔积操作生成的结果集中可能包含大量无效的数据。这些数据通常是由于没有指定连接条件而产生的。因此，在使用笛卡尔积时，应确保已经明确了所需的连接条件，并使用这些条件来过滤无效的数据。

3.使用JOIN代替：为了避免笛卡尔积操作带来的问题，通常建议使用JOIN操作来替代笛卡尔积。JOIN操作可以根据指定的连接条件来合并两个表的数据，从而生成更精确、更有用的结果集

联合查询

当需要从多个表中检索相似类型的数据 并将这些数据组合成一个结果集时 可以使用联合查询

内连接（inner join)

定义:内连接是最常用的联合查询方式 它只返回满足连接条件的行 可以通过多个表之间的共同字段来进行连接

特点：

1.只返回两个表中满足连接条件的行

2.如果两个表的某一对匹配的行在连接条件上不符合 那么这一对行就不会出现在结果集中

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

示例：

假设我们有两个表：employees(员工表)和departments(部门表)

内连接示例：

查询语句：

SELECT employees.name, departments.department_name  
FROM employees  
INNER JOIN departments  
ON employees.department_id = departments.id;

查询结果：

解释：

1.SELECT子句：指定了要从连接结果中选择的列，即employees.name和departments.department_name。

2.FROM子句：指定了第一个表employees。

3.INNER JOIN子句：表示执行内连接操作，连接employees表和departments表。

4.ON子句：指定了连接条件，即employees.department_id = departments.id。这意味着只有当employee表中的department_id与department表中的id相匹配时，才会返回相应的行

外连接

外连接是SQL中用于连接多个表的操作 它会返回符合连接条件的行 并且如果某个表中没有满足条件的匹配行 则使用NULL值填充 外连接主要包括左外连接、右外连接和全外连接(在某些数据库系统中，如MySQL中，可能不是直接支持的，但可以通过左外连接和右外连接的并集来实现)

我们这里先不讲解全外连接 

左外连接(left join)

左外连接：返回左表的所有行以及与其关联的右表的匹配行 如果右表中没有匹配行 则用NULL值填充

语法：

select * from 表1 left join 表2 on 连接条件;

示例：

假设我们有两个表：Customers(顾客表)和Orders(订单表)

左外连接示例：

MySQL查询语句：

SELECT Orders.OrderID, Customers.CustomerName, Orders.Product  
FROM Customers  
LEFT JOIN Orders  
ON Customers.CustomerID = Orders.CustomerID;

查询结果：

在这个结果中，Customers表中的所有行都返回了，即使它们在Orders表中没有匹配的订单。对于没有订单的顾客，OrderID和Product列显示为NULL

右外连接(right join)

右外连接：返回右表的所有行以及与其关联的左表中的匹配行 如果左表中没有匹配行 则用NULL值填充

语法：

select * from 表1 right join 表2 on连接条件;

MySQL查询语句：

SELECT Orders.OrderID, Customers.CustomerName, Orders.Product  
FROM Customers  
RIGHT JOIN Orders  
ON Customers.CustomerID = Orders.CustomerID;

查询结果：

与左外连接的结果相同，因为在这种情况下，两个表之间的连接条件是对称的。但通常，右外连接更常用于以右表为基础返回数据的场景

自连接

自连接是一种查询技术 它允许我们将同一张表视为两个不同的表(通常通过为表指定别名来实现) 然后基于这连个‘虚拟表’之间的某个共同字段(如ID)进行连接

语法：

SELECT 列名  
FROM 表名 AS 表别名1  
JOIN 表名 AS 表别名2  
ON 表别名1.列名 = 表别名2.列名  
WHERE 条件;

在这个语法中，使用AS关键字给表起了别名，以便区分两个表的使用。然后使用JOIN关键字指定连接条件，最后使用WHERE关键字指定额外的筛选条件

应用场景

自连接通常用于需要查询表中记录与自身记录相关联的场景 

例如，有一个员工表 其中包含员工的ID和上级领导的ID 我们想要查询每个员工的名字和他们的上级领导的名字 就可以使用自连接来实现

示例：

假设我们有一个名为employees的员工表，其结构如下：

CREATETABLE employees (  id INT PRIMARY KEY,  name VARCHAR(50),  supervisor_id INT  
);

并插入一些示例数据：

INSERT INTO employees (id, name, supervisor_id) VALUES  
(1, 'John', NULL),  
(2, 'Jane', 1),  
(3, 'Mike', 2),  
(4, 'Sarah', 1);

我们可以使用自连接来查询每个员工的名字和他们的上级领导的名字：

SELECT   e.name AS employee_name,  s.name AS supervisor_name  
FROM   employees AS e  
JOIN   employees AS s  
ON   e.supervisor_id = s.id;

执行上述查询后，我们将得到以下结果：

在这个结果中，我们成功地查询到了每个员工的名字以及他们的上级领导的名字。

另外，自连接还可以用于查询表中记录与其子类或父类记录相关联的场景，如层级分类等。此时，通常需要在表中添加一个字段来存储类别的子类或者父类的ID，然后通过自连接来查询层级关系

子查询

子查询是指 嵌入在 其他sql语句中的 select语句 也叫嵌套查询

单行子查询：返回一行记录的子查询

示例：

查询大于公司平均工资的员工姓名

SELECT name  
FROM employees  
WHERE salary > (SELECT AVG(salary) FROM employees);

多行子查询：返回多行记录的子查询

示例：查询“语文”或“英文”课程的成绩信息

[NOT] IN 

-- 使用IN
select * from score where course_id 
in (select id from course where name='语文'or name='英文');--使用 NOT IN
select * from score where course_id 
not in (select id from course where name!='语文'and name!='英文');

多列包含

SELECT e.name, e.salary, d.department_name  
FROM employees e  
JOIN departments d ON e.department_id = d.department_id  
WHERE (e.department_id, e.salary) 
IN (SELECT department_id, MAX(salary)  FROM employees  GROUP BY department_id  
);

-- 使用 EXISTS
select * from score sco where 
exists (select sco.id from course cou 
where (name='语文'or name='英文') and cou.id = sco.course_id);-- 使用 NOT EXISTS
select * from score sco where 
not exists (select sco.id from course cou where 
(name!='语文'and name!='英文') and cou.id = sco.course_id);

示例2：

返回 工资高于其所在部门平均工资的 员工的姓名、工资和部门平均工资

SELECT e1.name, e1.salary, e2.avg_salary  
FROM employees e1  
JOIN 
(SELECT department_id, AVG(salary) AS avg_salary 
FROM employees GROUP BY department_id) e2  
ON e1.department_id = e2.department_id  
WHERE e1.salary > e2.avg_salary;

解释：

(SELECT department_id, AVG(salary) AS avg_salary 
FROM employees GROUP BY department_id)

首先被执行，它计算每个部门的平均工资，并生成一个包含department_id和avg_salary的结果集。这个结果集在查询的上下文中被当作一个临时表，并被赋予别名e2

然后，外部查询使用这个临时表（e2）与employees表（别名为e1）进行连接，基于department_id字段匹配记录。最后，通过where子句过滤出工资高于部门平均工资的员工记录

合并查询

合并查询是指将两个或多个SELECT语句的结果集合并成一个大的结果集。这个特性使得合并查询成为了SQL语言中非常强大和常用的功能之一

在SQL中，合并查询主要使用两个操作符：UNION和UNION ALL

1. UNION：将两个或多个查询的结果集合并在一起，并去除重复的行。UNION操作会对结果集进行排序和去重，因此可能需要大量的CPU和内存资源。

2. UNION ALL：将两个或多个查询的结果集合并在一起，但不去除重复的行。与UNION相比，UNION ALL的性能通常更好，因为它不会进行排序和去重操作

~union

示例：

SELECT name, salary FROM employees_2022  
UNION  
SELECT name, salary FROM employees_2023;

 返回employees_2022和employees_2023两个表中所有员工的姓名和工资信息，会去除重复的行

~union all

SELECT name, salary FROM employees_2022  
UNION ALL  
SELECT name, salary FROM employees_2023;

返回employees_2022和employees_2023两个表中所有员工的姓名和工资信息，并且不会去除重复的行