SQL（二）|多表查询

多表查询的常用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
	)

结果：