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

-- 訂單表同時鏈接顧客表和訂單狀態(tài)表，合并為有顧客和狀態(tài)信息的詳細訂單表
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，但作為最佳實踐，最好調整順序并統(tǒng)一只用 [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，用以區(qū)分今年的訂單和今年以前的訂單
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';