连接

内连接 INNER JOIN

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

{
SELECT order_id,o.customer_id,first_name,last_name
FROM orders o
JOIN customers c
	 ON o.customer_id = c.customer_id-- 也可 USING(customer_id)

---

SELECT order_id,oi.product_id,quantity,oi.unit_price
FROM order_items oi
JOIN products p
	 ON oi.product_id = p.product_id
}

跨数据库连接

{
USE sql_store;

SELECT *
FROM order_items oi
JOIN sql_inventory.products p
	 ON oi.product_id = p.product_id-- 跨数据库连接注意sql_inventory.
}

自连接

{
USE sql_hr;

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-- 注意这里对employees表命了两个别名
}

多表连接

{
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
}

复合连接

{
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-- 多条件情况下的连接
}

隐式连接

{
SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id=c.customer_id

---

SELECT *
FROM orders o,customers c
WHERE o.customer_id=c.customer_id
-- 后者为隐式连接，两者等价，但是隐式连接中，如果忘记了WHERE，其不会报错，但是会输出交差连接。
}

外连接 OUTER JOIN（LEFT/RIGHT JOIN）

{
SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id=o.customer_id 
ORDER BY c.customer_id
-- 注意左连接和右连接中的左和右是相对位置。LEFT　JOIN中，相对位置上左表为customers，连接右表orders
-- 左向外联接的结果集包括 LEFT OUTER 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 
ORDER BY c.customer_id
-- 两者返回结果一样，尽量使用左连接，防止左右连接杂糅程序难读
}

多表外连接

{
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 c.customer_id

---

SELECT
    o.order_date,
    o.order_id,
    c.first_name,
    sh.name AS shipper,
    os.name AS status
FROM orders o
JOIN customers c
	ON c.customer_id=o.customer_id 
LEFT JOIN shippers sh
	ON sh.shipper_id=o.shipper_id
JOIN order_statuses os
	ON o.status = os.order_status_id
ORDER BY status
}

自外连接

{
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 -- 注意这里对employees表命了两个别名e m
}

USING

{
SELECT
    o.order_id,
    c.first_name
FROM orders o
JOIN customers c
	USING (customer_id) -- ON o.customer_id=c.customer_id
JOIN shippers sh
	USING (shipper_id)
-- USING关键字只能在不同表中列名字完全一致的情况下使用

---

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	USING (order_id, product_id)

---

SELECT
    p.date,
    c.name AS client,
    p.amount,
    pm.name
FROM payments p
JOIN clients c
	USING (client_id)
JOIN payment_methods pm
	ON p.payment_method=pm.payment_method_id
}

自然连接

系统会自己选择连接，可能出现意想不到的结果，不建议使用

{
SELECT
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c
}

交叉连接 CROSS JOIN

一般用来返回连接表的笛卡尔积。交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。

{
SELECT
    c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name-- 用于如小中大型号的、红黄蓝颜色的各种商品
}

合并 UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION与UNION ALL 都是行合并，UNION去重，后者不去重，会全部罗列出来。

{
SELECT
    order_id,
    order_date,
    'Actice' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION-- UNION与UNION ALL 都是行合并，前者去重，后者不去重，会全部罗列出来。
SELECT
    order_id,
    order_date,
    'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'-- 通过UNION合并多个查询结果

---

SELECT name
FROM shippers
UNION
SELECT first_name
FROM customers-- 第一段查询决定了列名，这里列名是name而不会是first_name
}