多表查询的常用SQL语句
1 连接查询
1.1 内连接-inner join查询即连接-join查询
join和inner join是等效的。
SQL——join:
SELECT
pe.id,
pe.`name`,
pe.age,
a.id AS a_id,
a.detail,
a.person_id
FROM
person AS pe
JOIN address AS a ON pe.id = a.person_id
结果:
SQL——inner join:
SELECT
pe.id,
pe.`name`,
pe.age,
a.id AS a_id,
a.detail,
a.person_id
FROM
person AS pe
INNER JOIN address AS a ON pe.id = a.person_id
结果:
通过结果对比可知,join和inner join等效。
1.2 左连接-left join查询即左外连接查询–left outer join
SQL:
SELECT
pe.id,
pe.`name`,
pe.age,
a.id AS a_id,
a.detail,
a.person_id
FROM
person AS pe
LEFT JOIN address AS a ON pe.id = a.person_id
结果:
1.3 右连接-right join查询即右外连接查询–right outer join
SQL:
SELECT
pe.id,
pe.`name`,
pe.age,
a.id AS a_id,
a.detail,
a.person_id
FROM
person AS pe
RIGHT JOIN address AS a ON pe.id = a.person_id
结果:
2 传统连接查询-from 多表
SQL:
SELECT
pe.id,
pe.`name`,
pe.age,
a.id AS a_id,
a.detail,
a.person_id
FROM
person AS pe,
address AS a
WHERE
pe.id = a.person_id
结果:
3 查询嵌套
内层的嵌套的子查询返回记录数:
- 单条记录
- 多条记录
3.1 子层查询返回一条记录
where pe.id 操作符可以是下表中的任意一个:
操作符 | 描述 |
---|---|
pe.id > | 大于 |
< | 小于 |
= | 等于 |
>= | 大于等于 |
<= | 小于等于 |
<> | 不等于 |
SQL:
SELECT
*
FROM
person pe
WHERE
pe.id = (
SELECT
a.id
FROM
address a
WHERE
id = 1
)
结果:
3.2 子层查询返回多条记录
操作符 | 描述 |
---|---|
in | |
any | |
all |
SQL——in:
SELECT
*
FROM
person pe
WHERE
pe.id IN (
SELECT
a.person_id
FROM
address a
)
结果:
SQL——all:
SELECT
*
FROM
person pe
WHERE
pe.id > ALL (
SELECT
a.person_id
FROM
address a
WHERE a.person_id = 1
)
结果: