关于SQL表联接
以SQL2008为例,Microsoft SQL Server 2008支持四种表运算符-JOIN,APPLY,PIVOT,UNPIVOT.JOIN表运算符是ANSI标准,而其他三种是T-SQL对标准的扩展。今天主要是了解有关JOIN表的操作。
JOIN表运算符对两个输入表进行操作。联接有三种基本类型:交叉联接,内联接和外联结。这三种联接的区别是它们采用的逻辑查询处理步骤各不相同,每种联接都有一套不同的步骤。交叉联接只有一个步骤-笛卡尔积;内联接有两个步骤-笛卡尔积,过滤;外联结有三个步骤-笛卡尔积,过滤,添加外部行。
学习使用的数据库是T-SQLFundamentals2008数据库,数据库创建代码分享:http://pan.baidu.com/s/1jGLBxm2
1.交叉联接
逻辑上,交叉联接是最简单的联接。交叉联接只实现一个逻辑查询步骤(笛卡尔积)。这一步是对输入的两个表进行操作, 把它们联接起来,生成二者的笛卡尔积。也就是将一个输入表的每行与另一个表的所有行进行匹配。如果一个表有m行,另一个表有n行,将得到m*n行的结果集。
SQL-Server支持交叉联接的两种标准语法-ANSI SQL-92和ANSI SQL-89语法。这里推荐使用ANSI SQL-92,具体原因稍后解释。
在下载数据库代码后创建T-SQLFundametals2008数据库后,下面的例子对数据库中的Customers表和Employees表进行交叉联接查询(使用ANSI SQL-92语法),返回集合中的custid和empid:
-- ANSI SQL-92语法
USE TSQLFundamentals2008;
SELECT C.custid, E.empid
FROM Sales.Customers AS C
CROSS JOIN HR.Employees AS E;
-- ANSI SQL-89语法
SELECT C.custid, E.empid
FROM Sales.Customers AS C, HR.Employees AS E;
这两种语法在逻辑和性能上没有区别,它们都是最新的SQL标准中不可缺少的一部分,而且SQL Server的最新版本也完全支持这两种语法。之所以推荐使用ANSI SQL-92语法,在解释完内联接后将会变的很清楚。
自交叉联接:对同一个表的多个实例也可以进行联接,这种功能就是所谓的自联接(self-join),所有基本联接类型(交叉联接,内联接,外联接)都支持自联接。
例:该查询生成员工配对的所有可能组合。
-- Self Cross-Join
SELECT
E1.empid, E1.firstname, E1.lastname,
E2.empid, E2.firstname, E2.lastname
FROM HR.Employees AS E1
CROSS JOIN HR.Employees AS E2;
在自联接中,必须为表起别名。如果不为表指定别名,联接结果中的列名就会有歧义。
2.内联接
内联接要应用两个逻辑查询处理步骤:它首先像交叉联接一样,对两个输入表进行笛卡尔积运算:然后根据用户指定的谓词对结果进行过滤。和交叉联接一样,内联接也有两种标准语法:ANSI SQL-92和ANSI SQL-89。
2.1:ANSI SQL-92语法
使用ANSI SQL-92语法,需在两个表名之间指定 INNER JOIN关键字。INNER关键字是可选的,因为内联接是默认的联接方式,所以可以只单独指定JOIN关键字,但是 还是建议全部指定。用于进行过滤的谓词是一个称为ON子句的特别设定的语句中指定的,该谓词也称之为联接条件。
例:对TSQLFundamentals2008数据库中Employees表和Orders表执行内联接运算,根据谓词条件 E.empid=O.empid对雇员和订单记录进行匹配:
-- ANSI SQL-92
USE TSQLFundamentals2008;
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E
JOIN Sales.Orders AS O
ON E.empid = O.empid;
因为SQL使用三值谓词逻辑(true,false,unknown),ON子句也只返回谓词结果为true的行,而不会返回谓词结果为false或者unknown的行,如果雇员没有相关的订单,则在过滤处理阶段会将这些与雇员相关的行过滤掉。
2.2:ANSI SQL-89语法
和交叉联接类似,内联接也可以使用ANSI SQL-89语法来表达。可以像交叉联接那样在表名之间放一个逗号,在查询的WHERE子句中定义条件,如:
-- ANSI SQL-89
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E, Sales.Orders AS O
WHERE E.empid = O.empid;
注意:ANSI SQL-89语法没有ON子句
2.3:更安全的内联接
强烈推荐使用ANSI SQL-92的联接语法,因为他在某些方面用起来更安全。假如你想写一条内联接查询,但不小心忘记指定联接条件。如果这时候使用的是ANSI SQL-92语法,查询语句将是无效的,如:
-- Inner Join Safety
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E
JOIN Sales.Orders AS O;
然而,如果使用ANSI SQL-89语法,忘了指定联接条件,那么这个查询仍然是有效的,但执行的确是一个交叉查询:
SELECT E.empid, E.firstname, E.lastname, O.orderid
FROM HR.Employees AS E, Sales.Orders AS O;
因为查询没有失败,所以可能一时无法发现语句中隐藏的逻辑错误,最终导致用户在使用应用程序时得到的是错误的结果。对于简短的查询语句来说,程序员还不太可能忘记指定联接条件;但大多数的产品应用中的查询语句要比这复杂的多,会涉及多个表,过滤条件,以及其他查询元素。在这些复杂条件下,忘记指定联接条件的可能性也随之增加。
3.外联接
3.1:外联结基础
与内联接和交叉联接不同,外联结是在ANSI SQL-92中才被引入的,因此它只有一种标准语法----在表名中间指定JOIN关键字,在ON子句中指定联接条件。外联结会应用内联接所应用的两个逻辑步骤:(笛卡尔积和ON过滤),此外还多了一个外联结特有的第三步:添加外部行。
在外联结中,要把一个表标记为“保留的”表,可以在表名之间使用关键字LEFT OUTER JOIN,RIGHT OUTER JOIN,以及FULL OUTER JOIN,其中OUTER关键字是可选的。LEFT表示左边的表是保留的,RIGHT表示右边的表是保留的,FULL表示关键字左右两个表都是保留的。外联结的第三个步骤就是要识别保留表中按照ON条件在另一个表中找不到匹配的那些行,再把这些行添加到联接的前两个步骤所生成的结果集中;对于来自联接的非保留表的那些列,追加的外部行中的这些列则用NULL作为占位符。
通过例子来理解是个好办法。以下查询根据客户ID和订单的客户ID 对Customers表和Orders表进行联接,并返回客户和他们的订单信息。该查询语句使用的联接类型是左外联接,所以查询结果也会返回那些没有发出任何订单的客户:
-- Customers and their orders, including customers with no orders
SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid;
从外联结保留表的角度看,可以认为外联结结果中的数据行包括两种:内部行和外部行。内部行指的是按照ON子句中能在联接的另一个表中找到匹配的那些行;外部行就是找不到匹配的那些行。使用外联结时,经常会为到底是查询的ON子句中,还是在where子句中指定联接条件而感到困惑。从外联结的保留表中的行来考虑这个问题,ON子句中的过滤条件不是最终的。换句话说,ON子句中的条件并不能最终决定保留表中部分行是否会在结果中出现,而只是判断是否能够匹配到另一边表中的某些行。所以,当需要表达一个非最终的条件时,就在ON子句中指定联接条件。当在生成外部行以后,要应用过滤器,而且希望过滤条件是最终的,就应该在where中指定条件。
同样通过例子理解是个好办法:
在上面介绍外联时以下查询结果中:
-- Customers and their orders, including customers with no orders
SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid;
我们发现结果中当custid为22时,对应的orderid为null,那么再添加一个条件,外联结查询且custid不为22的结果。
如果我们在ON中指定条件
SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid AND C.custid<>22;
发现结果和上面的结果一样,同样会出现custid=22的那条数据,如果在where子句中添加条件
SELECT C.custid, C.companyname, O.orderid
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE C.custid<>22;
你就会发现custid=22的那条数据被过滤掉了,所以在外联结查询中添加条件是视情况而选择在ON子句中添加还是在WHERE子句中添加,所以建议在ON子句中只放关于两个表联接所需要的条件,如果要实现对结果集的再次过滤,请使用where子句。
摘自sql server2008技术内幕
虽然以前学习过SQL,但是还是感觉学习的不是很透彻,这里推荐一本书:microsoft sql server 2008技术内幕 t-sql语言基础!!!分享地址http://pan.baidu.com/s/1hqtplqO
看完之后又会发现自己学到好多东西!大神绕路,新人共勉!