【数据库】集合运算UNION,INTERSECT,EXCEPT,JOIN

7.1 表的加减法
7.3 表之间的加法运算UNION（求并集）

sql">postgres=# SELECT product_id,product_name
postgres-# FROM Product
postgres-# UNION
postgres-# SELECT product_id,product_name
postgres-# FROM Product2;
 product_id | product_name
------------+--------------
 0001       | T恤
 0009       | 手套
 0004       | 菜刀
 0002       | 打孔器
 0010       | 水壶
 0006       | 叉子
 0005       | 高压锅
 0007       | 擦菜板
 0003       | 运动T恤
 0008       | 圆珠笔
(10 行记录)

注：1.自动去除重复记录 2.列数和数据类型保持一致 3.SELECT语句可以无限使用，ORDER BY语句只能在最后使用一次。
7.5 保留重复行

sql">postgres=# SELECT product_id,product_name
postgres-# FROM Product
postgres-# UNION ALL
postgres-# SELECT product_id ,product_name
postgres-# FROM Product2
postgres-# ;
 product_id | product_name
------------+--------------
 0001       | T恤
 0002       | 打孔器
 0003       | 运动T恤
 0004       | 菜刀
 0005       | 高压锅
 0006       | 叉子
 0007       | 擦菜板
 0008       | 圆珠笔
 0001       | T恤
 0002       | 打孔器
 0003       | 运动T恤
 0009       | 手套
 0010       | 水壶
(13 行记录)

7.6 选取两个表中的公共部分INTERSECT(交集）

sql">postgres=# SELECT product_id,product_name
postgres-# FROM Product
postgres-# INTERSECT
postgres-# SELECT product_id,product_name
postgres-# FROM Product2;
 product_id | product_name
------------+--------------
 0001       | T恤
 0002       | 打孔器
 0003       | 运动T恤
(3 行记录)

7.7 对记录做减法运算EXCEPT

sql">postgres=# SELECT product_id,product_name
postgres-# FROM Product
postgres-# EXCEPT
postgres-# SELECT product_id,product_name
postgres-# FROM Product2;
 product_id | product_name
------------+--------------
 0006       | 叉子
 0004       | 菜刀
 0005       | 高压锅
 0007       | 擦菜板
 0008       | 圆珠笔
(5 行记录)

示意图：
在这里插入图片描述
注:语句的顺序会影响结果
7.2 连结JOIN（列会改变）
7.8 将两张表进行内连接

sql">postgres=# SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,
postgres-# P.sale_price
postgres-# FROM ShopProduct AS SP INNER JOIN Product AS P
postgres-# ON SP.product_id=P.product_id;
 shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+------------
 000D    | 福冈      | 0001       | T恤          |       1000
 000A    | 东京      | 0001       | T恤          |       1000
 000B    | 名古屋    | 0002       | 打孔器       |        500
 000A    | 东京      | 0002       | 打孔器       |        500
 000C    | 大阪      | 0003       | 运动T恤      |       4000
 000B    | 名古屋    | 0003       | 运动T恤      |       4000
 000A    | 东京      | 0003       | 运动T恤      |       4000
 000C    | 大阪      | 0004       | 菜刀         |       3000
 000B    | 名古屋    | 0004       | 菜刀         |       3000
 000C    | 大阪      | 0006       | 叉子         |        500
 000B    | 名古屋    | 0006       | 叉子         |        500
 000C    | 大阪      | 0007       | 擦菜板       |        880
 000B    | 名古屋    | 0007       | 擦菜板       |        880
(13 行记录)

注：第一行的别名可以不写，前提是两个表的列名不同。
7.10 内连结和WHERE子句的联合使用

sql">postgres=# SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,
postgres-# P.sale_price
postgres-# FROM ShopProduct AS SP INNER JOIN Product AS P
postgres-# ON SP.product_id = P.product_id
postgres-# WHERE SP.shop_id='000A';
 shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+------------
 000A    | 东京      | 0001       | T恤          |       1000
 000A    | 东京      | 0002       | 打孔器       |        500
 000A    | 东京      | 0003       | 运动T恤      |       4000
(3 行记录)

7.11 外连结 OUTER JOIN（包含主表的全部信息）

sql">postgres=# SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,
postgres-# P.sale_price
postgres-# FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
postgres-# ON SP.product_id=P.product_id;
 shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+------------
 000D    | 福冈      | 0001       | T恤          |       1000
 000A    | 东京      | 0001       | T恤          |       1000
 000B    | 名古屋    | 0002       | 打孔器       |        500
 000A    | 东京      | 0002       | 打孔器       |        500
 000C    | 大阪      | 0003       | 运动T恤      |       4000
 000B    | 名古屋    | 0003       | 运动T恤      |       4000
 000A    | 东京      | 0003       | 运动T恤      |       4000
 000C    | 大阪      | 0004       | 菜刀         |       3000
 000B    | 名古屋    | 0004       | 菜刀         |       3000
         |           |            | 高压锅       |       6800
 000C    | 大阪      | 0006       | 叉子         |        500
 000B    | 名古屋    | 0006       | 叉子         |        500
 000C    | 大阪      | 0007       | 擦菜板       |        880
 000B    | 名古屋    | 0007       | 擦菜板       |        880
         |           |            | 圆珠笔       |        100
(15 行记录)

注：主表的确定看是LEFT还是RIGHT
7.12 另一个作为主表

sql">postgres=# SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,
postgres-# P.sale_price
postgres-# FROM ShopProduct AS SP LEFT OUTER JOIN Product AS P
postgres-# ON SP.product_id=P.product_id;
 shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+------------
 000D    | 福冈      | 0001       | T恤          |       1000
 000A    | 东京      | 0001       | T恤          |       1000
 000B    | 名古屋    | 0002       | 打孔器       |        500
 000A    | 东京      | 0002       | 打孔器       |        500
 000C    | 大阪      | 0003       | 运动T恤      |       4000
 000B    | 名古屋    | 0003       | 运动T恤      |       4000
 000A    | 东京      | 0003       | 运动T恤      |       4000
 000C    | 大阪      | 0004       | 菜刀         |       3000
 000B    | 名古屋    | 0004       | 菜刀         |       3000
 000C    | 大阪      | 0006       | 叉子         |        500
 000B    | 名古屋    | 0006       | 叉子         |        500
 000C    | 大阪      | 0007       | 擦菜板       |        880
 000B    | 名古屋    | 0007       | 擦菜板       |        880
(13 行记录)

7.13 建立新表

sql">postgres=# SELECT * FROM InventoryProduct;
 inventory_id | product_id | inventory_quantity
--------------+------------+--------------------
 P001         | 0001       |                  0
 P001         | 0002       |                120
 P001         | 0003       |                200
 P001         | 0004       |                  3
 P001         | 0005       |                  0
 P001         | 0006       |                 99
 P001         | 0007       |                999
 P001         | 0008       |                200
 P002         | 0001       |                 10
 P002         | 0002       |                 25
 P002         | 0003       |                 34
 P002         | 0004       |                 19
 P002         | 0005       |                 99
 P002         | 0006       |                  0
 P002         | 0007       |                  0
 P002         | 0008       |                 18
(16 行记录)

7.14 三张表连结

sql">postgres=# SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity
postgres-#   FROM ShopProduct AS SP INNER JOIN Product AS P
postgres-#     ON SP.product_id = P.product_id
postgres-#                INNER JOIN InventoryProduct AS IP
postgres-#                    ON SP.product_id = IP.product_id
postgres-# WHERE IP.inventory_id='P001';
 shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity
---------+-----------+------------+--------------+------------+--------------------
 000A    | 东京      | 0001       | T恤          |       1000 |                  0
 000A    | 东京      | 0002       | 打孔器       |        500 |                120
 000A    | 东京      | 0003       | 运动T恤      |       4000 |                200
 000B    | 名古屋    | 0002       | 打孔器       |        500 |                120
 000B    | 名古屋    | 0003       | 运动T恤      |       4000 |                200
 000B    | 名古屋    | 0004       | 菜刀         |       3000 |                  3
 000B    | 名古屋    | 0006       | 叉子         |        500 |                 99
 000B    | 名古屋    | 0007       | 擦菜板       |        880 |                999
 000C    | 大阪      | 0003       | 运动T恤      |       4000 |                200
 000C    | 大阪      | 0004       | 菜刀         |       3000 |                  3
 000C    | 大阪      | 0006       | 叉子         |        500 |                 99
 000C    | 大阪      | 0007       | 擦菜板       |        880 |                999
 000D    | 福冈      | 0001       | T恤          |       1000 |                  0
(13 行记录)

7.15 交叉连结（作为了解)
练习：
在这里插入图片描述

sql">postgres=# SELECT COALESCE(SP.shop_id, '不明')  AS shop_id,
postgres-#        COALESCE(SP.shop_name, '不明') AS shop_name,
postgres-#        P.product_id,
postgres-#        P.product_name,
postgres-#        P.sale_price
postgres-#   FROM ShopProduct SP RIGHT OUTER JOIN Product P
postgres-#     ON SP.product_id = P.product_id
postgres-# ORDER BY shop_id;
 shop_id | shop_name | product_id | product_name | sale_price
---------+-----------+------------+--------------+------------
 000A    | 东京      | 0002       | 打孔器       |        500
 000A    | 东京      | 0003       | 运动T恤      |       4000
 000A    | 东京      | 0001       | T恤          |       1000
 000B    | 名古屋    | 0006       | 叉子         |        500
 000B    | 名古屋    | 0002       | 打孔器       |        500
 000B    | 名古屋    | 0003       | 运动T恤      |       4000
 000B    | 名古屋    | 0004       | 菜刀         |       3000
 000B    | 名古屋    | 0007       | 擦菜板       |        880
 000C    | 大阪      | 0006       | 叉子         |        500
 000C    | 大阪      | 0007       | 擦菜板       |        880
 000C    | 大阪      | 0003       | 运动T恤      |       4000
 000C    | 大阪      | 0004       | 菜刀         |       3000
 000D    | 福冈      | 0001       | T恤          |       1000
 不明    | 不明      | 0005       | 高压锅       |       6800
 不明    | 不明      | 0008       | 圆珠笔       |        100
(15 行记录)

注意：指定谁的product；COALESCE用法；

【数据库】集合运算UNION,INTERSECT,EXCEPT,JOIN

相关文章

SQLyog快捷键，这一篇就够！！

磕代码Java：删除输入数组中的某个数字并遍历输出

直接爬取个省市区的行政代码编号

【java笔记】集合Set：HashSet，LinkedHashSet，TreeSet，Comparable，Comparator

VOL.bat 内容

【java笔记】泛型：泛型类，泛型方法，泛型接口，可变参数

自古深情留不住，技术Leader30条套路得人心！！！

【数据库】SQL高级处理:窗口函数，GROUPING