【MySQL】多表查询

JOIN …… ON

• JOIN （要联查的表） ON （联查的条件）

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
-- 可以给常用的名字进行缩写

JOIN customers c ON o.customer_id = c.customer_id
-- 根据customer_id将两个表合并在一起

-- 小小练习
SELECT order_id, o.product_id, quantity, o.unit_price
FROM order_items o
JOIN products p ON p.product_id = o.product_id

跨数据库连接

• 给不在当前数据库的表加前缀

SELECT *
FROM order_items oi
-- 给不在当前查询库的表加上他自己的库前缀
JOIN sql_inventory.products p ON oi.product_id = p.product_id

自连接

• 同一张表需要使用不同的别名，查询的每列前要指定别名

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

多表连接

• 一直往下写JOIN ON就行

SELECT o.order_id, o.order_date, c.first_name, c.last_name, os.name AS states
-- 多表连接 接着往下写JOIN ON就行
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON o.status = os.order_status_id

-- 小小练习
SELECT p.date, p.invoice_id, p.amount,
       c.name, pm.name
FROM payments p
JOIN clients c ON p.client_id = c.client_id
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id

复合连接条件

• 使用多个条件连接表格
• JOIN ON 1 AND 2

SELECT *
FROM order_items oi
JOIN order_item_notes oin ON oi.order_id = oin.order_Id
-- 用AND连接更多的条件
    AND oi.product_id = oin.product_id

隐式连接语法

• 不推荐使用隐式连接语法，尽量使用显示语法JOIN ON

-- 以下两种查询结果是一样的
-- 尽量使用显示语法
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id

-- 隐式连接语法，但不推荐使用
-- 使用隐式连接语法时，如果忘记写WHERE了，就会得到交叉连接的结果【笛卡尔积】
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

外连接

• LEFT JOIN
• RIGHT JOIN
• 外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表或右表不满足连接条件的行

-- 内连接
SELECT c.customer_id, c.first_name, o.order_id
FROM  customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- 外连接：LEFT JOIN / 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 / RIGHT JOIN
SELECT c.customer_id, c.first_name, o.order_id
FROM  customers c
-- 右连接的结果和内连接一样。
RIGHT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- 小小练习
SELECT p.product_id, name, quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id;

多表外连接

-- 多表外连接
SELECT c.customer_id, c.first_name, o.order_id, sh.name AS shipper
FROM  customers c
-- o表和c表连，sh表和o表连
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, os.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN order_statuses os ON o.status = os.order_status_id
LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id
ORDER BY status

自外连接

• 一个表用外连接来连接自己

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

USING子句

• 使用USING可以是JOIN ON后的条件更简介
• USING （列名）
• USING关键字只能在不同表中的列名字完全一样的情况下使用

USE sql_store;
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)

-- 使用JOIN ON 写法较为复杂
SELECT *
FROM order_items o
JOIN order_item_notes oin
    ON o.order_id = oin.order_Id AND
        o.product_id = oin.product_id;
-- 同样的查询，使用USING，写法简介
SELECT *
FROM order_items o
JOIN order_item_notes oin
    USING (order_id, product_id);

-- 小小练习
USE sql_invoicing;
SELECT
       p.date,
       c.name AS client,
       p.amount,
       pm.name AS name
FROM payments p
JOIN clients c USING (client_id)
-- 两个表中的列名不一致，不能用USING，只能用ON查询
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id;

自然连接

• 写起来简单，但不建议使用。数据库引擎会自己看着办去连接两个表

USE sql_store;
SELECT *
FROM orders
-- 此时数据库引擎会自己看着办，基于两个表中共同的列连接
NATURAL JOIN customers c

交叉连接

• 和前面的隐式连接语法一样，得到笛卡尔积的结果
• 交叉连接有隐式语法和显示语法
• 显示语法：CROSS JOIN
• 隐式语法，即隐式连接语法，FROM 表1， 表2

-- 显示语法
SELECT c.first_name AS customer,
       p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name;

-- 隐式语法
SELECT c.first_name AS customer,
       p.name AS product
FROM customers c, products p
ORDER BY c.first_name;

-- 小小练习，先隐式，再显示
SELECT *
FROM products, shippers;

SELECT *
FROM products
CROSS JOIN shippers;

联合

• 使用UNION可以合并多段查询的记录
• 也可以基于不同表格写查询，并将结果合并到一个结果集

-- 2019年1月1日之后的订单状态为Active；
-- 之前的订单状态为Archived。用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'

• 注意：被UNION关联的两个子句中，SELECT后查询的列的数量必须要一直，否则会报错，如下图所示。
  

-- 小小练习
SELECT customer_id, first_name, points, 'Bronze' AS type
FROM customers
WHERE points < 2000
UNION
SELECT customer_id, first_name, points, 'Silver' AS type
FROM customers
WHERE points >= 2000 AND points <= 3000
-- WHERE points BETWWEN 2000 AND 3000，写法更简洁
UNION
SELECT customer_id, first_name, points, 'Gold' AS type
FROM customers
WHERE points > 3000
ORDER BY first_name