Sql在多张表中检索数据的方法详解

select *
from order 
inner join customers
	on	order.id = customers.id;
-- inner可省略
-- 可起别名
select 
	order_id,
	customer_id,
	o.id,
	last_name
from order o
join customers c
	on	o.id = c.id;

use sql_store;
select * from order_items oi
join sql_inventory.products p
    on oi.product_id = p.product_id
-- 或
use sql_inventory;
select * from sql_store.order_items oi
join products p
    on oi.product_id = p.product_id
-- 非当前使用的库，才需要加库前缀

USE sql_hr
select 
	e.employee_id,
	e.first_name,
	m.first_name as manger
from
	employees e
join employee m
	on e.reports_to = m.employee_id

-- 订单表同时链接顾客表和订单状态表，合并为有顾客和状态信息的详细订单表
USE sql_store;
SELECT 
	o.order_id,
	o.order_date,
	c.first_name,
	c.last_name,
	os.name AS status
FROM	order o
JOIN	customers c
	on	o.customers_id = c.customers_id
JOIN	order_statuses os
    ON o.status = os.order_status_id;

USE sql_store
SELECT *
FROM order_items oi
	JOIN order_item_notes oin
		ON oi.order_Id = oin.order_Id
    	AND oi.product_id = oin.product_id

USE sql_store;
SELECT *
FROM orders o
JOIN customers c
	on o.id = c.id;
-- 可转换为下面这个
SELECT *
FROM orders 

-- 合并顾客表和订单表，用INNER JOIN
USE sql_store;
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
JOIN orders o
    ON o.customer_id = c.customer_id
ORDER BY customer_id
-- 这样是INNER JOIN，只展示有订单的顾客（及其订单），也就是两张表的交集，但注意这里因为一个顾客可能有多个订单，所以INNER JOIN以后顾客信息其实是是广播了的，即一条顾客信息被多条订单记录共用，当然 这叫广播（broadcast）效应，是另一个问题，这里关注的重点是 INNER JOIN 的结果确实是两表的交集，是那些同时有顾客信息和订单信息的记录。
-- 若要展示全部顾客（及其订单，如果有的话），要改用LEFT (OUTER) JOIN，结果相较于 INNER JOIN 多了没有订单的那些顾客，即只有顾客信息没有订单信息的记录
-- 当然，也可以调换左右表的顺序（即调换FROM和JOIN的对象）再 RIGHT JOIN，即：
FROM orders o
    RIGHT [OUTER] JOIN customers c
    -- 中括号 [] 表示是可选项、可省略 
    ON o.customer_id = c.customer_id
-- 若要展示全部订单（及其顾客），就应该是 orders RIGHT JOIN customers，结果相较于 INNER JOIN 多了没有顾客的那些订单，即只有订单信息没有顾客信息的记录。（注：因为这里所有订单都有顾客，所以这里 RIGHT JOIN 结果和 INNER JOIN 一样）

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 shippers sh
    ON o.shipper_id = sh.shipper_id
ORDER BY customer_id
-- 虽然可以调换顺序并用 RIGHT JOIN，但作为最佳实践，最好调整顺序并统一只用 [INNER] JOIN 和 LEFT [OUTER] JOIN（总是左表全包含），这样，当要合并的表比较多时才方便书写和理解而不易混乱

USE sql_hr;
SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m  -- 包含所有雇员（包括没有report_to的老板本人）
    ON e.reports_to = m.employee_id

SELECT
    o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
    USING (customer_id)
LEFT JOIN shippers sh
    USING (shipper_id)
ORDER BY order_id
-- 复合主键表间复合连接条件的合并也可用 USING，中间逗号隔开就行：
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_Id AND
    oi.product_id = oin.product_id
/USING (order_id, product_id)
-- USING对复合主键的简化效果更加明显
-- 列名不同就必须用 ON …… 了
-- 实际中同一个字段在不同表列名不同的情况也很常见，不能想当然的用USING

USE sql_store;
SELECT 
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

-- 得到顾客和产品的全部组合（毫无意义，纯粹为了展示交叉连接）
USE sql_store;
SELECT 
    c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
-- 上面是显性语法，还有隐式语法，之前讲过，其实就是隐式内合并忽略WHERE子句（即合并条件）的情况，也就是把 CROSS JOIN 改为逗号，即 FROM A CROSS JOIN B 等效于 FROM A, B，Mosh更推荐显式语法，因为更清晰
USE sql_store;
SELECT 
    c.first_name,
    p.name
FROM customers c, products p
ORDER BY c.first_name

-- 给订单表增加一个新字段——status，用以区分今年的订单和今年以前的订单
USE sql_store;
    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  -- Archived 归档
    FROM orders
    WHERE order_date < '2019-01-01';