SQL的多表连接查询（难点）

多表连接查询（难点）

连接查询 join

·连接查询用于根据表之间的关联关系（通常是外键）从多个表中获取数据

·as：起别名 可以省略用空格代替

·若两表有匹配行：行数取决于匹配的数量，可能为“1对1”（内连接找交集，行数不变）、“1对多”（比如左连接左边一行对应右边多行时，行数增加），极端情况下（如无有效关联条件）可能为两表行数的乘积（笛卡尔积）。

常见的连接类型包括：

1.内连接：[inner] join(查找显示两个表中相同字段的内容)

查询多张表（自连接一张表）中相关联的内容。

只返回多个表中匹配条件的记录，是最常用的连接方式。

只保留两表中同时满足关联条件的行

select 字段名

from 总表

inner join 表2 on 表1.某个相同字段=表2.某个相同字段

[inner join 表3 on 表3.某个相同字段=表1.某个相同字段

……];

只有在两个表中相同字段匹配的行才会出现在结果集中。

# 多表联查，通过inner join关联三个表：orders，users，products

# 关联条件是：

订单的用户 ID (o.user_id) 匹配用户表的 ID (u.id)，

订单的商品 ID (o.product_id) 匹配商品表的 ID (p.id)

# 查询所有订单的用户和商品信息

select

o.id as 订单id,

u.username as 用户名,

p.name as 商品名称,

o.total_amount as 总金额

from orders o

inner join users u on o.user_id = u.id

inner join products p on o.product_id = p.id;

# 把所有订单的用户和商品信息全部排列组合一遍

# 同样关联三个表，查询字段更多

select

o.id as 订单id,

u.username as 用户名,

p.name as 商品名称,

o.quantity as 购买数量,

o.total_amount as 总金额,

o.create_time as 下单时间

from orders o

inner join:user u on o.users_id = u.id

inner join products p on o.product_id = p.id

where o.status = '已完成'

order by o.create_time desc;

# 笛卡尔查询（交叉连接cross join）:将所有表中的所有内容都排列组合一次，不加关联条件时会产生。

·后面没写on+关联条件，产生 “笛卡尔积”（行数 = 表 A 行数 × 表 B 行数）

select

o.id as 订单id,

u.username as 用户名,

p.name as 商品名称,

o.quantity as 购买数量,

o.total_amount as 总金额,

o.create_time as 下单时间

from orders o

inner join users u

inner join products p;

2.左（外）连接：left join/left outer join

·返回左表中所有数据和右侧表中匹配条件的记录，右侧表无匹配则显示NULL

·以左侧的表为基准，获取所有数据，右表显示匹配到的数据，匹配不到的显示null（意思就是说，左连接会返回左表中的所有行，以及右表中与左表满足连接条件的行。如果右表中没有匹配的行，则对应列的值为NULL。）

select * from 主表 left join 从表 on 表1.字段 = 表2.字段;

# 查询所有用户及其订单情况（包括没有订单的用户）

select

    u.username as 用户名,

    count(o.id) as 订单数,

    ifnull(sum(o.total_amount), 0) as 总消费

from users u  

left join orders o on u.id = o.user_id

group by u.id, u.username;

3.右（外）连接：right join/right outer join

将右侧侧表中所有数据和左侧表中相关联数据查询出来，左侧表无关联则显示NULL

select * from 主表 right join 从表 on 表1.字段 = 表2.字段;

# 查询所有商品及其被购买情况（包括未被购买的商品）

select

p.name as 商品名称,

ifnull(sum(o.quantity),0) as 销售总量

from orders o

right join products p on o.product_id = p.id

group by p.id, p.name;

4.自连接：self join

将表与自身进行连接，通常用于处理表中存在层级关系

# 查询和”张三”同城市的用户

select u2.username, u2.city

from users u1

join users u2 on u1.city = u2.city

where u1.username = ‘张三’ and u2.username !=’张三’;

联合查询union

合并时，两个表对应的列数和数据类型必须相同或者兼容。最终结果集的列数等于单个查询的列数，结果集的行数是多个查询结果的行数总和（union是去重后的总和，union all是不去重的总和）

1.union

合并结果集并去除重复的行

select 字段1, 字段2, ……

from 表1

where 条件

union

select 字段1, 字段2, ……

from 表2

where 条件;

2.union all

合并结果集但保留重复记录（效率比union高）

select 字段1, 字段2, ……

from 表1

where 条件

union all

select 字段1, 字段2, ……

from 表2

where 条件;

连接查询与联合查询的区别