MySQL基础：在多张表格中检索数据

多表合并，依据主表和根据主表和连接表的属性相关性来生成新的关系表格。（可重复的并集）

1. 内连接

各表分开存放是为了保证最下实体单元内的属性联系更加紧密，但是优势需要将相互之间的关系连接，从而满足多表间的查询要求。如果两个关系之间有相同的属性，则可以用内连接将两个表合并。
虽然SELECT语句还是放在前面，但是我们写语句时从后往前看，假定已经将两个关系合并过了来操作SELECT语句。
NOTE：

• AS 也可以用来设置简化的别名，同时AS可省略。一旦用别名，所有地方都需要使用别名。
• 选择关系中相同的属性列时，必须指定关系表名前缀，不然会报错。良好的编程习惯是：明确属性不加前缀，不明确前加表名前缀。
• 编写SELECT的列时，可以先用*，确定好别名和需要选择的属性列后，再修改SELECT中选定的列名。
• 合并完后的表格，一般需要设置排序规则。

SELECT o.order_id, c.customer_id, c.first_name, c.last_name
-- 列前尽量加上表名前缀
FROM orders o  -- 创建别名，省略AS
JOIN customers c
    ON o.customer_id = c.customer_id
ORDER BY o.order_id ASC

2. 跨数据库连接

只需要在不属于当前数据库的表前面加上数据库名前缀即可。
USE关键字可以将操作转移到某个数据库，而.字符可以跨数据库访问表

USE sql_store;
SELECT *
FROM order_items  oi
JOIN sql_inventory.products  sp
    ON oi.product_id = sp.product_id

3. 自连接

自连接和内连接的区别在于，内连接用的是两个不同的表，自连接用的是相同的表（表内属性列有相同属性数值），然后取不同的别名。都是采用相同的属性值连接起来。

USE sql_hr;
SELECT e.employee_id, e.first_name employee, m.first_name manager
FROM employees e 
JOIN employees m 
	ON e.reports_to = m.employee_id

4. 多表连接

一个主表采用FROM xxx 后面重复使用JOIN table ON attribute语句。

USE sql_store;
SELECT o.order_id, o.order_date, c.first_name, os.name
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id
-- ORDER BY o.order_id

NOTE:选择合适的列输出，保证输出的数据不凌乱（编程时再处理，先是全部选取*）。

5. 复合连接条件

软件中属性码前黄色闪电表示为主码（候选码），复合主键

对于复合主键，即候选码，需要采用所有的候选码的属性来进行连接两个表格。

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 – 该查询程序运行出来无满足要求的结果
/* USING (order_id, product_id)*/

6. 隐式连接语法

用FROM WHRER 来替代 FROM JOIN ON，但是尽量不要用隐式连接。因为如果忘记用关键词WHERE将产生“交叉连接”，两个10条记录的表格将产生100条记录新表格。

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

7. 外连接

• (INNER) JOIN 结果只包含两表的交集，注意“广播（broadcast）”效应，根据多列出的有重复的属性记录数值会进行扩充
• LEFT/RIGHT (OUTER) JOIN 结果里除了交集，还包含只出现在左/右表中的记录.左连接则包含主表全部信息，右连接则包含连接的表全部信息，没有连接的属性数值则用NULL进行填充。尽量少使用右连接RIGHT JOIN
• 连接过程涉及记录数据互补的问题

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

8. 多表外连接

与内连接的多表连接类似，尽量不用RIGHT JOIN；
内连接和外连接可以混合使用。

SELECT o.order_date, order_id, c.first_name customer, s.name shipper, os.name
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
LEFT JOIN shippers s   -- 外连接的作用域大于内连接
    ON o.shipper_id = s.shipper_id
JOIN order_statuses os     -- 只要相关联，以多的数据为准
-- 内连接中，不同表中有没有关联的记录则不表示出来
    ON o.status = os.order_status_id

9. 自外连接

自连接和外连接的综合使用

USE sql_hr;
SELECT e.employee_id, e.first_name employee, m.first_name manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id  
    -- 根据主表条件来确定连接表的记录数值内容

10. USING子句

当作为合并条件 join condition 的列在两个表中有相同列名时，可用 USING (……, ……) 取代 ON …… AND …… 予以简化，内/外连接均可如此简化。（复合连接即候选码，则USING的（内部采用逗号隔开即可））

NOTE：
1）USING对复合主键的简化效果更加明显;
2）相同属性对应的不同列名必须采用ON…

USE sql_store;
SELECT o.order_id, c.first_name customer, s.name shipper
FROM orders o
JOIN customers c
    -- ON o.customer_id = c.customer_id
    USING (customer_id)
LEFT JOIN shippers s
    USING (shipper_id)

11. 自然连接

NATURAL JOIN 就是让MySQL自动检索同名列作为合并条件。但是不建议使用，因为有时会产生意想不到的不可控的结果。

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

12. 交叉连接

CROSS JOIN得到两个表记录排列组合（两个表中的记录两两相互连接）后的所有记录情况，不需要合并条件（任何两个表都可以交叉连接，但是内连接和外连接不是任意两个表就可以连接的）。内连接和外连接产生的结果为交叉连接产生的结果的子集。

NOTE:隐式内合并忽略 WHERE 子句（即合并条件）的情况，交叉连接的隐式表达也就是把 CROSS JOIN 改为逗号，即 FROM A CROSS JOIN B 等效于 FROM A, B，更推荐显式语法，因为更清晰

USE sql_store;
SELECT o.order_id, c.first_name customer
FROM orders o
CROSS JOIN customers c
-- FROM orders o, customers c
ORDER BY o.order_id

13. 联合

FROM …… JOIN …… 可对多张表进行横向列合并，而 …… UNION …… 可用来按行纵向合并多个查询结果，这些查询结果可能来自相同或不同的表.

• 同一张表可通过UNION添加新的分类字段，即先通过分类查询并添加新的分类字段再UNION合并为带分类字段的新表。
• 不同表通过UNION合并的情况如：将一张18年的订单表和19年的订单表纵向合并起来在一张表里展示.

NOTE:
1）合并的查询结果必须列数相等，否则会报错；
2）合并表里的列名由排在UNION前面的决定。

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'

SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers