1.2 多表格检索查询

目录

1. Inner Join内连接

【练习】写一段SQL，对照order_items表，与products表进行连接。

2. Joining Across Databases跨数据库连接

3. Self Join自连接

4. Joining Multiple Tables多表连接

【练习】写一段SQL，其中sql_invocing库中的四个表彼此之间有联系，写一个报表，显示付款和更多详细信息（顾客姓名、付款方式）。

5. Compound Join Conditions 复合连接条件

6. Implicit Join Syntax隐式连接语法

 7. Outer Joins外连接

（1）LEFT JOIN左连接

（2）RIGHT JOIN右连接

【练习】写一段SQL，返回product_id、name和quantity三列信息。

8. Outer Joins Between Multiple Tables多表外连接

【练习】写一段SQL，要求返回order_data，order_id，first_name，shipper和status。

9. Self Outer joins自外连接

10.USING语句

【练习】写一段SQL，从库sql_invocing，将表payments和表payment_methods连接起来。

11. Natural Joins自然连接

12. Cross Joins 交叉连接

【练习】写一段SQL，使用显式写法和隐式写法，将shippers表和products表连接起来。

13. Unions联合

【练习】写一段SQL，要求将积分<2000，将其标签设置为Bronze，2000<=积分<3000设置为Silver，积分>=3000则为Gold。

---

1. Inner Join内连接

如图所示，为【orders】表中的信息，其中顾客是以customer_id进行表示，具体顾客信息存储于【customer】表格中。现在，需要选取【orders】表的全部信息以及【customer】表中的name列。

注意：INNER可有可无。

这里【orders】表和【customer】表之间的联系是customer_id，则有。

SELECT *
	FROM orders
	INNER JOIN customers 
		ON orders.customer_id = customers.customer_id

表示【orders】中的customer_id与【customer】表中的customer_id要一一对应才能连接。

注：当多个表格中拥有相同的列时，应该加表格前缀，如customers.customer_id。

【练习】写一段SQL，对照order_items表，与products表进行连接。

SELECT order_id, o.product_id, quantity, o.unit_price 
	FROM order_items o
    INNER JOIN products p
		ON o.product_id = p.product_id

 

其中o和p是order_items和products的缩写。

2. Joining Across Databases跨数据库连接

现实生活中，如果从事开发者or数据库管理者工作时，经常会使用到多个数据库，因此需要将分散于不同数据库中的表的列合并起来。

数据库【sql_inventory】与数据库【sql_store】拥有相同的表products，并且其内容完全一致（❎其实这是错误的示范，我们不希望出现同样的表）。

要求，将【sql_inventory】中的products与【sql_store】中的order_items连接到一起。

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

3. Self Join自连接

SQL语言中可以将一张表与自己连接。如下图所示：

员工33391的领导为37270，同时37270也是一名员工，可以通过写一个SQL将该表与自己进行连接。

USE sql_hr;
SELECT *
	FROM employees e -- 给员工起名为表e
    JOIN employees m -- 给领导起名为表m
		ON e.reports_to = m.employee_id;

可以看到前面是员工的名字，后面是领导的名字及相关信息。

也就是说，可以根据此轻松创建组织架构图。

4. Joining Multiple Tables多表连接

如图所示：

表【order_statuses】中的order_statuses_id对应表【order】中的status，而表【orders】中的customer_id对应表【customers】中的customer_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;

【练习】写一段SQL，其中sql_invocing库中的四个表彼此之间有联系，写一个报表，显示付款和更多详细信息（顾客姓名、付款方式）。

如图所示为库sql_invocing中表的关系，则有：

USE sql_invoicing;
SELECT
	p.date,
    p.invoice_id,
    p.amount,
	c.name,
	pm.name
	FROM payments p
		JOIN payment_methods pm
			ON p.payment_method = pm.payment_method_id
		JOIN clients c
			ON p.client_id = c.client_id
		JOIN invoices i
			ON p.invoice_id = i.invoice_id

5. Compound Join Conditions 复合连接条件

在对多表进行连接的过程中，可能会出现无法用单一列来准确识别表中信息的情况，如下图所示为表【order_items】：

出现了order_id中有多个2或4，products_id也有多个重复的值，因此可以用这两列一起唯一识别每一个订单项目。

可以打开该表的设计模式，如下图所示：

可看到order_id和products_id是主键，这被称为复合主键。

现在，要求将【order_items】与表【order_item_notes】连接起来。

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;

6. Implicit Join Syntax隐式连接语法

已知如下代码为Inner Join内连接

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时，会造成交叉俩环节的情况。建议写显式连接语法。

 7. Outer Joins外连接

运行以下代码，

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

可以看到，一个顾客可能会购买多个物品产生订单号，但是并不是所有的顾客都进行了消费，因此这里只能看到顾客2、5、6、7、8、10，而没有1、3等等。我们希望看到所有顾客的信息，而不仅仅是产生了消费的顾客信息。此时就可以使用外连接进行操作。

观察代码可知，内连接只返回符合c.customer_id = o.customer_id的结果，因为有的顾客没有消费，所以无法检索到相应的结果。

外连接有两种方式，即左连接和右连接。

（1）LEFT JOIN左连接

在使用左连接的时候，左边表（customers表）的信息会被全部返回，不论条件是否正确。

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_id为NULL。

（2）RIGHT JOIN右连接

在使用右连接的时候，右边表（orders表）的信息会被全部返回，无论条件是否正确。

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

注：可以在LEFT和RIGHT后插入OUTER，与INNER一样可以省略。

【练习】写一段SQL，返回product_id、name和quantity三列信息。

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

8. Outer Joins Between Multiple Tables多表外连接

在多个表中也可以进行外连接，如图所示：

在把表【customers】与表【orders】连接的过程中，可观察到表【orders】中的shippers_id有缺失值，通过外连接将表【shippers】连入。

SELECT
	c.customer_id,
    c.first_name,
    o.order_id,
    s.shipper_id
	FROM customers c
		LEFT JOIN orders o
			ON c.customer_id = o.customer_id
		LEFT JOIN shippers s
			ON o.shipper_id = s.shipper_id;

可见所有的信息均已返回。

注：一般避免使用右连接，因为当连接多表时，同时使用左连接、右连接及内连接会变得非常复杂，可读性查。

【练习】写一段SQL，要求返回order_data，order_id，first_name，shipper和status。

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

9. Self Outer joins自外连接

在标题3Self Join自连接内容中可知，将员工与领导进行关联时，只能返回有“reports_to”的人，但是当“reports_to”为None时，则显示不全，因此可使用Self Outer Joins进行自外连接。

Select 
	e.employee_id,
    e.first_name,
    m.first_name As manager
	FROM employees e
    LEFT Join employees m
		ON m.employee_id = e.reports_to

10.USING语句

USE sql_store;

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

如上所示是一个内连接，当查询条件很多时，如此写法会十分复杂，因此可以使用一个更加简洁的USING语句替代。

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

注：USING只适用于表不同，列相同的情况。

如图所示，当表为复合主键时才能唯一检索一条信息，因此该表的USING语法使用方式如下所示。

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

【练习】写一段SQL，从库sql_invocing，将表payments和表payment_methods连接起来。

USE sql_invoicing;

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

11. Natural Joins自然连接

自然连接是更加简单的表连接方式，但是不建议使用。

USE sql_store;

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

根据以上可知，使用Natural进行连接时，无需指定列，而是让数据库引擎自己进行判断如何连接，无法控制，故不推荐使用。

12. 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;

这是一种隐式写法。

【练习】写一段SQL，使用显式写法和隐式写法，将shippers表和products表连接起来。

-- 显式写法
SELECT *
	FROM shippers s
    CROSS JOIN products p;
    
-- 隐式写法
SELECT *
	FROM shippers s , products p;

13. Unions联合

该方法可以将不同表之间的行进行结合.

观察表【orders】，要求当order_date在2019-01-01后的，标注为'Active'，在之前的标注为'Archived'。

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可以将不同的查询结果进行合并。

注：UNION不仅可以合并同一个表的查询结果，也可以合并不同表的查询结果。

【练习】写一段SQL，要求将积分<2000，将其标签设置为Bronze，2000<=积分<3000设置为Silver，积分>=3000则为Gold。

SELECT 
	c.customer_id,
    c.first_name,
    c.points,
    'Bronze' AS type
	FROM customers c
    WHERE c.points < 2000
UNION
SELECT 
	c.customer_id,
    c.first_name,
    c.points,
    'Silver' AS type
	FROM customers c
    WHERE c.points >= 2000 AND c.points < 3000 -- WHERE c.points BETWEEN 2000 AND 3000
UNION
SELECT 
	c.customer_id,
    c.first_name,
    c.points,
    'Gold' AS type
	FROM customers c
    WHERE c.points >= 3000

注意：

1）左联结（left join），联结结果保留左表的全部数据

2）右联结（right join），联结结果保留右表的全部数据

3）内联结（inner join），取两表的公共数据