第三章 跨表格操作

1- 内连接

JOIN语句

SELECT *
FROM orders
JOIN customers 
	ON orders.customer_id = customers.customer_id
-- FROM A JOIN B ON c，根据c，把B加在A之后

若要选重复的列，则要在前面加前缀，如orders.customer_id

SELECT order_id, orders.customer_id, first_name, last_name -- 直接写customer_id就报错
FROM orders
JOIN customers
	ON orders.customer_id = customers.customer_id

---

可以给表格简单命名，只要在FROM和JOIN对应表格名称后加简单命名就行。但是注意命名后，全局都要用简单命名，以前的名字会失效

SELECT order_id, o.customer_id, first_name, last_name 
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id -- 若用orders.customer_id则会报错

2- 跨数据库连接

加数据库前缀即可

USE sql_inventory;

SELECT *
FROM sql_store.order_items oi
JOIN products p
	ON oi.product_id = p.product_id

3- 自连接

例如员工信息表后接对应的汇报人

JOIN的过程：依次查询每行后者和前者一样的判定特征，然后扩张表格；内连接：只会显示能满足判定特征的对应的行。外连接：能显示其余没有对应判定特征的行。

USE sql_hr;

SELECT *
FROM employees e -- 自连接用两个别名
JOIN employees m
	ON e.reports_to = m.employee_id

 简化版，标出员工编号，名字和manager名字：

SELECT 
	e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
JOIN employees m
	ON e.reports_to = m.employee_id

 4- 多表连接

连JOIN

USE sql_store;

SELECT 
	o.order_id,
	o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c 
	ON o.customer_id = c.customer_id
JOIN order_statuses os
	ON o.status = os.order_status_id

5- 复合连接条件

加AND就行

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id

6- 隐式连接

不建议用

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

7- 外连接

LEFT JOIN 或 RIGHT JOIN；分别表示不管条件满不满足，总是完整显示FROM 后的表或JOIN 后的表。没有对应值的话返回NULL。

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM orders o
RIGHT JOIN customers c 
	ON c.customer_id = o.customer_id

 8- 多表外连接

后面接就行

USE sql_store;

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
LEFT JOIN shipper sh
	ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

9- 自外连接

很类似啊，so easy 

USE sql_hr;

SELECT 
	e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m
	ON e.reports_to = m.employee_id

10- USING子句

当连接两个表的判断列名字相同时，可以用USING来简化表达

SELECT * 
FROM orders o 
JOIN customers c
USING (customer_id)
-- 等价于ON o.customer_id = c.customer_id

-- 多个判断条件时为 USING (order_id, product_id)

 11- 自然连接

自动根据相同的列进行拼接

SELECT *
FROM orders o
NATURAL JOIN customers c

12- 交叉连接

JOIN CROSS子句，即笛卡尔积，如

A = {1,2}
B = {3,4,5}

则 A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };

SELECT *
FROM customers c
CROSS JOIN products p --显示写法


SELECT *
FROM customers c, products p -- 隐式写法

13- 联合（并集）

将两个表按行合并，并取并集（即去重）；两个表要有一样的列数；列名是取决于第一个表的

可以用于根据条件添加列；即依次添加不同条件下的列

SELECT 
	order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'

UNION 
SELECT 
	order_id,
    order_date,
    'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'