union+联结join

遨游的laugh哥

已于 2022-10-26 22:42:28 修改

阅读量1.7k

点赞数 1

文章标签： mysql 数据库

于 2022-10-25 22:29:01 首次发布

本文链接：https://blog.csdn.net/weixin_47173597/article/details/127520307

版权

MYSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

集合运算符

在标准 SQL 中, 分别对检索结果使用UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

1.组合查询

union:组合多条sql查询，将它们的结果组合成单个结果集

select product_name
from product
where product_type='厨房用具'
union#注意union
select product_name
from product
where sale_price>=1000;

也可以是使用or

select product_name
from product
where product_type='厨房用具'
      or sale_price>=1000;

倘若要将两个不同的表中的结果合并在一起只能使用 UNION 了
使用 UNION的查询效率更高

union连接的查询必须包含相同列，表达式或聚集函数
union自动去重

包含重复行的集合运算 UNION ALL
在这里插入图片描述

union与order by

只能在union连接的最后一个select查询后加上order by

–排序的是所有结果

select product_name,product_type,sale_price
from product
where product_type='衣服'
union all
select product_name,product_type,sale_price
from product
where sale_price in(1000,500,100,880)
order by sale_price,product_name;

注意mysql不支持intersect的交操作

需要用 inner join 来求得交集

MySQL 8.0 还不支持表的减法运算符 EXCEPT.不过, 借助第六章学过的NOT IN 谓词, 我们同样可以实现表的减法。

2.联结

上述集合运算符都是针对行的操作
虽然使用函数或者CASE表达式等列运算, 可以增加列的数量, 但仍然只能从一张表中提供的基础信息列中获得一些"引申列"

SQL最强大的功能之一就是：在查询中执行join去联结表

连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算.

where可以联结，实现添加列效果

select product_name,shop_name
from product,shopproduct
where product.product_id=shopproduct.product_id;

不使用where会按照笛卡尔积形式，将相同列进行联结
在这里插入图片描述

内部联结

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

ex:

select product_name,shop_name
from product inner join shopproduct
on product.product_id=shopproduct.product_id;

在这里插入图片描述

select product_name,shop_name
from product ,shopproduct
where product.product_id=shopproduct.product_id;

这两组功能相同

SELECT  P.product_name,P.product_type,SP.shop_name
from shopproduct as SP
inner join product as P
on SP.product_id=P.product_id;

关于内连结,需要注意以下三点:

要点一: 进行连结时需要在 FROM 子句中使用多张表.
要点二:必须使用 ON 子句来指定连结条件.
要点三: SELECT 子句中的列最好按照 表名.列名的格式来使用。

结合 WHERE 子句使用内连结

在这里插入图片描述
可以改写

在结合 WHERE 子句使用内连结的时候, 我们也可以更改任务顺序, 并采用任务分解的方法,先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来。

SELECT  P.product_name,P.product_type,SP.shop_name
from (select *
      from shopproduct
      where shop_name='名古屋')as SP
inner join
      (select *
       from product
       where product_type='厨房用具')as P
on SP.product_id=P.product_id

每个商店中, 售价最高的商品的售价分别是多少?
-- 参考答案
SELECT SP.shop_id
     ,SP.shop_name
     ,MAX(P.sale_price) AS max_price
 FROM shopproduct AS SP
INNER JOIN product AS P
   ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name;
最简单的情形, 是在内连结之前就使用 GROUP BY 子句.
但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。

自联结

select p2.product_name,p1.product_type
from   product as p1,product as p2
where  p1.product_id=p2.product_id
and    p2.product_type='厨房用具';

找出每个商品种类当中售价高于该类商品的平均售价的商品
在这里插入图片描述
内联结解决

首先, 使用 GROUP BY 按商品类别分类计算每类商品的平均价格。
上述查询与表 product 按照 product_type (商品种类)进行内连结
增加 WHERE 子句, 找出那些售价高于该类商品平均价格的商品

最后where

select aaa.product_id,aaa.product_name,
       aaa.sale_price,bbb.avg_sale
from product as aaa
INNER JOIN
(select product_type,avg(sale_price)as avg_sale
            from product
            group by product_type)AS bbb
ON aaa.product_type=bbb.product_type
where aaa.sale_price>bbb.avg_sale;

自然联结

自然连结并不是区别于内连结和外连结的第三种连结
它其实是内连结的一种特例
当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。

SELECT *  FROM shopproduct NATURAL JOIN product

select product_name,shop_name
from product
natural join shopproduct;

与上述自然联结等价的内联结

select aaa.product_name,bbb.shop_name
from product as aaa
inner join shopproduct as bbb
where aaa.product_id=bbb.product_id;

只能从不同表中选择唯一列，这样不同表中相同列自动做联结

利用联结做交集
在这里插入图片描述

外联结

内连结会丢弃两张表中不满足 ON 条件的行
和内连结相对的就是外连结
外连结会根据外连结的种类有选择地保留无法匹配到的行。

按照保留的行位于哪张表,
外连结有三种形式: 左连结, 右连结和全外连结。

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别

select SP.shop_id,
       SP.shop_name,
       SP.product_id,
       P.product_name,
       P.sale_price
from product as P
left OUTER JOIN  shopproduct as SP#关键词left OUTER JOIN
             ON  SP.product_id=P.product_id;

在这里插入图片描述

观察上述结果可以发现, 有两种商品: 高压锅和圆珠笔, 在所有商店都没有销售.

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值

外连结要点

选取出单张表中全部的信息
与内连结的结果相比,不同点显而易见,那就是结果的行数不一样.
内连结只能选取出同时存在于两张表中的数据
外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在的信息

多出的 2 条记录是高压锅和圆珠笔,这 2 条记录在 shopproduct 表中并不存在
对于外连结来说,只要数据存在于某一张表当中,就能够读取出来

使用 LEFT、RIGHT 来指定主表
最终的结果中会包含主表内所有的数据.
指定主表的关键字是 LEFT 和 RIGHT.

用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表

select SP.shop_id,
       SP.shop_name,
       SP.product_id,
       P.product_name,
       P.sale_price
from product as P
right OUTER JOIN  shopproduct as SP#关键词left OUTER JOIN
             ON  SP.product_id=P.product_id;

在这里插入图片描述

通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT)

select SP.shop_id,
       SP.shop_name,
       SP.product_id,
       P.product_name,
       P.sale_price
from shopproduct as SP
right OUTER JOIN  product as P#关键词left OUTER JOIN
             ON  SP.product_id=P.product_id;

等价于

select SP.shop_id,
       SP.shop_name,
       SP.product_id,
       P.product_name,
       P.sale_price
from product as P
left OUTER JOIN  shopproduct as SP#关键词left OUTER JOIN
             ON  SP.product_id=P.product_id;

SQL查询的执行顺序(FROM->WHERE->SELECT)

使用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店.

先写个子查询,用来从shopproduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来。

select P.product_id,
       P.product_name,
       P.sale_price,
       SP.shop_id,
       SP.shop_name,
       SP.quantity
from product as P
Left OUTER JOIN(SELECT *
                FROM shopproduct
                WHERE quantity<50
               )as SP
ON  SP.product_id=P.product_id;

全外联结

全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充。
遗憾的是, MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行UNION来实现全外连结。

多表连结

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 INNER JOIN Inventoryproduct AS IP
    ON SP.product_id = IP.product_id
 WHERE IP.inventory_id = 'P001';

在这里插入图片描述

多表进行外连结

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,IP.inventory_quantity
  FROM product AS P
  LEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id;