JAVA基础拓展->MySQL关联查询（联合查询）

jws0703

昨天看见有个朋友，问关联查询问题，由于刚刚注册只能现在发！
第一次发帖，有不好的地方，请大佬指教，谢谢！

关联查询（联合查询）

什么是关联查询

关联查询：两个或更多个表一起查询。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

关联查询结果分为几种情况

关联查询的SQL有几种情况

1、内连接：inner join  ... on

结果：A表 ∩ B表

2、左连接：A left join B on

（2）A表全部

（3）A表- A∩B

3、右连接：A right join B on

（4）B表全部

（5）B表-A∩B

4、==全外连接：full outer join ... on，但是mysql不支持这个关键字，mysql使用union（合并）结果的方式代替==

（6）A表∪B表：    （2） A表结果  union （4）B表的结果

（7）A∪B - A∩B     （3）A表- A∩B结果 union （5）B表-A∩B结果

1、内连接

内连接：实现 A∩B

关键字：A inner join B on 关联条件

格式：

select 字段列表
from A表 inner join B表
on A表.关联字段 = B表.关联字段
【其他子句】

举例：

#查询所有员工的姓名和部门编号，部门名称
/*
分析：需要哪些表？t_employee（员工表） 和 t_department（部门表）
关联字段：t_employee.did 和 t_department.did
*/
SELECT ename,t_employee.did,dname
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;
/*
内连接还有一种写法：
注意：不推荐因为基于笛卡尔积，笛卡尔积会增长查询时间
*/
SELECT ename,t_employee.did,dname
FROM t_employee , t_department
WHERE t_employee.did = t_department.did;
#容易犯的错误
SELECT ename,t_employee.did,dname
FROM t_employee , t_department;
#这个现象叫做笛卡尔积 A表记录 * B表记录
#########################################################
#查询员工姓名，员工部门编号，部门名称，职位编号，职位名称
/*
分析：需要哪些表？
t_employee（员工表）、t_department（部门表）、t_job（职位表）
关联字段：
    t_employee.did 和 t_department.did
    t_employee.job_id 和 t_job.jid
*/
SELECT ename AS 员工姓名,
    t_department.did AS 部门编号,
    dname AS 部门名称,
    t_job.jid AS 职位编号,
    jname AS 职位名称
FROM    t_employee INNER JOIN t_department 
    ON  t_employee.did = t_department.did
    INNER JOIN t_job
    ON t_employee.job_id = t_job.jid;

SELECT ename AS 员工姓名,
    t_department.did AS 部门编号,
    dname AS 部门名称,
    t_job.jid AS 职位编号,
    jname AS 职位名称
FROM    t_employee,t_department,t_job
WHERE t_employee.did = t_department.did 
    AND t_employee.job_id = t_job.jid;

2、左连接

左连接实现：A 或 A - A ∩ B

关键字：A表 left join B表 on 关联条件

#查询所有员工，包括没有分配部门的员工
#的姓名、部门编号、部门名称
SELECT ename,t_employee.did,dname
FROM t_employee LEFT JOIN t_department
  ON t_employee.did = t_department.did;

#查询那些没有分配部门的员工
#显示结果姓名、部门编号、部门名称
SELECT ename,t_employee.did,dname
FROM t_employee LEFT JOIN t_department
  ON t_employee.did = t_department.did
WHERE  t_employee.did IS NULL; 

3、右连接

右连接：A 或 A - A ∩ B

关键字：A表 right join B表 on 关联条件

#查询所有员工，包括没有分配部门的员工
#的姓名、部门编号、部门名称
SELECT ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
  ON t_employee.did = t_department.did;

#查询那些没有分配部门的员工
#显示结果姓名、部门编号、部门名称
SELECT ename,t_employee.did,dname
FROM t_department RIGHT JOIN t_employee
  ON t_employee.did = t_department.did
WHERE  t_employee.did IS NULL; 

Ps：左右可以调换位置

4、全连接（union）

关键字：SQL ServerA表 full join B表 on 关联条件，mysql不支持这种写法；

mysql要实现A∪B 或 A∪B - A∩B 查询结果也是可以的，使用关键字：==union（合并去重）= union ALL（仅合并）==：

（1）A∪B格式：

select 字段列表
from A表 left join B表
on A表.关联字段 = B表.关联字段

union

select 字段列表
from A表 right join B表
on A表.关联字段 = B表.关联字段;

注意：

A表和B表有主从关系。
where 从表.关联字段 is null;
员工表是从表，部门表是主表。
员工的部门编号一定从部门表的部门编号列表中筛选。

#查询所有员工的姓名、部门编号、部门名称，以及所有的部门编号、部门名称
SELECT ename,t_department.did,dname
FROM t_employee LEFT JOIN t_department
  ON t_employee.did = t_department.did

UNION

SELECT ename,t_department.did,dname
FROM t_employee RIGHT JOIN t_department
  ON t_employee.did = t_department.did;

 #查询所有没有分配部门的员工，和没有分配员工的部门
 SELECT ename,t_department.did,dname
FROM t_employee LEFT JOIN t_department
  ON t_employee.did = t_department.did
WHERE   t_employee.did IS NULL

UNION

SELECT ename,t_department.did,dname
FROM t_employee RIGHT JOIN t_department
  ON t_employee.did = t_department.did
WHERE   t_employee.did IS NULL;  

联合查询字段列表问题

当两个表同有重名字段时，不处理会报错：

错误代码： 1052
Column 'did' in field list is ambiguous（模糊不清的；引起歧义的）

需要在重名字段前标名是那个表的：

表名 . 字段 ==》进行区分，这个时候通常使用从表的字段；

#查询字段的问题
#查询每一个员工及其所在部门的信息
#要求：显示员工的编号，姓名，部门编号，部门名称
SELECT eid,ename,did,dname
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;
/*
错误代码： 1052
Column 'did' in field list is ambiguous（模糊不清的；引起歧义的）
*/

SELECT eid,ename,t_employee.did,dname
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;

#查询每一个员工及其所在部门的信息
#要求，显示员工的编号，姓名，部门表的所有字段
SELECT eid,ename,t_department.*
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;

自链接

参与联合查询的表其实是一张表。只是把一张表当成两张表。

举例：

#查询每一个员工的编号、姓名以及他的领导的编号、领导的姓名
/*
分析：
    参与查询的表只有1张 t_employee
现在要把t_employee当成两张表：
A：普通的员工信息表
B：领导的信息表
实现的方式：给一张表取两个别名
A：普通的员工信息表 别名 emp
B：领导的信息表 别名 mgr
关联条件：
普通的员工信息表.mid =  领导的信息表.eid
*/
SELECT emp.eid,emp.ename,mgr.eid,mgr.ename
FROM t_employee AS emp INNER JOIN t_employee AS mgr
ON emp.mid = mgr.eid;

SELECT emp.eid,emp.ename,mgr.eid,mgr.ename
FROM t_employee AS emp LEFT JOIN t_employee AS mgr
ON emp.mid = mgr.eid;

jws0703

ygiveupc 发表于 2023-3-14 11:41
不错，不错，Java板块太少东西了

是的  java学的人最多，但在吾爱不多，一起卷起来！

jws0703

jojogao 发表于 2023-3-15 20:11
这些内外连接查询比直接用id关联查询有什么优势吗

1、关联查询就是内连接：inner join  ... on、左连接：A left join B on、右连接：A right join B on、全链接 full outer join ... on（但是mysql不支持这个关键字，mysql使用union（合并）结果的方式代替）；
2、我理解的你说的id关联查询可能是这个：

[SQL] 纯文本查看 复制代码

SELECT * FROM sku_info,sku_sale_attr_value
WHERE sku_info.id  = 30 AND sku_sale_attr_value.sku_id = 30

这段代码是基于sku_info与sku_sale_attr_value两表，先进行笛卡尔积基础上进行where条件处理，因此效率较低；

ygiveupc

不错，不错，Java板块太少东西了

llh0101

谢谢分享

skyua

感谢楼主分享

hgj8836

学些了，感谢分享

biog

谢谢分享

Castle7890

不错的分享，数据处理提升很重要

Penguindazhou

这么久终于看见一个java的了

yuelang

学习到了，谢谢分享

Yingz520

谢谢楼主分享，最近刚好正在学MySQL，很有帮助。