SQL语言笔记 第二章 多表连接查询 JOIN

使用的是MySQL数据库

1. 内连接 INNER JOIN 同数据库中多表联合查询

-- 内连接是显示连接语法
-- INNER 关键字可写可不写
-- 连接多表列，结果显示两张连在一起的表，orders表在前customers表在后
SELECT *
FROM orders
JOIN customers
    ON orders.customer_id = customers.customer_id;
    
-- SELECT 子句中 如果有列不明确的列（两个表中都有的列），需要加上前缀
SELECT order_id,o.customer_id,first_name,last_name
-- 给表起别名 在表名后面空格加别名，则其他需要加表名前缀的都可以写成表别名
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id;
    
 -- 练习：用order_items表连接products表查询订单id,商品id,商品件数，单价 
 -- *注意order_items订单列表和product商品列表中的unit_price可能存在差异，所以要注意到底需要哪个
SELECT order_id,oi.product_id,quantity,oi.unit_price
FROM order_items oi
JOIN products p
    ON oi.product_id = p.product_id;

2. 跨数据库连接 多个数据库的表中的列合并起来

-- 要给不在当前数据库的表加前缀，不管是被连接的表还是要连接的表
-- 当前使用的表是sql_store，所以order_items不需要加前缀
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
    ON oi.product_id = p.product_id;

3. 自连接

-- 自连接的表需要起不同的别名
-- 由于自己连接自己，最后显示的也是两个表列的合并，因此SELECT子句中的列选项要做优化
-- 每个列也要加前缀，或者起别名，用以优化查询结果

USE sql_hr;
-- 查找employees表中所有员工的管理者，每一条数据包括员工和对应管理者
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;

4. 多表连接

-- 查询订单id 订单日期 顾客first name 顾客last name 订单状态 连接三张表 orders customers order_statuses
SELECT 
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS 
FROM orders o
-- 通过各表之间相互存在的唯一标识符进行JOIN连接，实现多表连接查询
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id;

5. 复合连接条件

-- 如一个列表中需要另外两个及以上的表确定一个项目
-- 则这个表就会超过两个及以上的主键，称之为复合主键
-- order_items表中的每一列都是由order_id和product_id两个键组合指定的项目，order_item_notes表中也有这两个键
SELECT *
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id

6. 隐式连接语法 不建议使用 如果忘记写WHERE子句得到的是交叉数据

-- 内连接
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;

7. 外连接

• 外连接有两种，左连接LEFT OUTER JOIN；右连接RIGHT OUTER JOIN; OUTER关键字可以省略。
• 使用左连接，所有左表的查询记录会被返回，不管条件正确与否；
• 使用右连接，所有右表的查询记录会被返回，不管条件正确与否。

SELECT
      c.customer_id,
      c.first_name,
      o.order_id
FROM customers c
-- 左外连接，此时customers表为左表，此时所有坐标的customer_id和first_name都显示，不管条件正确与否
-- 右外连接，此时orders表为右表，右表所有的order_id都显示出来，不管条件正确与否
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

8. 多表外连接 统一外连接，要么都左连，要么都右连

-- 显示所有customers表中的数据，并且显示每位顾客对应的订单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
-- orders左外连接shippers
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
ORDER BY customer_id;

-- 练习：获取订单表的订单日期 订单id 顾客表的顾客first name 物流表的物流人员 订单状态表的订单状态
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 o.customer_id = c.customer_id
-- 不是所有订单都有物流，但没有物流也要显示，因此使用左连接
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
-- 每个订单都有状态，无物流是1，有物流是2，因此使用内连接即可
JOIN order_statuses os
    ON o.status = os.order_status_id
ORDER BY status;

9. 自外连接

-- 以自连接为例，自连接中实现了获取员工和对应管理者，但管理者自身没有显示在输出中
-- 这时候可以使用左外连接实现所有左表记录都获取
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;

10. USING 子句

• 注意：使用USING子句，必须两个表中的列相同才可以使用；否则就用ON。

-- 如果连接的ON子句的两个表的列名称相同，可以替换成USING子句
SELECT
    o.order_id,
    c.first_name,
    sh.name
FROM orders o
JOIN customers c
-- 使用ON子句添加条件连接两表
    -- ON o.customer_id = c.customer_id;
-- 当这两个表的列名称相同时，用USING子句替代ON子句
   USING (customer_id)
JOIN shippers sh
   USING (shipper_id);

-- 使用USING子句查询有复合主键的表
-- order_item_notes表是由order_id和product_id组成的复合主键
-- 连接order_items，显示note_id 数量 单价
SELECT
    oin.note_id,
    oi.quantity,
    oi.unit_price
FROM order_items oi
JOIN order_item_notes oin
    USING (order_id,product_id);

11. 自然连接 NATIONAL JOIN 不建议使用

• 使用自然连接的两个表，数据库引擎会自己根据相同名称的列进行查询，我们无法控制。

-- orders表和customers表中都有相同的列名称customer_id
SELECT
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

12. 交叉连接 CROSS JOIN

• 第一个表的每条记录都会跟连接的表的每条记录结合，显示的数据量是N*N。
• 使用场景：如商品型号和商品颜色，可以使用交叉连接。

-- 交叉连接有显示语法和隐式语法
-- 连接customers表和products表，交叉显示每位顾客可以对应每件商品有哪些情况
SELECT
    c.first_name AS customer,
    p.name AS product
-- 交叉连接的显示语法 推荐使用 更清晰
-- FROM customers c
-- CROSS JOIN products p
-- 交叉连接的隐式语法 将交叉连接的表直接写在FROM后面
FROM customers c,products p
ORDER BY customer;

13. 联合 UNION

• 基于相同表或不同表写查询，并将结果合并到一个结果集。
• 联合使用不多，使用场景：查看所有存档订单和活跃订单记录。
• 注意：(1) 查询返回的列的数量一定要一样，否则会得到错误提示；
  (2) 基于不同表查询，不同表的列名称可能不同，而返回结果集的列名称显示的是第一张表的列名称，因此可以给第一个表查询的列名称起一个别名；
  (3) 使用UNION，并且还想对结果集进行排序，只需要在最后写上ORDER BY即可对整个结果集排序。

-- 同一张表查询
-- 2019-01-01之后的订单状态标为Active(活跃)，之前的订单标为Archived(存档)，将查询结果显示在一个结果集中
SELECT
    order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
-- 若不使用UNION，出来的查询是分开的两张表
UNION
SELECT
    order_id,
    order_date,
    'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'

-- 不同表查询 哪张表放在前面，结果集的列名称就显示它；如果shipers表放在前面，结果集列名称显示的就是name
SELECT first_name AS full_name
FROM customers
UNION
SELECT name
FROM shippers

-- 练习
-- 使用customers表 积分小于2000，类型是Bronze（青铜）
-- 积分在2000到3000之间，是Silver（白银）
-- 积分超过3000，是Gold（黄金）
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 BETWEEN 2000 AND 3000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers
WHERE points > 3000
ORDER BY first_name