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

联接查询

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 1 - 1000

-- 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 (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

/*
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 1 - 1000
SELECT D3.digit * 100 + D2.digit * 10 + D1.digit + 1 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 运算符.

3.4.2 外联接的高级主题

1.包含缺少值的数据

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

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

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

111