联接查询

sql server 2008支持四种表运算符----JOIN,APPLY,PIVOT,UNPIVOT.

JOIN表运算符是ANSI标准,而APPLY,PIVOT,UNPIVOT是T-SQL对标准的扩展

JOIN表运算符,联接有三种基本类型:交叉联接,内联接和外联接.

交叉联接只有一个步骤:笛卡尔积;内联接有两个步骤:笛卡尔积和过滤;外联接有三个步骤:笛卡尔积,过滤,添加外部行.

(逻辑查询处理和物理查询处理的不同!)

3.1 交叉联接 (cross join)

最简单的联接,只实现了一个逻辑查询步骤(笛卡尔积)

3.1.1 ANSI SQL-92

一般标准都是用 ANSI-SQL 92的

-- ANSI SQL-92

USE TSQLFundamentals2008;

SELECT C.custid, E.empid

FROM Sales.Customers AS C

  CROSS JOIN HR.Employees AS E;

3.1.2 ANSI SQL-89

-- ANSI SQL-89

SELECT C.custid, E.empid

FROM Sales.Customers AS C, HR.Employees AS E;

3.1.3 自交叉联接

对同一个表的多个实例也可以进行联接,这种功能就是所谓的自联接,所有基本联接类型(交叉联接,内联接,以及外联接)都是支持自联接.

-- 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;

GO

--在自联接中,必须为表起别名.如果不为表指定别名,联接结果中的列名就会有歧义.

--自联接的笛卡尔积,与不是自联接的笛卡尔积不同!

3.1.4 生成数字表

自交叉联接的运用.

-- All numbers from  -

-- Auxiliary table of digits

USE tempdb;

IF OBJECT_ID('dbo.Digits', 'U') IS NOT NULL DROP TABLE dbo.Digits;

CREATE TABLE dbo.Digits(digit INT NOT NULL PRIMARY KEY);

--2008特有的

INSERT INTO dbo.Digits(digit)

  VALUES (),(),(),(),(),(),(),(),(),();

/*

Note:

Above INSERT syntax is new in Microsoft SQL Server 2008.

In earlier versions use:

INSERT INTO dbo.Digits(digit) VALUES(0);

INSERT INTO dbo.Digits(digit) VALUES(1);

INSERT INTO dbo.Digits(digit) VALUES(2);

INSERT INTO dbo.Digits(digit) VALUES(3);

INSERT INTO dbo.Digits(digit) VALUES(4);

INSERT INTO dbo.Digits(digit) VALUES(5);

INSERT INTO dbo.Digits(digit) VALUES(6);

INSERT INTO dbo.Digits(digit) VALUES(7);

INSERT INTO dbo.Digits(digit) VALUES(8);

INSERT INTO dbo.Digits(digit) VALUES(9);

*/

SELECT digit FROM dbo.Digits;

GO

-- All numbers from  -

SELECT D3.digit *  + D2.digit *  + D1.digit +  AS n

FROM         dbo.Digits AS D1

  CROSS JOIN dbo.Digits AS D2

  CROSS JOIN dbo.Digits AS D3

ORDER BY n;

3.2 内联接

内联接要应用的两个逻辑查询处理步骤:首先像交叉联接一样,对两个输入表进行笛卡尔积运算;然后根据用户指定的谓词对结果进行过滤.

3.2.1 ANSI-SQL 92

ANSI-SQL 92:须在两个表名之间指定INNER JOIN关键字.

INNER关键字是可选的,因为内联接是默认的联接方式,所以可以只单独指定JOIN关键字.

用于对行进行过滤的谓词是在一个称为ON子句的特别设计的语句中指定的,该谓词也称为联接条件.

---------------------------------------------------------------------

-- INNER Joins

---------------------------------------------------------------------

-- 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;

--以上,在关系代数的基础上来考虑内联接,联接运算首先对两个表求笛卡尔积,然后根据条件E.empid = O.empid,对行进行过滤.

--与WHERE和HAVING子句类似,ON子句同样也只返回令谓词结果为TRUE的行,而不会返回令谓词计算结果为FALSE或UNKNOW的行.

3.2.2 ANSI-SQL 89

-- 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;

GO

--没有ON子句,不推荐使用!

3.2.3 更安全的内联接

-- Inner Join Safety

-- 采用ANSI-SQL 92的语法

SELECT E.empid, E.firstname, E.lastname, O.orderid

FROM HR.Employees AS El

  JOIN Sales.Orders AS O;

GO

SELECT E.empid, E.firstname, E.lastname, O.orderid

FROM HR.Employees AS E, Sales.Orders AS O;

GO

3.3 特殊的联接实例

特殊的联接实例包括:组合联接,不等联接,以及多表联接

3.3.1 组合联接

组合联接就是联接条件设计联接两边的多个列的查询.当需要根据主键-外键关系来联接两个表,而且主外键关系是组合的(即,关系基于多个列)时,通常就要使用组合联接.

例如:

SELECT * FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.COL1= T2.COL1 AND T1.COL2 = T2.COL2

---------------------------------------------------------------------

-- Composite Joins

---------------------------------------------------------------------

-- Audit table for updates against OrderDetails

USE TSQLFundamentals2008;

IF OBJECT_ID('Sales.OrderDetailsAudit', 'U') IS NOT NULL

  DROP TABLE Sales.OrderDetailsAudit;

CREATE TABLE Sales.OrderDetailsAudit

(

  lsn        INT NOT NULL IDENTITY,

  orderid    INT NOT NULL,

  productid  INT NOT NULL,

  dt         DATETIME NOT NULL,

  loginname  sysname NOT NULL,

  columnname sysname NOT NULL,

  oldval     SQL_VARIANT,

  newval     SQL_VARIANT,

  CONSTRAINT PK_OrderDetailsAudit PRIMARY KEY(lsn),

  CONSTRAINT FK_OrderDetailsAudit_OrderDetails

    FOREIGN KEY(orderid, productid)

    REFERENCES Sales.OrderDetails(orderid, productid)

);

SELECT OD.orderid, OD.productid, OD.qty,

  ODA.dt, ODA.loginname, ODA.oldval, ODA.newval

FROM Sales.OrderDetails AS OD

  JOIN Sales.OrderDetailsAudit AS ODA

    ON OD.orderid = ODA.orderid

    AND OD.productid = ODA.productid

WHERE ODA.columnname = N'qty';

3.3.2 不等联接

如果联接条件只包含等号运算符,要么这样的联接叫做等值联接.如果联接条件包含除等号以外的其他运算符,那么这样的联接叫做不等联接.

---------------------------------------------------------------------

-- Non-Equi Joins

---------------------------------------------------------------------

-- Unique pairs of employees

SELECT

  E1.empid, E1.firstname, E1.lastname,

  E2.empid, E2.firstname, E2.lastname

FROM HR.Employees AS E1

  JOIN HR.Employees AS E2

    ON E1.empid < E2.empid;

--ON子句中指定的判断条件,目的是为了生存雇员之间的唯一配对组合.

--以上,使用内联接,并在联接条件中指定左边的键值要小于右边的键值!

3.3.3 多表联接

一个联接表运算符只对两个表进行操作,而一条查询语句可以包含多个联接.

当FROM子句中包含多个表运算符时,表运算符在逻辑上是按从左到右的顺序处理的.也就是,第一个表运算符的结果表将作为第二个表运算符的输入,第二个表运算符的结果将作为第三个表运算符左边的输入,以此类推!

---------------------------------------------------------------------

-- Multi-Table Joins

---------------------------------------------------------------------

SELECT

  C.custid, C.companyname, O.orderid,

  OD.productid, OD.qty

FROM Sales.Customers AS C

  JOIN Sales.Orders AS O

    ON C.custid = O.custid

  JOIN Sales.OrderDetails AS OD

    ON O.orderid = OD.orderid;

3.4 外联接

3.4.1 外联接基础

外联接是在ANSI-SQL 92中才被引入的,外联接的逻辑步骤有:1.笛卡尔积;2.ON 过滤器;3.添加外部行

在外联接中,要把一个表标记为"保留的"表;在表名之间使用关键字LEFT OUTER JOIN,RIGHT OUTER JOIN,以及FULL OUTER JOIN,其中OUTER关键字是可选的.

LEFT关键字表示左边表的行是保留的,RIGHT关键字表示右边表的行是保留的,而FULL关键字则表示左右两边表的行都是保留的.外联接的第三个逻辑查询处理步骤就是要识别保留表中按照ON条件在另一个表找不到与之匹配的那些行,再把这些行添加到联接的前两个步骤生成的结果表中;对于来自联接的非保留表的那些列,追加的外部行中额这些列则用NULL作为占位符.

-- 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子句中的过滤条件不是最终的.ON子句中的条件并不能最终决定保留表中部分行是否会在结果中出现,而只是判断是否能够匹配另一边表中的某些行.所以,当需要表达一个非最终的条件时,就在ON子句中指定联接条件.当在生成外部行之后,要应用过滤器,而且希望过滤条件是最终的,就应该在WHERE子句中指定.,对于行的过滤来说,WHERE子句是最终的.

-- Customers with no orders

SELECT C.custid, C.companyname

FROM Sales.Customers AS C

  LEFT OUTER JOIN Sales.Orders AS O

    ON C.custid = O.custid

WHERE O.orderid IS NULL;

GO

--以上,可以返回外部行,对于外部行,其来自联接的非保留表的列都标记为NULL,所以可以只筛选联接的非保留表的列值之一为NULL的那些行

--选择非保留表中的哪个列作为过滤器也很重要.应该选择只在外部行才取值为NULL,而在其他行取值不为NULL(例如,NULL值不能来自基本表)的某个列.可以考虑三种情况----主键列,联接列,以及定义为NOT NULL的列.\
--当查找NULL值时,应该使用 IS NULL 运算符.

联接

第三章联接查询 T-SQL语言基础

联接查询

3.1 交叉联接 (cross join)

3.1.1 ANSI SQL-92

3.1.2 ANSI SQL-89

3.1.3 自交叉联接

3.1.4 生成数字表

3.2 内联接

3.2.1 ANSI-SQL 92

3.2.2 ANSI-SQL 89

3.2.3 更安全的内联接

3.3 特殊的联接实例

3.3.1 组合联接

3.3.2 不等联接

3.3.3 多表联接

3.4 外联接

3.4.1 外联接基础

3.4.2 外联接的高级主题

1.包含缺少值的数据

2.对外联接中非保留表的列值进行过滤

3.在多表联接中使用外联接

4.随外联接一起使用COUNT聚合函数

联接

第三章 联接查询 T-SQL语言基础

联接查询

3.1 交叉联接 (cross join)

3.1.1 ANSI SQL-92

3.1.2 ANSI SQL-89

3.1.3 自交叉联接

3.1.4 生成数字表

3.2 内联接

3.2.1 ANSI-SQL 92

3.2.2 ANSI-SQL 89

3.2.3 更安全的内联接

3.3 特殊的联接实例

3.3.1 组合联接

3.3.2 不等联接

3.3.3 多表联接

3.4 外联接

3.4.1 外联接基础

3.4.2 外联接的高级主题

1.包含缺少值的数据

2.对外联接中非保留表的列值进行过滤

3.在多表联接中使用外联接

4.随外联接一起使用COUNT聚合函数

第三章联接查询 T-SQL语言基础