跟Mosh老师学SQL的第二天 - 各种Join

元元的成长之路
178 阅读8分钟

第一部分：Inner Joins - 内连接

USE sql_store;

SELECT *
FROM orders
INNER JOIN customers 
    ON orders.customer_id = customers.customer_id
--- Inner在这里不一定需要写出来
--- 如果想把订单order表和顾客表customer连接起来
--- 首先需要思考：我们基于什么来连接这些表？
--- 在customers表中，有customer_id列，在orders表中，也有customer_id列
--- 所以如果想联合记录，使得customer id相等即可
--- 在这里可以使用ON短语，ON后面需要输入一个condition条件

错误写法举例：
USE sql_store;

SELECT order_id, customer_id, first_name, last_name
FROM orders
INNER JOIN customers 
    ON orders.customer_id = customers.customer_id
--- 这里的写法MySQL会报错“列不明确”。
--- 因为orders和customers两表中都有以customer_id命名的列，所以MySQl无法确定我们想从那张表里来选取这一列，所以应该改成下面的写法：
--- 当有多张列表有一样的列，在列名之前加上表格名称作为前缀让这列可用

----------------------------------------------------
正确写法举例：
USE sql_store;

SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
INNER JOIN customers 
    ON orders.customer_id = customers.customer_id
--- 这里我们写orders.customer_id和customers.customer_id都是对的
--- 因为在ON的条件中我们可以看到这两个值是相等的

----------------------------------------------------
正确使用别名，让SQL语句看起来更简洁(别名的as可以省略)
USE sql_store;

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
INNER JOIN customers c
    ON o.customer_id = c.customer_id

EXERCISE：

Part1 - Inner Joins 内连接
-- 写一段查询，把order_items表和products表连接起来

-- order_items: order_id, product_id, quantity, unit_price
-- products: product_id, name, quantity_in_stock, unit_price
-- 每笔订单都返回product_id, name, order_items表中的quantity and unit_price
-- 用别名来简化代码

SOLUTION:

USE sql_store;

SELECT p.product_id, name, quantity, oi.unit_price
FROM products p
INNER JOIN order_items oi
    ON p.product_id = oi.product_id

第二部分：Joining Across Database - 跨数据库连接

USE sql_store;

SELECT *
FROM order_items oi
JOIN sql_inventory.products p
    ON oi.product_id = p.product_id
--- 在这里需要注意的是，我们把数据库的名字放在了products表的前面
--- 因为当前这段查询的数据库是sql_store的数据库（即在MySQL中这个地方是黑色加粗）
--- 除了点击以外，可以选中数据库的方法是邮件Set as Default Schema
--- 或者在SQL代码的最上面写上USE sql_store;

第三部分：Self Joins - 自连接

为了寻找manager
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

第四部分：Joinging Multiple Tables - 多表连接

USE sql_store;

SELECT *
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 
	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

EXERCISE：

Part4 - Joinging Multiple Tables - 多表连接
-- 在sql_invoicing中的payments表中
-- payment_id, client_id, invoice_id, date, amount, payment_method
-- 可以连接这张表和client表，来查看客户的姓名
-- 也可以连接这张表和payment_methods表

-- 写一段查询，把这张表和payment methods表以及clients表连接
-- 生成一份报告，显示付款和更多详析信息
-- 比如客户姓名，付款方式

SOLUTION:

USE sql_invoicing;

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

第五部分：Compound Join Conditions - 复合连接条件

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
--- 使用多个条件连接这两个表格
--- 在order_items中，order_id和product_id是这张表的复合主键

第六部分：Implicit Join Syntax - 隐式连接语法

基本款式：
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语句，那么查询的记录数量会从10变成100
-- 这是由于orders的每条记录都和customers里的每条记录连接了

第七部分：Outer Joins - 外连接

Join分为Inner Join和Outer Join，当我们使用Join时，都默认指Inner Join

写法一：
SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
ORDER BY c.customer_id
--- 目前的情况下，换一种语句顺序完全不会影响查询结果，例如，改换为如下的语句顺序 ---
写法二：
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
--- 以上的查询得到的结果是有缺失的，查询的结果中仅仅包含了目前有订单记录的customer id
--- 但是在customers表中我们可以看到，还有一些其他的顾客并没有被查询包含其中
--- 但是如果想看到所有的customer（无论是否有订单），我们该怎么做？

--- 解决问题的办法就是：Outer Joins ---
--- 分析过程：
--- 上述查询只能看到有订单的customer的原因是：
--- 是因为ON后的连接条件，连接orders和customers这两张表时，查询仅仅返回了符合这个条件的记录
--- 可以回想一下数学中交集和并集的概念
--- SQL中存在两种不同类型的Outer Joins：LEFT JOINS and RIGHT JOINS

下例为使用LEFT 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的记录，会被全部返回，而不论这些记录是否符合ON后的条件语句
--- 所以如果使用LEFT JOIN，我们就会在查询结果中得到所有的customer
--- 如果他们有订单，我们就可以看到order id

查询结果如下图所示，没有order的customer_id也会显示，单元格的值会为空
![left join.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/b7a7493505df45e29d95ba42dc4afbed~tplv-k3u1fbpfcp-watermark.image?)

下例为使用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
--- 当使用RIGHT JOIN时，也就是本例中的orders的记录，会被全部返回，而不论这些记录是否符合ON后的条件语句
--- 所以下图的查询结果中我们并没有看到所有顾客，而是看到了所有的订单

![right join.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/739dd100a2884dd5acb189b37f03ee57~tplv-k3u1fbpfcp-watermark.image?)

EXERCISE：

Part7 - Outer Joins - 外连接
-- 写一段查询，连接products和order_items两个表
-- 能看到每个产品被订购了多少次
-- 在Inner Join中我们只能看到有订单的产品
-- 外连接的要求是：所以练习的结果中，可以看到产品7一次也没有被订购过，也在结果中
-- 查询的结果为三列：product_id, name, quantity

SOLUTION:

USE sql_store;

SELECT
    p.product_id,
    p.name,
    oi.quantity
FROM products p
LEFT JOIN order_items oi
    ON p.product_id = oi.product_id

第八部分：Outer Joins Between Multiple Tables - 多表外连接

--- 这里我们使用Part7中Left Join的查询作为例子
SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
--- 保留所有customer, 无论是否有订单
    ON c.customer_id = o.customer_id
JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
--- 让orders表和shippers表做内连接
ORDER BY c.customer_id

--- 目前的想法是：有些订单有shipper_id，指已经发货的订单
--- Join orders表和shippers表，让发货人的名字出现在查询结果中

--- 但上述的语句，查询的结果仅仅有5条记录，但是应该有更多订单才对
--- 有些订单没有发货人，所以它们没有被返回
--- ON o.shipper_id = sh.shipper_id 也就是说，这个连接条件不符合部分订单
--- 但是我们需要所有的订单都被返回，即是它们中的有些并没有发货
--- 所以在这里并不能 让orders表和shippers表做内连接

正确的写法如下：
SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customers c
LEFT JOIN orders o
--- 保留所有customer, 无论是否有订单
    ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

--- 当连接多表时，最好还是避免使用RIGHT JOIN

EXERCISE：

Part8 - Outer Joins Between Multiple Tables - 多表外连接
-- 查询的结果为五列：order_date, order_id, first_name, shipper, status
-- 其中可以看到有些订单还没有发货，所以shipper这一列有些值为空

SOLUTION:

USE sql_store;

SELECT
    o.order_date,
    o.order_id,
    c.first_name,
    sh.name AS shipper,
    ost.name AS status
FROM orders o
LEFT JOIN customers c
-- Mosh说这里用内连接也是可以的
-- 原因是：因为每笔订单都有顾客
-- 所以 ON o.customer_id = c.customer_id 这个条件总是有效的
    ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
-- 这里就必须使用LEFT JOIN，如果使用内连接
-- 那么那些没有发货人的订单就无法被显示在查询结果中
LEFT JOIN order_statuses ost
    ON o.status = ost.order_status_id

第九部分：Self Outer Joins - 自外连接

USE sql_hr;

SELECT
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
JOIN employees m
-- m 指代managers
    ON e.reports_to = m.employee_id

-- 但是如上的查询语句无法看到manager的employee_id
-- 因为内连接和ON后的条件，导致查询只会返回有管理人员的人
-- 我们可以使用LEFT JOIN解决这个问题
-- 意即我们想得到employee表中的每位员工，不管他们有没有对应的管理人员

正确写法:
USE sql_hr;

SELECT
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m
-- m 指代managers
    ON e.reports_to = m.employee_id

第十部分：The USING Clause - USING子句

USE sql_store;

SELECT
    o.order_id,
    c.first_name
FROM orders o
JOIN customer c
    ON o.customer_id = c.customer_id
--- 对于条件中的两个表 涉及到的列 名称完全一致时
--- MySQL有一个强大的功能来简化这些查询
--- 就可以使用更简洁的USING子句替换ON子句

替换写法：
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)

--- 但是我们不能在连接以上的查询结果 和 order_statuses表时 使用这个技巧
--- 因为orders表中有叫”status“的一列，但是在order_statuses表里的名字叫做“order_status_id”
--- 所以USING关键词只能在不同表中的列名完全一样的情况下使用

对于有复合主键的表 连接时 使用USING子句的例子

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 order_items oi
JOIN order_item_notes oin
    USING (order_id, product_id)

EXERCISE：

Part10 - The USING Clause - USING子句
-- USE sql_invoicing
-- 查询的结果为四列：date, client, amount, name
-- 写一段查询，从payments里

SOLUTION:

USE sql_invoicing;

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

第十一部分：Natural Join - 自然连接

本方法不建议使用
-------------
USE sql_store;

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

-- 通过使用Natural Join，我们就不打上具体列名列
-- 数据库引擎会自己看着办，基于共同的列连接
-- 也就是有相同名称的列，因此这个查询能这么短

第十二部分：Cross Joins - 交叉连接

交叉连接结合或者连接第一个表的每条记录和第二个表的每条记录

交叉连接的显式语法：
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

EXERCISE：

Part12 - Cross Joins - 交叉连接
-- Do a cross join between shippers and products
-- using the implicit syntax
-- and then using the explicit syntx

SOLUTION_隐式语法:

SELECT
    sh.name AS shipper,
    p.name AS product
FROM shippers sh, products p
ORDER BY sh.name

SOLUTION_显式语法:

SELECT
    sh.name AS shipper,
    p.name AS product
FROM shippers sh
CROSS JOIN products p
ORDER BY sh.name

第十三部分：Unions - 交叉连接

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 first_name, last_name
FROM customers
UNION
SELECT name
FROM shippers
--- 执行查询的时候就会得到错误提示
--- 因为第一段查询返回两列
--- 但是第二段查询返回一列

SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers
--- 第一段查询返回了first_name
--- 查询的结果的列名是基于第一段查询的结果的：first_name

EXERCISE：

Part13 - Unions - 交叉连接
-- 查询结果为customer_id, first_name, points, type四列
-- 根据每位顾客的point
-- 如果他们的积分小于2000，类型则为Bronze
-- 如果他们的积分在2000到3000之间，就是Silver
-- 如果他们的积分超过3000，类型为Gold

SOLUTION:

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
avatar
文章
阅读
粉丝