4.1 表的加减法
4.1.1 什么是集合运算
`集合`在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。
在标准 SQL 中, 分别对检索结果使用 `UNION`, `INTERSECT,` `EXCEPT` 来将检索结果进行并,交和差运算, 像`UNION`,`INTERSECT`, `EXCEPT`这种用来进行集合运算的运算符称为集合运算符。
以下的文氏图展示了几种集合的基本运算。
在数据库中, 所有的表--以及查询结果--都可以视为集合, 因此也可以把表视为集合进行上述集合运算, 在很多时候, 这种抽象非常有助于对复杂查询问题给出一个可行的思路。
4.1.2 表的加法--UNION
4.1.2.1 UNION--并集运算
对不同的两张表:
SELECT product_id, product_name
FROM product
UNION
SELECT product_id, product_name
FROM product2;
上述结果包含了两张表中的全部商品,即并集运算。
*UNION 等集合运算符通常都会除去重复的记录。
对于同一张表:
假设连锁店想要增加成本利润率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集。
SELECT product_id,product_name,product_type
,sale_price,purchase_price
FROM PRODUCT
WHERE sale_price<800
UNION
SELECT product_id,product_name,product_type
,sale_price,purchase_price
FROM PRODUCT
WHERE sale_price>1.5*purchase_price;
*如果不使用 UNION 该怎么写查询语句?
SELECT product_id,product_name,product_type
,sale_price,purchase_price
FROM PRODUCT
WHERE sale_price < 800
OR sale_price > 1.5 * purchase_price;
4.1.2.2 UNION 与 OR 谓词
对于同一个表的结果合在一起,使用 UNION或 OR 谓词连接, 会得到相同的结果(UNION效率较高), 但若要将两个不同的表中的结果合并在一起, 就只能用 UNION 了.。
例如,分别使用 UNION 或者 OR 谓词,找出成本利润率不足 30%或成本利润率未知的商品。
-- 使用 OR 谓词
SELECT *
FROM Product
WHERE sale_price / purchase_price < 1.3
OR sale_price / purchase_price IS NULL;
-- 使用 UNION
SELECT *
FROM Product
WHERE sale_price / purchase_price < 1.3
UNION
SELECT *
FROM Product
WHERE sale_price / purchase_price IS NULL;
4.1.2.3 包含重复行的集合运算 UNION ALL
SQL 语句的 UNION 会对查询的结果集进行合并和去重, 但若想在 UNION 的结果中保留重复行,就需要在 UNION 后面添加 ALL 关键字。
例如, 想要知道 product 和 product2 中所包含的商品种类及每个种类里每种商品的数量
-- 保留重复行
SELECT product_type
FROM Product
UNION ALL
SELECT product_type
FROM Product2;
查询结果如下:
4.1.2.4 隐式数据类型转换
通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:
SELECT product_id, product_name, '1'
FROM Product
UNION
SELECT product_id, product_name,sale_price
FROM Product2;
查询如下结果:
*注意:hive中进行join关联时,关联列要避免使用隐式数据类型转换,否则容易导致数据倾斜
例如,使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性。(若以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容。)
SELECT SYSDATE(), SYSDATE(), SYSDATE()
UNION
SELECT 'chars', 123, null
查询结果:
4.1.3交运算INTERSECT,inner join
集合的交, 就是两个集合的公共部分, 遗憾的是, 截止到 MySQL 8.0 版本, MySQL 仍然不支持 INTERSECT 操作。
如果:
SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
则会报错,此时需要用 inner join 来求得交集
SELECT p1.product_id, p1.product_name
FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id
4.1.4 差集,补集与表的减法
差集:集合A和B做减法,即将集合A中也同时属于集合B的元素减掉。
4.1.4.1 EXCEPT 运算--差集
MySQL 8.0 还不支持表的减法运算符 EXCEPT. 此时需要用NOT IN 谓词实现表的减法。
例如,找出只存在于Product表但不存在于Product2表的商品。
-- 使用 NOT IN 子句的实现方法
SELECT *
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM Product2)
4.1.4.2 EXCEPT 与 NOT 谓词
使用 NOT IN 谓词和EXCEPT运算效果大致相同。
例如,使用NOT谓词进行集合的减法运算, 求出 Product 表中, 售价高于2000、成本利润率不低于 30% 的商品。
SELECT *
FROM Product
WHERE sale_price > 2000
AND product_id NOT IN (SELECT product_id
FROM Product
WHERE sale_price < 1.3*purchase_price)
4.1.4.3 INTERSECT 与 AND 谓词
对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现。
如上题:
SELECT *
FROM Product
WHERE sale_price > 2000
AND sale_price > 1.3*purchase_price
4.1.5 对称差
两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合.
求对称差:
使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集。
但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此要使用差集运算,两个集合的对称差等于 A-B并上B-A。
例如,使用Product表和Product2表的对称差来查询哪些商品只在其中一张表。
-- 使用 NOT IN 实现两个表的差集
SELECT *
FROM Product
WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT *
FROM Product2
WHERE product_id NOT IN (SELECT product_id FROM Product)
*借助并集和差集迂回实现交集运算 INTERSECT:两个集合的交可以看作是两个集合的并去掉两个集合的对称差。
4.2 连结(JOIN)
UNION和INTERSECT 等集合运算的特征就是以行方向为单位进行操作:使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数。但这些运算不能改变列的变化, 虽然使用函数或者 CASE表达式等列运算, 可以增加列的数量, 但仍然只能从一张表中提供的基础信息列中获得一些"引申列", 本质上并不能提供更多的信息. 如果想要从多个表获取信息,就需要使用连结了。
连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算.连结能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询。
4.2.1 内连结(INNER JOIN)
语法:
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
其中 INNER 关键词表示使用了内连结。
例如,找出东京商店里的衣服类商品的商品名称,商品价格,商品种类,商品数量信息。
4.2.1.1 使用内连结从两个表获取信息
我们先来分别观察所涉及的表,
所以问题的关键是, 找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来。这就是连结运算所要作的事情。
对比上述两张表, 可以发现,商品编号列是一个公共列, 因此很自然的事情就是用这个商品编号列来作为连接的“桥梁”,将Product和ShopProduct这两张表连接起来。我们把上述问题进行分解:首先, 找出每个商店的商店编号, 商店名称, 商品编号, 商品名称, 商品类别, 商品售价,商品数量信息。按照内连结的语法, 在 FROM 子句中使用 INNER JOIN 将两张表连接起来, 并为 ON 子句指定连结条件为ShopProduct.product_id= Product.product_id, 就得到了如下的查询语句:
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id;
在上述查询中, 我们分别为两张表指定了简单的别名,让查询语句看起来更加简洁。
查询结果如下:
关于内连结,需要注意以下三点:
*一: 进行连结时需要在 FROM 子句中使用多张表.
*二:必须使用 ON 子句来指定连结条件
*三: SELECT 子句中的列最好按照“表名.列名”的格式来使用
4.2.1.2 结合 WHERE 子句使用内连结
如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。
例如, 对于上述查询问题, 我们要在前一步查询的基础上, 增加 WHERE 条件。
方法一:把上述查询作为子查询, 用括号封装起来, 然后在外层查询增加筛选条件。
SELECT *
FROM (-- 第一步查询的结果
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id) AS STEP1
WHERE shop_name = '东京'
AND product_type = '衣服' ;
子查询实质是数据库中其他表经过筛选,聚合等查询操作后得到的一个"视图"。
方法二:两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 最后, SELECT 子句选出了那些我们需要的列。
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
WHERE SP.shop_name = '东京'
AND P.product_type = '衣服' ;
上述查询的执行顺序 : FROM 子句->WHERE 子句->SELECT 子句
方法三:将 WHERE 子句中的条件直接添加在 ON 子句中, 这时候 ON 子句后最好用括号将连结条件和筛选条件括起来。(不是太容易阅读, 不建议使用。)
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON (SP.product_id = P.product_id
AND SP.shop_name = '东京'
AND P.product_type = '衣服') ;
方法四:先分别在两个表使用 WHERE 进行筛选,然后把上述两个子查询连结起来。
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.product_type
,P.sale_price
,SP.quantity
FROM (-- 子查询 1:从 ShopProduct 表筛选出东京商店的信息
SELECT *
FROM ShopProduct
WHERE shop_name = '东京' ) AS SP
INNER JOIN -- 子查询 2:从 Product 表筛选出衣服类商品的信息
(SELECT *
FROM Product
WHERE product_type = '衣服') AS P
ON SP.product_id = P.product_id;
*练习题:
1、找出每个商店里的衣服类商品的名称及价格等信息. 希望得到如下结果:
--方案 1--不使用子查询
SELECT SP.shop_id,SP.shop_name,SP.product_id
,P.product_name, P.product_type, P.purchase_price
FROM shopproduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
WHERE P.product_type = '衣服';
-- 方案 2--使用子查询
SELECT SP.shop_id, SP.shop_name, SP.product_id
,P.product_name, P.product_type, P.purchase_price
FROM shopproduct AS SP
INNER JOIN --从 Product 表找出衣服类商品的信息
(SELECT product_id, product_name, product_type, purchase_price
FROM Product
WHERE product_type = '衣服')AS P
ON SP.product_id = P.product_id;
2、找出东京商店里, 售价低于 2000 的商品信息,希望得到如下结果。
SELECT SP.*, P.*
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
WHERE shop_id = '000A'
AND sale_price < 2000;
4.2.1.3 结合 GROUP BY 子句使用内连结
结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待:最简单的情形, 是在内连结之前就使用 GROUP BY 子句. 但如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。
*练习题:
每个商店中, 售价最高的商品的售价分别是多少?
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
4.2.1.4 自连结(SELF JOIN)
一张表也可以与自身作连结, 这种连接称之为自连结.自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。
4.2.1.5 内连结与关联子查询
例如,找出每个商品种类当中售价高于该类商品的平均售价的商品.
使用关联子查询来实现:
SELECT product_type, product_name, sale_price
FROM product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
FROM product AS P2
WHERE P1.product_type = P2.product_type
GROUP BY product_type);
使用内连结实现:
首先, 使用 GROUP BY 按商品类别分类计算每类商品的平均价格。
SELECT product_type
,AVG(sale_price) AS avg_price
FROM Product
GROUP BY product_type;
接下来, 将上述查询与表 Product 按照 product_type (商品种类)进行内连结。
SELECT P1.product_id
,P1.product_name
,P1.product_type
,P1.sale_price
,P2.avg_price
FROM Product AS P1
INNER JOIN
(SELECT product_type,AVG(sale_price) AS avg_price
FROM Product
GROUP BY product_type) AS P2
ON P1.product_type = P2.product_type;
最后, 增加 WHERE 子句, 找出那些售价高于该类商品平均价格的商品.完整的代码如下:
WHERE P1.sale_price > P2.avg_price;
4.2.1.6 自然连结(NATURAL JOIN)
自然连结是内连结的一种特例--当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。
SELECT * FROM shopproduct NATURAL JOIN Product
上述查询得到的结果, 会把两个表的公共列放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来。
与上述自然连结等价的内连结:
SELECT SP.product_id,SP.shop_id,SP.shop_name,SP.quantity
,P.product_name,P.product_type,P.sale_price
,P.purchase_price,P.regist_date
FROM shopproduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id
使用自然连结还可以求出两张表或子查询的公共部分。
例如:求表 Product 和表 Product2 中的公共部分
SELECT * FROM Product NATURAL JOIN Product2
但是,结果少了regist_date 字段为空的运动 T 恤。因为ISNULL,IS NOT NULL两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行。因此,要将查询语句进行修改:
SELECT *
FROM (SELECT product_id, product_name
FROM Product ) AS A
NATURAL JOIN
(SELECT product_id, product_name
FROM Product2) AS B;
4.2.1.7 使用连结求交集
例如: 使用内连结求 Product 表和 Product2 表的交集。
SELECT P1.*
FROM Product AS P1
INNER JOIN Product2 AS P2
ON (P1.product_id = P2.product_id
AND P1.product_name = P2.product_name
AND P1.product_type = P2.product_type
AND P1.sale_price = P2.sale_price
AND P1.regist_date = P2.regist_date)
得到如下结果
但结果少了 product_id='0003'这一行, 观察源表数据可发现, 少的这行数据的 regist_date 为缺失值,同理,修改如下:
SELECT P1.*
FROM Product AS P1
INNER JOIN Product2 AS P2
ON P1.product_id = P2.product_id
查询结果:
4.2.2 外连结(OUTER JOIN)
内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对,外连结会根据外连结的种类有选择地保留无法匹配到的行。
按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。
左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 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)>
4.2.2.1 左连结与右连结
由于连结时可以交换左表和右表的位置, 因此左连结和右连结并没有本质区别.我们先以左连结为例,所有的内容在调换两个表的前后位置, 并将左连结改为右连结之后, 都能得到相同的结果。
4.2.2.2 使用左连结从两个表获取信息
例如: 统计每种商品分别在哪些商店有售, 需要包括那些在每个商店都没货的商品。
使用左连结的代码如下:
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
ON SP.product_id = P.product_id;
上述查询得到的检索结果如下(由于未使用 ORDER BY 子句指定顺序,故顺序不唯一):
观察上述结果, 有两种商品: 高压锅和圆珠笔, 在所有商店都没有销售
*外连结要点 1: 选取出单张表中全部的信息
内连结只能选取出同时存在于两张表中的数据,因此只在 Product 表中存在的 2 种商品并没有出现在结果之中.相反,对于外连结来说,只要数据存在于某一张表当中,就能够读取出来.在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结。外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在(在原表之外)的信息”.相反,只包含表内信息的连结也就被称为内连结了。
*外连结要点 2:使用 LEFT、RIGHT 来指定主表.
外连结的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表。
区别:
1.含义不同
左连接:只要左边表中有记录,数据就能检索出来,而右边有的记录必需在左边表中有的记录才能被检索出来。
2.运行空值不同
左连接是已左边表中的数据为基准,若左表有数据右表没有数据,则显示左表中的数据右表中的数据显示为空。右联接是将返回右表的所有行,如果右表的某行在左表中没有匹配行,则将为左表返回空值。
通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两种方式会到完全相同的结果。
4.2.2.3 结合 WHERE 子句使用左连结
例如,使用外连结从ShopProduct表和Product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果。
注意高压锅和圆珠笔两种商品在所有商店都无货, 所以也应该包括在内。
使用内连结加上WHERE quantity< 50和 `OR quantity IS NULL` 的条件, 便可以得到预期的结果。然而在真实的查询环境中, 由于数据量大且数据质量并非设想的那样"干净", 我们并不能容易地意识到缺失值等问题数据的存在, 因此,还需另想方法使得它能够适应更复杂的真实数据的情形:
SQL查询的执行顺序(FROM->WHERE->SELECT),所以问题可能出在筛选条件上, 因为在进行完外连结后才会执行WHERE子句, 因此那些主表中无法被匹配到的行就被WHERE条件筛选掉了。所以可以试着把WHERE子句挪到外连结之前进行: 先写个子查询,用来从ShopProduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来。
我们把上述思路写成SQL查询语句:
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-- 先筛选quantity<50的商品
(SELECT *
FROM ShopProduct
WHERE quantity < 50 ) AS SP
ON SP.product_id = P.product_id
4.2.2.4 在 MySQL 中实现全外连结
全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充。
遗憾的是, MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。
4.2.3 多表连结
4.2.3.1 多表进行内连结
先创建一个用于三表连结的表 InventoryProduct:首先我们创建一张用来管理库存商品的表, 假设商品都保存在 P001 和 P002 这 2 个仓库之中。
建表语句如下:
CREATE TABLE InventoryProduct
( inventory_id CHAR(4) NOT NULL,
product_id CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));
然后插入一些数据:
--- DML:插入数据
START TRANSACTION;
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0001', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0002', 120);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0003', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0004', 3);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0005', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0006', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0007', 999);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0008', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0001', 10);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0002', 25);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0003', 34);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0004', 19);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0005', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0006', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0007', 0 );
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0008', 18);
COMMIT;
接下来, 我们根据上表及 ShopProduct 表和 Product 表, 使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少。
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';
直接往后连,即使想要把连结的表增加到 4 张、5 张……使用 INNER JOIN 进行添加的方式也是完全相同的。
4.2.3.2 多表进行外连结
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
查询结果
4.2.4 ON 子句进阶--非等值连结
除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。
4.2.4.1 非等值自左连结(SELF JOIN)
使用非等值自左连结实现排名。
例如:希望对 Product 表中的商品按照售价赋予排名.
一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 如, 对于价格最高的商品,
SELECT product_id
,product_name
,sale_price
,COUNT(p2_id) AS my_rank
FROM (--使用自左连结对每种商品找出价格不低于它的商品
SELECT P1.product_id
,P1.product_name
,P1.sale_price
,P2.product_id AS P2_id
,P2.product_name AS P2_name
,P2.sale_price AS P2_price
FROM Product AS P1
LEFT OUTER JOIN Product AS P2
ON P1.sale_price <= P2.sale_price
) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY my_rank;
注 1: COUNT 函数的参数是列名时, 会忽略该列中的缺失值, 参数为 * 时则不忽略缺失值。
注 2: 上述排名方案存在一些问题--如果两个商品的价格相等, 则会导致两个商品的排名错误。
注 3: 实际上, 进行排名有专门的函数, 这是 MySQL 8.0 新增加的窗口函数中的一种(窗口函数将在下一章学习), 但在较低版本的 MySQL 中只能使用上述自左连结的思路。
练习题
4.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT * FROM product WHERE sale_price > 500
UNION ALL
SELECT * FROM product2 WHERE sale_price > 500;
4.2
借助对称差的实现方式, 求product和product2的交集。
-- 两个集合的交集就是两个集合的并集减去两个集合的对称差
-- 先求并集
SELECT *
FROM
(SELECT * FROM product
UNION
SELECT * FROM product2) AS T
-- 减去对称差
WHERE product_id NOT IN
(SELECT product_id
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT product_id
FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product));
4.3
每类商品中售价最高的商品都在哪些商店有售 ?
-- 取出想要的字段
SELECT sp.shop_id, sp.shop_name, sp.product_id ,p.product_type
FROM shopproduct sp
JOIN product p
ON sp.product_id=p.product_id
WHERE sp.product_id in
-- 过滤每个类型售价最高的商品
(SELECT product_id
FROM product p1
JOIN (SELECT product_type,
MAX(sale_price) as max_price
FROM product
GROUP BY product_type) as p2
ON p1.product_type=p2.product_type
AND p1.sale_price=p2.max_price);
4.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。
--方法1:关联子查询
SELECT product_type, product_name, sale_price
FROM product AS P1
WHERE sale_price = (SELECT max(sale_price)
FROM product AS P2
WHERE P1.product_type = P2.product_type
--方法2:先连接,再过滤
SELECT P1.product_id,P1.product_name,P1.product_type,P1.sale_price,P2.max_price
FROM product AS P1
INNER JOIN
(SELECT product_type,max(sale_price) AS max_price
FROM product
GROUP BY product_type) AS P2
ON P1.product_type = P2.product_type
WHERE P1.sale_price= p2.max_price;