SQL 多表查询的几种写法

本篇不涉及表的各种连接关联关系，仅仅记录关联的几种写法。

创建表

学校表

CREATE TABLE school (
school_id integer NOT NULL,
school_code varchar(10),
school_name varchar(50)
);

班级表

CREATE TABLE class (
class_id integer NOT NULL,
class_grade varchar(10),
class_class varchar(10),
school_id integer NOT NULL
);

学生表

CREATE TABLE student (
student_id integer NOT NULL,
student_code varchar(10),
student_name varchar(10),
class_id integer NOT NULL
);

写数据

学校表

INSERT INTO school (school_id, school_code, school_name)
VALUES
(1, 'A001', '理想中学'),
(2, 'A002', '红旗试验中学'),
(3, 'A003', '外国语学校')

班级表

INSERT INTO class (class_id, class_grade, class_class, school_id)
VALUES
(1, '2', '1', 1),
(2, '2', '2', 1),
(3, '2', '1', 2),
(4, '1', '1', 2),
(5, '3', '1', 3)

学生表

INSERT INTO student ("student_id", "student_code", "student_name", "class_id")
VALUES
(1, '1', '石头', 1),
(2, '2', '王强', 1),
(3, '3', '张建国', 1),
(4, '1', '李国华', 2),
(5, '1', '赵铁', 3),
(6, '2', '高亮', 4)

第一种写法

SELECT
a.school_id,
a.school_code,
a.school_name,
b.class_grade,
b.class_class,
c.student_code,
c.student_name
FROM
school a,
class b,
student c
WHERE
a.school_id = b.school_id
AND b.class_id = c.class_id

以上写法所有的表都被放在from关键字后面，实现的是三个表的innner jion查询关系，关联条件写在where子句里，当表多的时候逻辑混杂，不便阅读和理解。
这种写法不应该被推荐的原因，还在于它所能实现的关联关系少之又少，但也不是一无是处。（有些数据库在此基础上通过*=和=*或(+)（取决与其所处位置）符号能够实现left join或right join关系）

第二种写法

SELECT
a.school_id,
a.school_code,
a.school_name,
b.class_grade,
b.class_class,
c.student_code,
c.student_name
FROM school a
INNER JOIN class b ON a.school_id = b.school_id
INNER JOIN student c ON b.class_id = c.class_id

第三种写法

SELECT
a.school_id,
a.school_code,
a.school_name,
b.class_grade,
b.class_class,
c.student_code,
c.student_name
FROM
(
( school a INNER JOIN class b ON ( a.school_id = b.school_id ) )
INNER JOIN student c ON ( b.class_id = c.class_id )
)