9. 多表连接查询

        多表查询可理解为一个嵌套循环遍历。

多表连接查询有两种规范， 较早的SQL92规范支持：

• 等值连接：连接条件要求两列值相等
• 非等值连接
• 广义笛卡尔积：没有任何连接条件（n*m条记录）
• 外连接 【MySQL 不支持 92规范的外连接】

        外连接就是在外连接符所在的表中增加一个”万能行“，这行记录的所有数据都是null，而且该行可以与另一个表中所有不满足条件的记录匹配。即可以把另一表中的所有记录选出来，不管是否满足条件。

SQL99规范：提供可读性更好的多表连接语法，及更多类型的连接查询

• 交叉连接
• 自然连接
• 使用using子句的连接
• 使用on子句的连接
• 全外连接或左、右外连接。

        此外，还有一种自连接。如果同一表中的不同记录存在主、外键约束关联，则需使用自连接查询。自连接只是连接的一种用法，不是一种连接类型，SQL92、SQL99都可使用。 自连接本质是把一个表当成两个表用。

CREATE TABLE emp_table ( # 建立自关联数据表

    id INT auto_increment PRIMARY KEY,

    uname VARCHAR(255),

    manager_id INT,

    FOREIGN KEY(manager_id) REFERENCES emp_table (id)

);

INSERT INTO emp_table

VALUES

    (NULL,'tang',NULL), # 唐僧是老大

    (NULL,'sun',1),

    (NULL,'zhu',1),

    (NULL,'sha',1);

生成的表如下（取经团队）：

"id" "uname" "manager_id"

"1"     "tang"     ""

"2"     "sun"     "1"

"3"     "zhu"     "1"

"4"     "sha"     "1"

        查询该表所有员工名及对应经理名，必须使用自连接查询。为一个表起两个别名，且查询中所有数据列都要加表别名前缀。

SELECT

     emp.id,

     emp.uname 员工名,

     mgr.uname 经理名

FROM

     emp_table emp,

     emp_table mgr

WHERE

     emp.manager_id = mgr.id;

上述查询可查询出所有的员工名及对应的经理名。

"id"  "员工名" "经理名"

"2"    "sun"    "tang"

"3"    "zhu"    "tang"

"4"    "sha"    "tang"

1、SQL92的连接查询

•  多个数据表放在from之后，表间逗号隔开；
• 连接条件放where之后，与查询条件间用and逻辑运算符连接；
• 多个数据列具有相同列名时，需在同名列间用表（别）名前缀作为限制。

语法格式：

SELECT col1, col2...

FROM table1, table2...

[WHERE join_condition ]

例子：

#

# SQL92规范

#

# 查询学生信息及其Java成绩

SELECT s.*,java

FROM

    student s,  # 取别名

    grades g

WHERE

    #去掉where条件得到广义笛卡尔积

    #   s.id = g.id  # 等值连接

    s.id = g.id

AND s.math > g.math  # and连接过滤条件

#

#MySQL不支持SQL92规范的外连接,以下报错

#

SELECT  s.*, g.java

FROM

    student s, grades g 

WHERE  s.id = g.id (*); # 右外连接

2、SQL99的连接查询

        99和92原理基本相似，但99可读性更强：

• 多数据表显式用xxx join连接，而不是依次排在from后，from后只需放一个数据表；
• 提供了专门的连接条件子句，连接条件不再放在where后。
• 以下查询结果均是符合条件的行的笛卡尔积。

例子：

# 交叉连接（crossjoin）

# 效果就是92的广义笛卡尔积，无需任何连接条件

SELECT

    s.*,java# 99多连接查询的from后只有一个表名

FROM

    student s

CROSS JOIN grades g;

 

# 自然连接（natural join）

# 表面看起来无条件，但是有连接条件的，以两个表中【所有同名列】作为连接条件；

# 查询结果：为【所有同名列】数据相同行的笛卡尔积

# 如果两表没有同名列，则和交叉连接效果一样。

SELECT

    s.*,java

FROM

    student s

NATURAL JOIN grades g;

 

# using子句连接

# 显式指定一列或多列的同名列作为连接条件；

# using指定的列必须是同名列，否则报错[Err]1054-Unknown column 'java' in 'from clause'

SELECT

    s.*,java

FROM

    student s # join连接另一个表g

JOIN grades g USING(id);

 

# on子句连接

# SQL99语法在on子句指定连接条件

# 每个on子句只指定一个连接条件。即如果需要N表连接，则需要N-1个join...on对。

# 查询出符合条件的行的笛卡尔积

# on子句条件可以是等值、非等值的，完全可以替换SQL92的（非）等值连接

SELECT

    s.*,java

FROM

    student s

JOIN grades g # on指定连接条件

ON s.grade = g.java

JOIN emp_table e ON s.id > e.id;

# 最后结果集有2*2*4行

 

# 左、右、全外连接：left[outer]join、right[outer]join、full[outer]join

# outer（外），默认省略；

# on子句指定连接条件，(非)等值连接条件；

# 99外连接与92恰好相反；

# 左外连接：查询符合条件的结果集+left左边表中不符合条件的记录。

# 右外连接与左外相反；

# 全外连接:额外输出两表中所有不满足条件的记录。

SELECT

    s.*,java

FROM

    student s # RIGHT JOIN grades g # 右外连接

LEFT JOIN grades g # 左外连接

ON s.grade = g.java;

 

# 截止5.7.10MySQl不支持全外连接full，但可通过左外、右外连接实现

SELECT s.*,java

FROM  student s

LEFT JOIN grades g # 左外连接

ON s.grade = g.java

UNION(

        SELECT s.*,java

        FROM student s

        RIGHT JOIN grades g # 右外连接

        ON s.grade = g.java

    );