【第三章】在多张表格中检索数据

【第三章】在多张表格中检索数据

内连接

在SQL中有两种连接，分别为内连接(INNER JOIN)和外连接(OUTER JOIN)，后面会讲外连接，内连接可以省略INNER,默认内连接

SELECT order_id, o.customer_id, first_name, last_name   -- 当列名有歧义时，应制定哪个表
FROM orders AS o   -- AS可以省略
JOIN customers    -- JOIN:将orders表与customers表连接起来
	ON o.customer_id = customers.customer_id

练习：

-- 要求：
-- 对照order_items表,把他和products表连接
-- 每笔订单返回产品id和名字，连同order_items的数量和单价
-- 用别名简化代码

SELECT order_id, oi.product_id, quantity, oi.unit_price
FROM order_items oi
JOIN products p
	ON oi.product_id = p.product_id
-- 要是给了表一个别名，其他所有地方也都要用别名

跨数据库连接

USE sql_inventory;

SELECT *
FROM sql_store.order_items oi   -- 给不在当前数据库的表加前缀
JOIN products p
	ON oi.product_id = p.product_id

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

多表连接

• 三表连接

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

练习：

-- 要求：
-- 将payments和payment_method以及clients表连接

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

复合连接条件

• 当单一列无法位移识别表的特定行时，就可以结合多列识别区分，即有复合主键
• 如何将有复合主键的表与其他表相连接

SELECT *
FROM order_items oi
JOIN order_items_notes oin
	ON oi.order_id = oin.order_id      -- 并列条件，同时满足，主键都要满足相等
    AND oi.product_id = oin.product_id

隐式连接语法

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
LEFT OUTER JOIN orders o   -- 'OUTER'可以省略
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id

• 左连接：这里即为注重customers，不管有没有符合连接条件，customers表的内容都在
• 右连接：即注重orders表，不管有没有符合连接条件，orders表的内容都在

练习：

-- 查询product_id, name, order_items里的quantity列
-- 即连接product表，和order_items表
-- 无论产品是否有售卖过，都要查询
-- 即为外连接

SELECT 
	p.product_id,
    p.name,
    oi.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
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 AS customer,
	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
JOIN order_statuses os
	ON o.status = os.order_status_id

自外连接

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

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关键字简化
	USING (customer_id)
LEFT JOIN shippers sh
	USING (shipper_id)

• 当有复合键时，连接条件需要同时满足列相等

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)	

练习：

要求结果：

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

自然连接

• 自然连接，系统自己会基于相同的列连接，但不太推荐使用自然连接，可能会有意料外的结果

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

交叉连接

• 交叉连接：多对多连接

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

练习：

-- 要求:Do a cross join between shippers and products
-- 			using the implicit syntax
-- 			and then using the explicit syntax

-- 隐式语法
SELECT
	sh.name AS shipper,
	p.name AS product
FROM shippers sh, products p

-- 显式语法
SELECT
	sh.name AS shipper,
	p.name AS product
FROM shippers sh
CROSS JOIN products p

联合

• 通过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  -- 查询结果列名为'first_name'，而非name
FROM customers
UNION 
SELECT name
FROM shippers

练习：

查询结果：

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, 
	'Silver' AS type
FROM customers
WHERE points > 3000
UNION
SELECT 
	customer_id, 
	first_name, 
	points, 
	'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
ORDER BY first_name