Sql多张表中检索数据_sql在a表中查找b表里对应的信息数据

1.内连接

各表分开存放是为了减少重复信息和方便修改，需要时可以根据相互之间的关系连接成相应的合并详情表以满足相应的查询。FROM JOIN ON 语句就是告诉sql： 将哪几张表以什么基础连接/合并起来。

select *
from order 
inner join customers
	on	order.id = customers.id;
	
-- inner可省略
-- 可起别名

select 
	order_id,
	customer_id,
	o.id,
	last_name
from order o
join customers c
	on	o.id = c.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.跨数据库连接

有时需要选取不同库的表的列，其他都一样，就只是WHERE JOIN里对于非现在正在用的库的表要加上库名前缀而已。依然可用别名来简化

use sql_store;

select * from order_items oi
join sql_inventory.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
    
-- 非当前使用的库，才需要加库前缀
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.自连接

一个表和它自己合并。如员工的上级也是员工，所以也在员工表里，所以想得到的有员工和他的上级信息的合并表，就要员工表自己和自己合并，用两个不同的表别名即可实现。这个例子中只有两级，但也可用类似的方法构建多层级的组织结构。

USE sql_hr

select 
	e.employee_id,
	e.first_name,
	m.first_name as manger
from
	employees e
join employee m
	on e.reports_to = m.employee_id
1
2
3
4
5
6
7
8
9
10

4. 多表连接

FROM 一个核心表A，用多个 JOIN …… ON …… 分别通过不同的链接关系链接不同的表B、C、D……，通常是让表B、C、D……为表A提供更详细的信息从而合并为一张详情合并版A表，即：

FROM  A 
    JOIN B ON AB的关系 
    JOIN C ON AC的关系 
    JOIN D ON AD的关系 
    ……
1
2
3
4
5

将得到一个合并了BCD……等表详细信息的详情合并版A表

真实工作场景中有时甚至要合并十多张表

-- 订单表同时链接顾客表和订单状态表，合并为有顾客和状态信息的详细订单表

USE sql_store;

SELECT 
	o.order_id,
	o.order_date,
	c.first_name,
	c.last_name,
	os.name AS status
FROM	order o
JOIN	customers c
	on	o.customers_id = c.customers_id
JOIN	order_statuses os
    ON o.status = os.order_status_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5.复合连接条件

像订单项目（order_items）这种表，订单id和产品id合在一起才能唯一表示一条记录，这叫复合主键，设计模式下也可以看到两个字段都有PK标识，订单项目备注表（order_item_notes）也是这两个复合主键，因此他们两合并时要用复合条件：FROM 表1 JOIN 表2 ON 条件1 【AND】 条件2

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
1
2
3
4
5
6
7

6.隐式连接语法

就是用FROM WHERE取代FROM JOIN ON

尽量别用，因为若忘记WHERE条件筛选语句，不会报错但会得到交叉合并（cross join）结果：即10条order会分别与10个customer结合，得到100条记录。最好使用显性合并语法，因为会强制要求你写合并条件ON语句，不至于漏掉。

USE sql_store;

SELECT *
FROM orders o
JOIN customers c
	on o.id = c.id;
	
-- 可转换为下面这个
SELECT *
FROM orders 
1
2
3
4
5
6
7
8
9
10

7.外连接

(INNER) JOIN只包含两个表的交集

LEFT/RIGHT (OUTER) JOIN 结果里除了交集，还包含只出现在左/右表中的记录

-- 合并顾客表和订单表，用INNER JOIN
USE sql_store;

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

-- 这样是INNER JOIN，只展示有订单的顾客（及其订单），也就是两张表的交集，但注意这里因为一个顾客可能有多个订单，所以INNER JOIN以后顾客信息其实是是广播了的，即一条顾客信息被多条订单记录共用，当然 这叫广播（broadcast）效应，是另一个问题，这里关注的重点是 INNER JOIN 的结果确实是两表的交集，是那些同时有顾客信息和订单信息的记录。

-- 若要展示全部顾客（及其订单，如果有的话），要改用LEFT (OUTER) JOIN，结果相较于 INNER JOIN 多了没有订单的那些顾客，即只有顾客信息没有订单信息的记录

-- 当然，也可以调换左右表的顺序（即调换FROM和JOIN的对象）再 RIGHT JOIN，即：
FROM orders o
    RIGHT [OUTER] JOIN customers c
    -- 中括号 [] 表示是可选项、可省略 
    ON o.customer_id = c.customer_id
    
-- 若要展示全部订单（及其顾客），就应该是 orders RIGHT JOIN customers，结果相较于 INNER JOIN 多了没有顾客的那些订单，即只有订单信息没有顾客信息的记录。（注：因为这里所有订单都有顾客，所以这里 RIGHT JOIN 结果和 INNER JOIN 一样）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

8.多表外连接

与内连接类似，我们可以对多个表（3个及以上）进行外连接，最好只用 JOIN 和 LEFT JOIN

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 customer_id

-- 虽然可以调换顺序并用 RIGHT JOIN，但作为最佳实践，最好调整顺序并统一只用 [INNER] JOIN 和 LEFT [OUTER] JOIN（总是左表全包含），这样，当要合并的表比较多时才方便书写和理解而不易混乱
1
2
3
4
5
6
7
8
9
10
11
12
13

9.自外部连接

就用前面那个员工表的例子来说，就是用LEFT JOIN让得到的 员工-上级 合并表也包括老板本人（老板没有上级，即 reports_to 字段为空，如果用 JOIN 会被筛掉，用 LEFT JOIN 才能保留）

USE sql_hr;

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m  -- 包含所有雇员（包括没有report_to的老板本人）
    ON e.reports_to = m.employee_id
1
2
3
4
5
6
7
8
9

10. USING子句

当作为合并条件（join condition）的列在两个表中有相同的列名时，可用 USING (……, ……) 取代 ON …… AND …… 予以简化，内/外链接均可如此简化。

一定注意 USING 后接的是括号，特容易搞忘

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 BY order_id
-- 复合主键表间复合连接条件的合并也可用 USING，中间逗号隔开就行：

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)
-- USING对复合主键的简化效果更加明显
-- 列名不同就必须用 ON …… 了
-- 实际中同一个字段在不同表列名不同的情况也很常见，不能想当然的用USING
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

11自然连接

NATURAL JOIN 就是让MySQL自动检索同名列作为合并条件。

最好别用，因为不确定合并条件是否找对了，有时会造成无法预料的问题，编程时保持对结果的控制是非常重要的

但也要知道有这个东西，混个脸熟，不要别人用了看不懂。

USE sql_store;

SELECT 
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c
1
2
3
4
5
6
7

12. 交叉连接

得到名字和产品的所有组合，因此不需要合并条件。 实际运用如：要得到尺寸和颜色的全部组合

-- 得到顾客和产品的全部组合（毫无意义，纯粹为了展示交叉连接）

USE sql_store;

SELECT 
    c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
-- 上面是显性语法，还有隐式语法，之前讲过，其实就是隐式内合并忽略WHERE子句（即合并条件）的情况，也就是把 CROSS JOIN 改为逗号，即 FROM A CROSS JOIN B 等效于 FROM A, B，Mosh更推荐显式语法，因为更清晰

USE sql_store;

SELECT 
    c.first_name,
    p.name
FROM customers c, products p
ORDER BY c.first_name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

13.联合

FROM …… JOIN …… 可对多张表进行横向列合并，而 …… UNION …… 可用来按行纵向合并多个查询结果，这些查询结果可能来自相同或不同的表

• 同一张表可通过UNION添加新的分类字段，即先通过分类查询并添加新的分类字段再UNION合并为带分类字段的新表。
• 不同表通过UNION合并的情况如：将一张18年的订单表和19年的订单表纵向合并起来在一张表里展示

注意

• 合并的查询结果必须列数相等，否则会报错
• 合并表里的列名由排在 UNION 前面的决定

-- 给订单表增加一个新字段——status，用以区分今年的订单和今年以前的订单

USE sql_store;

    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  -- Archived 归档
    FROM orders
    WHERE order_date < '2019-01-01';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19