本篇博客深入讲解了SQL中的集合运算（UNION, EXCEPT, INTERSECT），表的加减法，以及JOIN操作（内连接、自连接、自然连接和外连接）。通过实例演示了如何使用这些技术处理实际商业场景中的数据，如商品库存管理和商品信息查询。
本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；
目录
Task04：集合运算-表的加减法和join等
一、表的加减法
集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。
在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。在Mysql中这类运算支持较少。
表的加法–UNION
UNION就是相当于2个表的并集，但是 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;
-- OR与UNION 的逻辑其实相同，上式可以写成下式；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;若想包含重复行，使用集合运算 UNION ALL方法即可。
-- 保留重复行
SELECT product_id, product_name
FROM product
UNION ALL
SELECT product_id, product_name
FROM product2;
查询结果如下:
UNION 存在隐式转换：
通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:
SELECT product_id, product_name, '1'
FROM product
UNION
SELECT product_id, product_name,sale_price
FROM product2;
上述查询能够正确执行,得到如下结果:
使用 SYSDATE()函数可以返回当前日期时间, 是一个日期时间类型的数据, 试测试该数据类型和数值,字符串等类型的兼容性.
例如, 以下代码可以正确执行, 说明时间日期类型和字符串,数值以及缺失值均能兼容.
SELECT SYSDATE(), SYSDATE(), SYSDATE()
UNION
SELECT 'chars', 123, null
上述代码的查询结果:
EXCEPT、INTERSECT
MYSQL不支持上面两种方法。但是INTERSECT即求2集合的交，完全可以用判断条件AND来表示，比如：使用AND谓词查找product表中利润率高于50%,并且售价低于1500的商品,查询结果如下所示.即完成了集合的交（INTERSECT运算）
SELECT *
FROM product
WHERE sale_price > 1.5 * purchase_price
AND sale_price < 1500对称差
两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合. 懂得2集合的差集计算就很容易理解该概念。对称差也是个非常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差。 从直观上就能看出来, 两个集合的对称差等于 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)二、连结(JOIN)
前一节我们学习了 UNION和INTERSECT 等集合运算, 这些集合运算的特征就是以行方向为单位进行操作. 通俗地说, 就是进行这些集合运算时, 会导致记录行数的增减. 使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数.
但这些运算不能改变列的变化, 虽然使用函数或者 CASE表达式等列运算, 可以增加列的数量, 但仍然只能从一张表中提供的基础信息列中获得一些"引申列", 本质上并不能提供更多的信息. 如果想要从多个表获取信息, 例如, 如果我们想要找出某个商店里的衣服类商品的名称,数量及价格等信息, 则必须分别从shopproduct 表和product 表获取信息.
连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算. 可以说,连结是 SQL 查询的核心操作, 掌握了连结, 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询。
内连结(INNER JOIN)
-- 内连结语法
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>有如下问题：找出东京商店里的衣服类商品的商品名称,商品价格,商品种类,商品数量信息. 现有2张表：
所以问题的关键是, 找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来. 这就是连结运算所要作的事情.
-- 先分别在两张表里做筛选, 把复杂的筛选条件按表分拆, 然后把筛选结果(作为表)连接起来, 避免了写复杂的筛选条件, 因此这种看似复杂的写法, 实际上整体的逻辑反而非常清晰.
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 *
FROMshopproduct
WHERE shop_name = '东京' ) AS SP
INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
(SELECT *
FROMproduct
WHERE product_type = '衣服') AS P
ON SP.product_id = P.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 JOINproduct AS P
ON (SP.product_id = P.product_id
AND SP.shop_name = '东京'
AND P.product_type = '衣服') ; -- 把条件都放在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 JOINproduct AS P
ON SP.product_id = P.product_id
WHERE SP.shop_name = '东京'
AND P.product_type = '衣服' ; -- 执行顺序：FROM 子句->WHERE 子句->SELECT 子句使用内连接时有以下3个要点：
要点一: 进行连结时需要在 FROM 子句中使用多张表.
之前的 FROM 子句中只有一张表, 而这次我们同时使用了shopproduct 和product 两张表,使用关键字 INNER JOIN 就可以将两张表连结在一起了:
FROM shopproduct AS SP INNER JOIN product AS P
要点二:必须使用 ON 子句来指定连结条件.
在进行内连结时 ON 子句是必不可少的。ON 子句是专门用来指定连结条件的, 我们在上述查询的 ON 之后指定两张表连结所使用的列以及比较条件, 基本上,它能起到与 WHERE 相同的筛选作用。
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用.
当两张表的列除了用于关联的列之外, 没有名称相同的列的时候, 也可以不写表名, 但表名使得我们能够在今后的任何时间阅读查询代码的时候, 都能马上看出每一列来自于哪张表, 能够节省我们很多时间.但是, 如果两张表有其他名称相同的列, 则必须使用上述格式来选择列名, 否则查询语句会报错.
练习题：
分别使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 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;结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待。
最简单的情形, 是在内连结之前就使用 GROUP BY 子句。但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合。
练习题:
每个商店中, 售价最高的商品的售价分别是多少?
-- 参考答案
SELECT SP.shop_id
,SP.shop_name
,MAX(P.sale_price) AS max_price
FROM shopproduct AS SP
INNER JOINproduct AS P
ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name自连结(SELF JOIN)
之前的内连结, 连结的都是不一样的两个表. 但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法.
找出每个商品种类当中售价高于该类商品的平均售价的商品.
-- 当时使用关联子查询来实现
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);
-- 内连接
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;
-- 上述代码的逻辑实际上更符合我们的思路, 因此尽管看起来复杂, 但思路实际上更加清晰.自然连结(NATURAL JOIN)
自然连接其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.（不常用）
SELECT * FROM shopproduct NATURAL JOIN product上述查询得到的结果, 会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来.
-- 等价于用INNER JOIN的语句
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
这个结果少了运动 T 恤, 这是由于运动 T 恤的 regist_date 字段为空, 在进行自然连结时, 来自于 product 和 product2 的运动 T 恤这一行数据在进行比较时, 实际上是在逐字段进行等值连结, 两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行.
如果我们将查询语句进行修改:
-- 但是这直接使用内连接一样，因此NATURAL JOIN不用特殊记忆。
SELECT *
FROM (SELECT product_id, product_name
FROM product ) AS A
NATURAL JOIN
(SELECT product_id, product_name
FROM product2) AS B;
那就可以得到正确的结果了:
使用连结来实现求交集的运算
SELECT P1.*
FROMproduct AS P1
INNER JOINproduct2 AS P2
ON P1.product_id = P2.product_id
-- 以下方法不等价于上面的。因为数据中NULL的存在无法用=
SELECT P1.*
FROMproduct AS P1
INNER JOINproduct2 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)查询结果:
外连接（OUTER JOIN)
内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行.
按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.
左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.
-- 左连结（3种的OUTER 都可省略，由于左连接与右连结逻辑相仿，后续仅介绍左连接
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
FROMproduct AS P
LEFT OUTER JOINshopproduct AS SP
ON SP.product_id = P.product_id;
上述查询得到的检索结果如下(由于并未使用 ORDER BY 子句指定顺序,你执行上述代码得到的结果可能顺序与下图不同):
我们观察上述结果可以发现, 有两种商品: 高压锅和圆珠笔, 在所有商店都没有销售. 由于我们在 SELECT 子句选择列的显示顺序以及未对结果进行排序的原因,
●外连结要点 1: 选取出单张表中全部的信息
与内连结的结果相比,不同点显而易见,那就是结果的行数不一样.内连结的结果中有 13 条记录,而外连结的结果中有 15 条记录,增加的 2 条记录到底是什么呢?这正是外连结的关键点. 多出的 2 条记录是高压锅和圆珠笔,这 2 条记录在shopproduct 表中并不存在,也就是说,这 2 种商品在任何商店中都没有销售.由于内连结只能选取出同时存在于两张表中的数据,因此只在product 表中存在的 2 种商品并没有出现在结果之中.相反,对于外连结来说,只要数据存在于某一张表当中,就能够读取出来.在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结.如果使用内连结的话,根据 SELECT 语句执行时商店库存状况的不同,结果的行数也会发生改变,生成的单据的版式也会受到影响,而使用外连结能够得到固定行数的结果.虽说如此,那些表中不存在的信息我们还是无法得到,结果中高压锅和圆珠笔的商店编号和商店名称都是 NULL （具体信息大家都不知道,真是无可奈何）.外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在（在原表之外）的信息”.相反,只包含表内信息的连结也就被称为内连结了.
●外连结要点 2:使用 LEFT、RIGHT 来指定主表.
外连结还有一点非常重要,那就是要把哪张表作为主表.最终的结果中会包含主表内所有的数据.指定主表的关键字是 LEFT 和 RIGHT.顾名思义,使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表.代码清单 7-11 中使用了 RIGHT ,因此,右侧的表,也就是product 表是主表.我们还可以像代码清单 7-12 这样进行改写,意思完全相同.这样你可能会困惑，到底应该使用 LEFT 还是 RIGHT？其实它们的功能没有任何区别,使用哪一个都可以.通常使用 LEFT 的情况会多一些,但也并没有非使用这个不可的理由,使用 RIGHT 也没有问题.
通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两种方式会到完全相同的结果.
结合 WHERE 子句使用左连结
本节主要是针对外连结中碰到NULL数据时遇到的一些问题。在结合WHERE子句使用外连结时, 由于外连结的结果很可能与内连结的结果不一样, 会包含那些主表中无法匹配到的行, 并用缺失值填写另一表中的列, 由于这些行的存在, 因此在外连结时使用WHERE子句, 情况会有些不一样. 我们来看一个例子:
练习题:
使用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果.
注意高压锅和圆珠笔两种商品在所有商店都无货, 所以也应该包括在内.
按照"结合WHERE子句使用内连结"的思路, 我们很自然会写出如下代码
SELECT P.product_id
,P.product_name
,P.sale_price
,SP.shop_id
,SP.shop_name
,SP.quantity
FROMproduct AS P
LEFT OUTER JOINshopproduct AS SP
ON SP.product_id = P.product_id
WHERE quantity< 50
然而不幸的是, 得到的却是如下的结果:
观察发现,返回结果缺少了在所有商店都无货的高压锅和圆珠笔。聪明的你可能很容易想到，在WHERE过滤条件中增加OR``quantity IS NULL的判断条件，便可以得到预期结果。然而在实际环境中，由于数据量大且数据质量并非像我们设想的那样"干净"，我们并不能容易地意识到缺失值等问题数据的存在，因此，还是让我们想一下如何改写我们的查询以使得它能够适应更复杂的真实数据的情形吧。
联系到我们已经掌握了的SQL查询的执行顺序(FROM->WHERE->SELECT),我们发现, 问题可能出在筛选条件上, 因为在进行完外连结后才会执行WHERE子句, 因此那些主表中无法被匹配到的行就被WHERE条件筛选掉了。
明白了这一点, 我们就可以试着把WHERE子句挪到外连结之前进行: 先写个子查询,用来从shopproduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来。
我们把上述思路写成SQL查询语句:
-- 一般都建议where 放在子查询中写，不要放在最后进行，不仅出错率低，还能提升查询效率
SELECT P.product_id
,P.product_name
,P.sale_price
,SP.shop_id
,SP.shop_name
,SP.quantity
FROMproduct AS P
LEFT OUTER JOIN-- 先筛选quantity<50的商品
(SELECT *
FROMshopproduct
WHERE quantity < 50 ) AS SP
ON SP.product_id = P.product_id
得到的结果如下:
全外连结OUTER JOIN在Mysql中不支持，但可以对左连结和右连结的结果使用UNION达到相同效果。有了对左连结和右连结的了解, 就不难理解全外连结的含义了. 全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充。
多表连接
-- 多表连接格式如下，没太大变化
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
,IP.inventory_quantity
FROMshopproduct AS SP
INNER JOINproduct 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
FROMproduct AS P
LEFT OUTER JOINshopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id在刚开始介绍连结的时候, 书上提到过, 除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件.
非等值自左连结(SELF JOIN)
除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件.
练习题:
希望对 product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品,
SELECT product_id
,product_name
,sale_price
,COUNT(p2_id) AS rank_id
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 rank_id;
注 1: COUNT 函数的参数是列名时, 会忽略该列中的缺失值, 参数为 * 时则不忽略缺失值.
注 2: 上述排名方案存在一些问题–如果两个商品的价格相等, 则会导致两个商品的排名错误, 例如, 叉子和打孔器的排名应该都是第六, 但上述查询导致二者排名都是第七。筛选时把<=改成<。意为比他大的个数，然后在count后加1，代表自己的排名。该题如果用窗口函数会非常简单。将在下一个Task中介绍。
交叉连结—— CROSS JOIN(笛卡尔积)
之前的无论是外连结内连结, 一个共同的必备条件就是连结条件–ON 子句, 用来指定连结的条件. 如果你试过不使用这个连结条件的连结查询, 你可能已经发现, 结果会有很多行. 在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合. 数据库表(或者子查询)的并,交和差都是在纵向上对表进行扩张或筛选限制等运算的, 这要求表的列数及对应位置的列的数据类型"相容", 因此这些运算并不会增加新的列, 而交叉连接(笛卡尔积)则是在横向上对表进行扩张, 即增加新的列, 这一点和连结的功能是一致的. 但因为没有了ON子句的限制, 会对左表和右表的每一行进行组合, 这经常会导致很多无意义的行出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有一些用处.
交叉连结的语法有如下几种形式:
-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM shopproduct AS SP
CROSS JOINproduct AS P;
--2.使用逗号分隔两个表,并省略 ON 子句，这种比较常用
SELECT SP.shop_id
,SP.shop_name
,SP.product_id
,P.product_name
,P.sale_price
FROM shopproduct AS SP ,product AS P;
请大家试着执行一下以上语句.
可能大家会惊讶于结果的行数, 但我们还是先来介绍一下语法结构吧.对满足相同规则的表进行交叉连结的集合运算符是 CROSS JOIN （笛卡儿积）.进行交叉连结时无法使用内连结和外连结中所使用的ON 子句,这是因为交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积.本例中,因为shopproduct 表存在 13 条记录,product 表存在 8 条记录,所以结果中就包含了 13 × 8 = 104 条记录.
可能这时会有读者想起前面我们提到过集合运算中的乘法会在本节中进行详细学习,这就是上面介绍的交叉连结.内连结是交叉连结的一部分,“内”也可以理解为“包含在交叉连结结果中的部分”.相反,外连结的“外”可以理解为“交叉连结结果之外的部分（一部分在之外）”.
交叉连结没有应用到实际业务之中的原因有两个.一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持.
连结与笛卡儿积的关系
考察笛卡儿积和连结, 不难发现, 笛卡儿积可以视作一种特殊的连结(事实上笛卡儿积的语法也可以写作 CROSS JOIN), 这种连结的 ON 子句是一个恒为真的谓词.
反过来思考, 在对笛卡儿积进行适当的限制之后, 也就得到了内连结和外连结.
例如, 对于 shopproduct 表和 product 表, 首先建立笛卡尔乘积:
SELECT SP.*, P.*
FROM shopproduct AS SP
CROSS JOIN product AS P;
然后对上述笛卡尔乘积增加筛选条件 SP.product_id=P.product_id, 就得到了和内连结一致的结果:
SELECT SP.*, P.*
FROM shopproduct AS SP
CROSS JOIN product AS P
WHERE SP.product_id = P.product_id;
查询结果如下:
实际上, 上述写法中, 将 CROSS JOIN 改为逗号后, 正是内连结的旧式写法, 但在 ANSI 和 ISO 的 SQL-92 标准中, 已经将使用 INNER JION …ON… 的写法规定为标准写法, 因此极力推荐大家在平时写 SQL 查询时, 使用规范写法.
三、练习题
1.找出 product 和 product2 中售价高于 500 的商品的基本信息。(无product2建表代码，请自行创建）
select * from product p1,product2 p2
where p1.sale_price>500 and p2.sale_price>500 ;2.借助对称差的实现方式, 求product和product2的交集。
select * from
(select p1.* from product p1 where p1.product not in (p2.product_id)),
(select p2.* from product2 p2 where p2.product_id not in (p1.product_id))3.每类商品中售价最高的商品都在哪些商店有售 ？
select p.product_type,q.product_id,g.shop_name from(
select max(sale_price) as max_price,product_type from product
group by product_type) p
left join
(select product_type,product_id,sale_price from product)q
on p.product_type = q.product_type and p.max_price = q.sale_price
left join
(select shop_name,product_id from shopproduct) g
on q.product_id = g.product_id4.分别使用内连结和关联子查询每一类商品中售价最高的商品。
-- 内连接
select p.product_type, q.product_name from
(select max(sale_price) as max_price,product_type from product group by product_type) p
inner join
product q
on p.product_type = q.product_type and p.max_price=q.sale_price
5.用关联子查询实现：在product表中，取出 product_id, product_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
-- 无视题干关联子查询
select product_id,product_name,sale_price,
(select sum(sale_price) from product )as sum_price
from product order by sale_price asc;四、总结
本章介绍的主要是针对表的操作，其实也就是相当于对集合的操作。其中工作中最实用的包括UNION/LEFT JOIN/RIGHT JOIN/INNER JOIN/ON 等方法，这些内容需要知道连结的基本过程和含义，剩下的了解即可。内连结往往比关联子查询更好理解，因此我更倾向于使用表的连结写SQL。
507
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
