【MySQL】关联查询（多表查询）

关联查询（多表查询）

交叉连接（CROSS JOIN）

  • 定义：返回两个表的笛卡尔积，即第一个表中的每一行，与第二个表中的每一行组合。如果涉及多于两个表，则依次进行。
======= 🌟 青柠来相伴，代码更简单。🌟 =======
📚 本文所有内容，我都整理在了 青柠合集 里。👇
🎯 搜索关注【青柠代码录】，即可查看所有合集文章 ~
======= 🌟 ================ 🌟 =======
  • 语法：
SELECT * FROM A, B (, C)
-- 或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C)
  • 特点：没有任何关联条件，结果集会很大，通常在实际应用中很少使用。
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

笛卡尔积也称为交叉连接，英文是 CROSS JOIN。

在 SQL99 中也是使用 CROSS JOIN表示交叉连接。

它的作用就是可以把任意表进行连接，即使这两张表不相关。

  • 笛卡尔积的错误会在下面条件下产生：

  • 省略多个表的连接条件（或关联条件）

  • 连接条件（或关联条件）无效

  • 所有表中的所有行互相连接

  • 为了避免笛卡尔积， 可以在WHERE 加入有效的连接条件。

内连接（INNER JOIN）

  • 定义：基于一个匹配条件来结合两个表的数据。只有当连接条件满足时，才会返回记录。
  • 语法：
SELECT * FROM A, B WHERE A.id = B.id
-- 或者
SELECT * FROM A INNER JOIN B ON A.id = B.id

  • 特点：可以缩写为 JOIN。用于检索多个表中同时符合某种条件的数据记录的集合。

  • 分为三类

  • 等值连接：ON A.id=B.id

  • 不等值连接：ON A.id > B.id

  • 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

包括等值连接、非等值连接、自连接 -------------- 取交集

select 查询列表
from 表1 别名
join 表2 别名
on 连接条件;

特点：

①添加排序、分组、筛选

②inner可以省略

③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1）等值连接

1）多表等值连接的结果为多表的交集部分
2）n表连接，至少需要n-1个连接条件
3）多表的顺序没有要求
4）一般需要为表起别名
5）可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

案例1：查询女神名称和对应男神名称

SELECT beauty.id,NAME,boyname 
FROM beauty ,boys 
WHERE beauty.boyfriend_id=boys.id;  #条件是等号为等值连接

2）非等值连接

案例1：查询员工的工资和工资级别

SELECT salary,grade_level

FROM employees e,job_grades g

WHERE salary BETWEEN g.lowest_sal AND g.highest_sal

AND g.grade_level='A';

3）自连接

#案例：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;

外连接（LEFT JOIN / RIGHT JOIN）

  • 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
  • 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

包括左外连接、右外连接、全外连接。

用于查询一个表中有，另一个表没有的记录

特点：
 1、外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果从表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有而从表没有的记录
 2、左外连接，left join左边的是主表
    右外连接，right join右边的是主表
 3、左外和右外交换两个表的顺序，可以实现同样的效果 
 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

1）左外连接

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

查询男朋友不在男神表的的女神名

#左外连接
 SELECT b.name,bo.*
 FROM beauty b
 LEFT OUTER JOIN boy bo
 ON b.boyfriend_id = bo.id
 WHERE b.id IS NULL;

2）右外连接

SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

3）全外连接

USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

联合查询（UNION 与 UNION ALL）

SELECT * FROM A UNION SELECT * FROM B UNION ...
  • 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
  • 如果使用UNION ALL，不会合并重复的记录行
  • 效率 UNION 高于 UNION ALL

合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。

合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION 操作符返回两个查询的结果集的并集，去除重复记录。UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。 注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
#方式2
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

全连接（FULL JOIN）

  • MySQL不支持全连接
  • 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用
SELECT * FROM A LEFT JOIN B ON A.id=B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.id=B.id

表连接面试题

有2张表，1张R、1张S

R表有ABC三列，S表有CD两列，表中各有三条记录。

R表

img
img

S表

img
img
  1. 交叉连接(笛卡尔积):
select r.*,s.* from r,s
img
img

2.内连接结果：

select r.*,s.* from r inner join s on r.c=s.c
img
img

3.左连接结果：

select r.*,s.* from r left join s on r.c=s.c
img
img

4.右连接结果：

select r.*,s.* from r right join s on r.c=s.c
img
img

5.全表连接的结果（MySql不支持，Oracle支持）：

select r.*,s.* from r full join s on r.c=s.c
img
img
1.查询所有学生的学号、姓名、选课数、总成绩
select A.学号,A.姓名,count(B.课程号) as 选课数,sum(B.成绩) as 总成绩 
from student as A 
left join score as B 
on A.学号=B.学号 
group by B.学号;

2.查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select A.学号,A.姓名,avg(B.成绩) as 平均成绩

from student as A

left join score as B

on A.学号=B.学号

group by B.学号

having avg(B.成绩)>85;


3.查询学生的选课情况：学号，姓名，课程号，课程名称

select A.学号,A.姓名,B.课程号,C.课程名称

from student as A

left join score as B on A.学号=B.学号

inner join course as C on B.课程号=C.课程号;


4.查询出每门课程的及格人数和不及格人数

select 课程号,

sum(case when 成绩>=60 then 1 else 0 end) as 及格人数,

sum(case when 成绩<60 then 1 else 0 end) as 不及格人数

from score group by 课程号;


5.使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select A.课程号,A.课程名称,

sum(case when B.成绩 between 85 and 100 then 1 else 0 end) as [100-85],

sum(case when B.成绩<85 and B.成绩>=70 then 1 else 0 end) as [85-70],

sum(case when B.成绩<70 and B.成绩>=60 then 1 else 0 end) as [70-60],

sum(case when B.成绩<60 then 1 else 0 end) as [<60]

from course as A

left join score as B on A.课程号=B.课程号

group by A.课程号,A.课程名称;


6.查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

select 学号,姓名

from student as A

left join score as B on A.学号=B.学号

where 课程号='0003' and 成绩>80;

Join查询图

img
img

代码实现

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上图：左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
#左下图：满外连接
# 左中图 + 右上图  A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL  #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右下图
#左中图 + 右中图  A ∪B- A∩B 或者 (A -  A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

Join 示例

建表语句

CREATE TABLE t_dept (
    id INT(11) NOT NULL AUTO_INCREMENT,
    deptName VARCHAR(30) DEFAULT NULL,
    address VARCHAR(40) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARSET = utf8;

CREATE TABLE t_emp (

id INT(11) NOT NULL AUTO_INCREMENT,

name VARCHAR(20) DEFAULT NULL,

age INT(3) DEFAULT NULL,

deptId INT(11) DEFAULT NULL,

empno int NOT NULL,

PRIMARY KEY (id),

KEY idx_dept_id (deptId)

) ENGINE = INNODB AUTO_INCREMENT = 1 CHARSET = utf8;


INSERT INTO t_dept (deptName, address)

VALUES ('华山', '华山');


INSERT INTO t_dept (deptName, address)

VALUES ('丐帮', '洛阳');


INSERT INTO t_dept (deptName, address)

VALUES ('峨眉', '峨眉山');


INSERT INTO t_dept (deptName, address)

VALUES ('武当', '武当山');


INSERT INTO t_dept (deptName, address)

VALUES ('明教', '光明顶');


INSERT INTO t_dept (deptName, address)

VALUES ('少林', '少林寺');




INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('风清扬', 90, 1, 100001);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('岳不群', 50, 1, 100002);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('令狐冲', 24, 1, 100003);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('洪七公', 70, 2, 100004);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('乔峰', 35, 2, 100005);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('灭绝师太', 70, 3, 100006);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('周芷若', 20, 3, 100007);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('张三丰', 100, 4, 100008);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('张无忌', 25, 5, 100009);


INSERT INTO t_emp (NAME, age, deptId, empno)

VALUES ('韦小宝', 18, NULL, 100010);

案例

结合上面的查询图

表一：门派表
门派id，门派名，门派地址

表二：成员表

成员id，姓名，年龄，门派id，成员号

1.所有有门派人员的信息（要求显示门派名称）

SELECT e.name,d.deptName FROM t_emp e INNER JOIN t_dept d ON e.deptId=d.id;


    

  1. 

    列出所有人员及其门派信息
    
SELECT e.name,d.deptName FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id;
    

    2. 

  2. 

    列出所有门派
    
SELECT * FROM t_dept;
    

    3. 

  3. 

    所有无门派人士
    
SELECT * FROM t_emp WHERE deptId IS NULL;
    

    4. 

  4. 

    所有无人门派
    
SELECT d.* FROM t_dept d LEFT JOIN t_emp e ON d.id=e.deptId WHERE e.deptId IS NULL;
    

    5. 

  5. 

    所有人员和门派的对应关系  即
    
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id
    
UNION
    
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId=d.id;
    

    6. 

  6. 

    所有没有入门派的人员和没人入的门派
    
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId=d.id WHERE e.deptId IS NULL
    
UNION
    
SELECT * FROM t_dept d LEFT JOIN t_emp e ON d.id=e.deptId WHERE e.deptId IS NULL;
    

    7. 

  7. 

    添加 CEO 字段
    
ALTER TABLE t_dept add CEO INT(11) ;
    
update t_dept set CEO=2 where id=1;
    
update t_dept set CEO=4 where id=2;
    
update t_dept set CEO=6 where id=3;
    
update t_dept set CEO=8 where id=4;
    
update t_dept set CEO=9 where id=5;
    
8.1 求各个门派对应的掌门人名称
    
SELECT d.deptName,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id
    
8.2 求所有当上掌门人的平均年龄
    
SELECT AVG(e.age) FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id
    

    8. 



8.3 求所有人物对应的掌门名称

SELECT ed.name '人物',c.name '掌门' FROM (SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed LEFT JOIN t_emp c on ed.ceo= c.id;


SELECT e.name '人物',tmp.name '掌门' FROM t_emp e LEFT JOIN (SELECT d.id did,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id)tmp ON e.deptId=tmp.did;


SELECT e1.name '人物',e2.name '掌门' FROM t_emp e1 LEFT JOIN t_dept d on e1.deptid = d.id LEFT JOIN t_emp e2 on d.ceo = e2.id ;


SELECT e2.name '人物', (SELECT e1.name FROM t_emp e1 where e1.id= d.ceo) '掌门' from t_emp e2 LEFT JOIN t_dept d on e2.deptid=d.id;

其他知识点

区分重复的列名

  • 多个表中有相同列时，必须在列名之前加上表名前缀。
  • 在不同表中具有相同列名的列，可以用表名加以区分。
SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

表的别名

  • 使用别名可以简化查询。
  • 列名前使用表名前缀可以提高查询效率。
SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

阿里开发规范：

【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

posted @ 2026-03-31 16:11  青柠代码录  阅读(55)  评论(0)    收藏  举报