浅谈T-SQL中的联接查询

引言

  平时开发时,经常会使用数据库进行增删改查,免不了会涉及多表联接。今天就简单的记录下T-SQL下的联接操作。

联接类型及其介绍

  在T-SQL中联接操作使用的是JOIN表运算符。联接有三种基本的类型,分别是:交叉联接、内联接和外联接。这三种联接的区别在于它们的逻辑查询处理步骤不同。每一种联接都有一套不同的步骤。

  交叉连接:

  只有一个步骤:笛卡尔积

  内连接:

  两个步骤:笛卡尔积、使用ON关键字进行过滤

  外连接:

  三个步骤:笛卡尔积、ON关键字过滤、添加外部行

  强调的一点:

  联接查询的逻辑步骤是需要比对两张表的数据的,听起来好像比较低效,但这是逻辑上的步骤。在实际的查询中RDBMS引擎去负责数据的查询,RDBMS在实际查询时会走"捷径",只要保证实际查询的结果和逻辑上获取的结果保持一致即可。所以说,我们SQL上写法是逻辑步骤的描述过程,但实际的执行引擎通过优化,会走很多的捷径来加快数据的检索。

交叉联接

  交叉联接在逻辑上是最简单的一种联接操作。他只有一个步骤就是笛卡尔积。假设表A存在m行数据,表B存在n行数据,对A,B两张表进行交叉联接会得到m*n行数据。T-SQL 92的语法如下:

 select E1.empid,E2.empid from HR.Employees as E1
cross join HR.Employees as E2

  在HR.Employee表中我们存放有9条数据。通过对同一张表进行交叉联接,我们会得到81行数据。E2表中的每一行都会映射到E1表的全部数据行。

内联接

  内联接有两个步骤:笛卡尔积和ON关键字过滤。内联接使用须在两个表名之间使用INNER JOIN关键字(INNER关键字是可选的,T-SQL使用JOIN时,默认就是使用内联接)。用于对行进行过滤的条件是在ON子句中指定的(ON也称为联接条件)。

  我们来看一个例子。Employee表中保存雇员的信息。Orders表存放客户下过的订单,每一个订单都有一名雇员来处理,这时会使用empid来标记该订单是哪一位雇员来处理的。通过内联结我们可以获取所有雇员处理过的订单数据。例如:

 select E.empid,E.firstname,E.lastname,O.orderid
from HR.Employees as E
inner join Sales.Orders as O
on E.empid=O.empid

  我们理解上面查询的逻辑步骤可以采用以下简单的方法来描述。我们可以认为将Employees表中的每一行数据与Orders表中的所有行进行比较。如果雇员的ID(Orders表中的雇员ID)与Employees表中的这一行雇员ID相同,则匹配成功。这是理解内联结的一种简单方法。其实,我们应该从逻辑处理步骤上来进行思考。内联结的第一步是笛卡尔积。雇员表是9行数据,订单表是830行数据。笛卡尔积一共是9*830=7470行数据。然后根据ON关键字来筛选。选择其中E.empid=O.empid的行,返回830行。

  注意:SQL使用的是三值谓词逻辑。与HAVING和WHERE子句类似,ON子句也是使用的三值逻辑。ON子句自会返回让谓词逻辑为TRUE的行,所有FALSE和UNKNOW的行都会被过滤掉。

外联结

  外联结与相比较其他两种联结,其逻辑步骤又多了一步,添加外部行。在外联结中,需要把一个表标记为"保留表",可以在表名之间使用LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN。LEFT关键字表示左表是保留表,RIGHT关键字表示右表是保留表,FULL关键字则表示左右两张表都是保留表。外联结的第三个步骤其实就是找出保留表中按照ON条件在另一个表中找不到与之匹配的那些行,然后把它们添加到结果集中。

  下面我们还是通过一个例子来了解:

  表Customers存放的是客户信息,Orders表存在是订单信息。现在需要查询客户和他们的订单的信息(没下过订单的客户也需要在结果集中)。这时我们可以将Customers表作为左表。请看例子:

 select C.custid,companyname,orderid
from Sales.Customers as C
left outer join Sales.Orders as O
on C.custid=O.custid

  Customers表中有两个客户没有下过订单,但是他们也出现在结果集中。从逻辑上讲这两个客户由于在Orders表中匹配不到数据,应该被过滤掉了(的确,他们在ON关键字这一步的确被过滤了)。但是外联结的第三步添加外部行又将他们加入进来了。

  上述查询结果如下(部分):

  浅谈T-SQL中的联接查询

  从外联结保留表的角度看,可以认为外联结结果中的数据包含两种。内部行和外部行。内部行是那些ON关键字可以在两表之间匹配到的行。外部行则是找不到匹配的那些行。内联结只返回内部行。而外联结返回内部行和外部行。

  注意

  使用外联接时经常就使用ON还是WHERE作为过滤条件而迷惑。从外联结保留表中的行角度看,ON关键字的过滤条件不是最终的。换句话说ON关键字并不能决定保留表中的部分行是否会被添加到结果集中,而只是判断是否匹配另一个表中的某些行。所有,当需要表达一个非最终的条件时(即这个条件只决定哪些行可以匹配非保留表),就在ON条件中指定。当在生成外部行以后,需要应用过滤器,并且希望过滤条件是最终的,就需要在WHERE子句中指定条件。

  请看例子:

  假设现在需要返回没有下过订单的客户信息(只需要返回外部行)。语句如下:

 select C.custid,companyname,orderid
from Sales.Customers as C
left outer join Sales.Orders as O
on C.custid=O.custid
where orderid is null

  我们看到,只是在WHERE条件中指定了最终的过滤条件即可。切记,选择非保留表中的列作为过滤条件也很重要。应该选择只在外部行才取值为NULL,而在其他行取值不为NULL的某个列。

上一篇:Grid控件


下一篇:JpaRepository 查询规范