SQL99 连接查询

SQL92 连接查询,介绍了SQL92标准,在它之后又提出了SQL99标准。现在各大DBMS中对SQL99标准的支持度更好。你一定听说过LEFT JOIN、RIGHT JOIN这样的操作符,这实际上就是SQL99的标准,在SQL92中它们是用(+)代替的。SQL92和SQL99标准原理类似,只是SQL99标准的可读性更强。本文就来讲解一下SQL99标准中的连接查询。

SQL92 连接查询,文章中用NBA球员的数据表进行了举例,包括了三张数据表player、team和height_grades。

其中player表为球员表,一共有37个球员,如下所示:

SQL99 连接查询
team表为球队表,一共有3支球队,如下所示:

SQL99 连接查询
height_grades表为身高等级表,如下所示:

SQL99 连接查询
接下来我们看下在SQL99标准中,是如何进行连接查询的?

交叉连接

交叉连接实际上就是SQL92中的笛卡尔乘积,只是这里我们采用的是CROSS JOIN。

我们可以通过下面这行代码得到player和team这两张表的笛卡尔积的结果:

SELECT * FROM player CROSS JOIN team

运行结果(一共37*3=111条记录):

SQL99 连接查询
如果多张表进行交叉连接,比如表t1,表t2,表t3进行交叉连接,可以写成下面这样:

SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3

自然连接

你可以把自然连接理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段,然后进行等值连接。

如果我们想把player表和team表进行等值连接,相同的字段是team_id。还记得在SQL92标准中,是如何编写的么?

SELECT player_id, a.team_id, player_name, height, team_name FROM player as a, team as b WHERE a.team_id = b.team_id

在SQL99中你可以写成:

SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team 

实际上,在SQL99中用NATURAL JOIN替代了 WHERE player.team_id = team.team_id

ON连接

ON连接用来指定我们想要的连接条件,针对上面的例子,它同样可以帮助我们实现自然连接的功能:

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id

这里我们指定了连接条件是ON player.team_id = team.team_id,相当于是用ON进行了team_id字段的等值连接。

当然你也可以ON连接进行非等值连接,比如我们想要查询球员的身高等级,需要用player和height_grades两张表:

SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest

这个语句的运行结果和我们之前采用SQL92标准的查询结果一样。

SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest

一般来说在SQL99中,我们需要连接的表会采用JOIN进行连接,ON指定了连接条件,后面可以是等值连接,也可以采用非等值连接。

USING连接

当我们进行连接的时候,可以用USING指定数据表里的同名字段进行等值连接。比如:

SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id)

你能看出与自然连接NATURAL JOIN不同的是,USING指定了具体的相同的字段名称,你需要在USING的括号()中填入要指定的同名字段。同时使用JOIN USING可以简化JOIN ON的等值连接,它与下面的SQL查询结果是相同的:

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id

外连接

SQL99的外连接包括了三种形式:

  1. 左外连接:LEFT JOIN 或 LEFT OUTER JOIN
  2. 右外连接:RIGHT JOIN 或 RIGHT OUTER JOIN
  3. 全外连接:FULL JOIN 或 FULL OUTER JOIN

我们在SQL92中讲解了左外连接、右外连接,在SQL99中还有全外连接。全外连接实际上就是左外连接和右外连接的结合。在这三种外连接中,我们一般省略OUTER不写。

1.左外连接

SQL92

SELECT * FROM player, team where player.team_id = team.team_id(+)

SQL99

SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id

2.右外连接

SQL92

SELECT * FROM player, team where player.team_id(+) = team.team_id

SQL99

SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id

3.全外连接

SQL99

SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id

需要注意的是MySQL不支持全外连接,否则的话全外连接会返回左表和右表中的所有行。当表之间有匹配的行,会显示内连接的结果。当某行在另一个表中没有匹配时,那么会把另一个表中选择的列显示为空值。

也就是说,全外连接的结果=左右表匹配的数据+左表没有匹配到的数据+右表没有匹配到的数据。

自连接

自连接的原理在SQL92和SQL99中都是一样的,只是表述方式不同。

比如我们想要查看比布雷克·格里芬身高高的球员都有哪些,在两个SQL标准下的查询如下。

SQL92

SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height

SQL99

SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = '布雷克-格里芬' and a.height < b.height

运行结果(6条记录):

SQL99 连接查询

SQL99和SQL92的区别

SQL92和SQL99标准下的连接查询,它们都对连接进行了定义,只是操作的方式略有不同。我们再来回顾下,这些连接操作基本上可以分成三种情况:

  1. 内连接:将多个表之间满足连接条件的数据行查询出来。它包括了等值连接、非等值连接和自连接。
  2. 外连接:会返回一个表中的所有记录,以及另一个表中匹配的行。它包括了左外连接、右外连接和全连接。
  3. 交叉连接:也称为笛卡尔积,返回左表中每一行与右表中每一行的组合。在SQL99中使用的CROSS JOIN。

不过SQL92在这三种连接操作中,和SQL99还存在着明显的区别。

首先我们看下SQL92中的WHERE和SQL99中的JOIN。

你能看出在SQL92中进行查询时,会把所有需要连接的表都放到FROM之后,然后在WHERE中写明连接的条件。而SQL99在这方面更灵活,它不需要一次性把所有需要连接的表都放到FROM之后,而是采用JOIN的方式,每次连接一张表,可以多次使用JOIN进行连接。

另外,我建议多表连接使用SQL99标准,因为层次性更强,可读性更强,比如:

SELECT ...
FROM table1
    JOIN table2 ON table1和table2的连接条件
        JOIN table3 ON table2和table3的连接条件

它的嵌套逻辑类似我们使用的FOR循环:

for t1 in table1:
    for t2 in table2:
       if condition1:
           for t3 in table3:
              if condition2:
                  output t1 + t2 + t3

SQL99采用的这种嵌套结构非常清爽,即使再多的表进行连接也都清晰可见。如果你采用SQL92,可读性就会大打折扣。

最后一点就是,SQL99在SQL92的基础上提供了一些特殊语法,比如NATURAL JOIN和JOIN USING。它们在实际中是比较常用的,省略了ON后面的等值条件判断,让SQL语句更加简洁。

赞(1)
未经允许不得转载:极客笔记 » SQL99 连接查询
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址