记录每一次成长

Appearance

什么时候需要用到UNION？ ​
  • 在一个查询中从不同的表返回结构数据
  • 对一个表执行多个查询，按一个查询返回数据

任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询

UNION规则 ​
  • UNION必须由2条或者2条以上的SELECT语句组成，语句之间用关键字UNION分隔
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数
  • 列数据类型必须兼容
  • 会自动去除重复行，可以使用UNION ALL会返回所有匹配行
  • 排序只能用一条ORDER BY，位于最后
其他类型的UNION（不常用） ​
  • EXCEPT: 检索在第一个表中存在在第二个表中不存在的行
  • INTERSECT:检索两个表中都存在的行
-- Active: 1739250781633@@127.0.0.1@3090@my_shop

# 单条语句的情况

# 位于IL、IN、MI这3个州的顾客
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');

# 不管哪个州，所有叫Fun4ALL的顾客
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';


# 组合查询
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';


##############挑战题###############


# 1
SELECT prod_id, quantity
FROM OrderItems
WHERE quantity = 100
UNION
SELECT prod_id, quantity
FROM OrderItems
WHERE prod_id LIKE 'BNBG%'
ORDER BY prod_id;

# 2
SELECT prod_id, quantity
FROM OrderItems
WHERE quantity = 100 OR prod_id LIKE 'BNBG%'
ORDER BY prod_id;

# 3

SELECT Customers.cust_name, Products.prod_name
FROM Customers, Products, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num
AND Products.prod_id = OrderItems.prod_id
ORDER BY Products.prod_name;


SELECT DISTINCT Customers.cust_name, Products.prod_name
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
INNER JOIN Products ON OrderItems.prod_id = Products.prod_id
ORDER BY Products.prod_name;


SELECT prod_name
FROM Products
UNION
SELECT cust_name 
FROM Customers
ORDER BY prod_name;