多表查询
- 多表查询
- 多表查询核心概念
- 多表查询 SQL 示例
- INNER JOIN
- LEFT JOIN
- LEFT JOIN EXCLUDING INNER JOIN
- RIGHT JOIN
- RIGHT JOIN EXCLUDING INNER JOIN
- FULL OUTER JOIN
- FULL OUTER JOIN EXCLUDING INNER JOIN
- 更多的表
- 排序、分页
- 子查询
Java 从 0 到架构师目录:【Java从0到架构师】学习记录
多表查询
内连接:INNER JOIN、CROSS JOIN、JOIN
在 MySQL 中,上述内连接等价;但在标准 SQL 中,它们并不等价
外连接:LEFT [OUTER] JOIN、RIGHT [OUTER] JOIN
并集:UNION
MySQL 并不支持标准 SQL 的 FULL [OUTER] JOIN,但可以用 UNION 来替代实现
参考教程:【官方教程】JOIN语法
ON 和 WHERE 后面都可以跟着条件,它们的区别是:
- ON:配合 JOIN 语句使用,用以指定如何连接表的条件
- WHERE:限制哪些记录出现在结果集中
INNER JOIN 和 逗号, 在没有连接条件的情况下,语义上是等价的
- 都在指定的表之间产生 笛卡儿乘积 (Cartesian Product)
- 也就是说,第一个表中的每一行都连接到第二个表中的每一行
多表查询核心概念
LEFT JOIN 对应的 ON 条件其实就是两个表共同的部分
多表查询 SQL 示例
目前有两张表:
- customer:顾客信息表
- company:公司信息表
有顾客的公司信息(附带顾客信息) = 有公司的顾客信息(附带公司信息)
INNER JOIN
内连接:查询出所有有公司信息的顾客信息(附带公司信息)
- 等价于 所有有顾客信息的公司信息(附带顾客信息)
MySQL 中 INNER JON 和 JOIN 查询结果是一样的,至于有无其他区别,希望有大佬告知
# l ∩ r
SELECT * FROM
customer l
INNER JOIN
company r
ON
l.company_id = r.id;
下面的写法等价于上面的内连接:
SELECT * FROM
customer l, company r
WHERE l.company_id = r.id;
LEFT JOIN
左外连接:查询出所有有公司和没有公司的顾客信息(附带公司信息)
# l ∪ (l ∩ r)
SELECT * FROM
customer l
LEFT JOIN
company r
ON
l.company_id = r.id;
LEFT JOIN EXCLUDING INNER JOIN
有 WHERE 的左外连接:查询出所有没有公司的顾客信息(附带公司信息)
- 排除了有公司的顾客信息(附带公司信息)
# l - (l ∩ r)
SELECT * FROM
customer l
LEFT JOIN
company r
ON
l.company_id = r.id
WHERE
r.id IS NULL;
RIGHT JOIN
右外连接:查询出所有有顾客和没有顾客的公司信息(附带顾客信息)
# r ∪ (l ∩ r)
SELECT * FROM
customer l
RIGHT JOIN
company r
ON
l.company_id = r.id;
RIGHT JOIN EXCLUDING INNER JOIN
带 WHERE 的右外连接:查询出所有没有顾客的公司信息(附带顾客信息)
- 排除了有顾客的公司信息(附带顾客信息)
# r - (l ∩ r)
SELECT * FROM
customer l
RIGHT JOIN
company r
ON
l.company_id = r.id
WHERE
l.company_id IS NULL;
SQL 规范中定义了 FULL OUTER JOIN(全外连接),MySQL 中实现需要依靠 UNION
FULL OUTER JOIN
全外连接:查询出所有:
- 有顾客的公司信息(附带顾客信息) 等价于 有公司的顾客信息(附带公司信息)
- 没有顾客的公司信息(附带顾客信息)
- 没有公司的顾客信息(附带公司信息)
# l ∪ r
(
SELECT * FROM
customer l
LEFT JOIN
company r
ON
l.company_id = r.id
)
UNION
(
SELECT * FROM
customer l
RIGHT JOIN
company r
ON
l.company_id = r.id
);
FULL OUTER JOIN EXCLUDING INNER JOIN
排除内连接的全连接,查询出所有:
- 没有顾客的公司信息(附带顾客信息)
- 没有公司的顾客信息(附带公司信息)
# (l ∪ r) - (l ∩ r)
(
SELECT * FROM
customer l
LEFT JOIN
company r
ON
l.company_id = r.id
WHERE
r.id IS NULL
)
UNION
(
SELECT * FROM
customer l
RIGHT JOIN
company r
ON
l.company_id = r.id
WHERE
l.company_id IS NULL
);
更多的表
排序、分页
排序:
ORDER BY 字段 [ASC | DESC]
分页:
LIMIT {[offset,] row_count | row_count OFFSET offset}
- offset 是记录的偏移量(最小值是 0),从哪一条记录开始选择
- row_count 是希望选择的记录数量
代码示例:从第 10 条记录开始,选择 20 条记录
LIMIT 10, 20
LIMIT 20 OFFSET 10
子查询
当一个查询是另一个查询的条件时,称之为子查询
代码示例:
SELECT * FROM customer WHERE company_id = (
SELECT id FROM company WHERE name = '腾讯'
)