2008技术内幕：T-SQL语言基础 联接查询摘记
第三章 联接查询
Microsoft SQL Server 2008 支持四种表运算符 join（ANSI标准）、apply（T-SQL扩展）、pivot（T-SQL扩展）、unpivot（T-SQL扩展）。
(ANSI SQL是美国国家标准学会(ANSI) 对SQL进行规范化后的国际标准SQL语言，T-SQL是Microsoft SQL Server基于ANSI SQL做了一些扩展形成的专用SQL语言。)
join表运算符对两个输入表进行操作，有三种基本类型：交叉联接、内联接、外联接。
处理步骤：
交叉联接只有一个步骤--笛卡尔积；
内联接有两个步骤--笛卡尔积、过滤；
外联接有三个步骤--笛卡尔积、过滤、添加外部行。
出于优化的目的，SQL Server关系引擎经常会采用很多处理捷径(数据库引擎并没有严格按照逻辑查询处理步骤)，因为他知道这样的处理仍然能够生成正确的结果。
交叉联接：
SQL Server支持交叉联接的两种标准语法--ANSI SQL-92 和 ANSI SQL-89 推荐用ANSI SQL-92(下面有推荐说明)。
--ANSI SQL-92标准 CROSS JOIN 关键字
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
这两种语法在逻辑上和性能上都没有区别，Customers表有91行而Employees有9行数据，所以上面的查询会生成一个819行数据的结果集。
笛卡尔积：如果一个表有m行，而另个表有n行，将得到m*n行的结果集。
图11
自交叉联接：顾名思义一个表实现自己连接自己。交叉联接、内链接、外连接都支持自联接。上面笛卡尔积图就是一个自交叉联接实例。
SELECT E1.empid ,E1.firstname ,E1.lastname ,
E2.empid ,E2.firstname ,E2.lastname
FROM hr.Employees AS E1
CROSS JOIN hr.Employees AS E2
在自联接中，必须为表起别名，如果不为表指定别名，联接结果中的列名就会有歧义。
--利用自联接生成数字表
DECLARE @table TABLE
(
digit INT NOT NULL PRIMARY KEY
)
INSERT INTO @table( digit )VALUES ( 0 ),( 1 ),( 2 ),( 3 ),( 4 ),( 5 ),( 6 ),( 7 ),( 8 ),( 9 )
SELECT T1.digit + T2.digit * 10 + T3.digit * 100 + 1 AS d
FROM @table AS T1
CROSS JOIN @table AS T2
CROSS JOIN @table AS T3
ORDER BY d
内联接
两个逻辑步骤：一，对两个输入的表进行笛卡尔积运算，二，过滤。
--ANSI SQL-92标准
两表之间必须指定inner join关键字，inner关键字是可选的，因为内联接是默认的联接方式，可以单独指定join关键字。
SELECT e.empid ,e.firstname ,e.lastname ,o.orderid
FROM hr.Employees AS e
JOIN Sales.Orders AS o ON e.empid = o.empid
on条件子句和where、having子句一样，只返回结果为true的行，而不会返回谓词计算结果为false、unknow的行
--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-92标准的联接语法，因为在某些方面它用起来更安全。如果你写一条内联接查询但忘记了指定联接条件，如果这时候用的是ANSI SQL-92标准，查询语句将是无效的，语法分析器会报错。即使错误提示信息没有马上指出错误是缺少了联接条件，但最终还是可以想办法找到问题在哪，修改好查询语句。
而ANSI SQL-89语法，如果忘记了指定联接条件，查询仍然是有效的，执行的结果集却是一个交叉查询。
错误例子
SELECT e.empid ,e.firstname ,e.lastname ,o.orderid
FROM hr.Employees AS eJOIN
JOIN Sales.Orders AS o
错误：Incorrect syntax near 'o'.
如果使用的是ANSI SQL-89标准语法，忘记了写连接条件，
SELECT e.empid ,e.firstname ,e.lastname ,o.orderid
FROM hr.Employees AS e ,
Sales.Orders AS o
那么恭喜得了一个大数据的大奖！爽歪歪！逻辑一复杂，错误在哪里也不知。
坚持推荐使用ANSI-92的原因：
1.是保持一致性（都有关键字）
2.减少错误（以防逻辑条件被遗漏）
3.提高维护性（一个写好的存储过程，你也不知道会被后面的人改几次，本来是个交叉联接改来改去成了内联接！这个是你无法预算的，但是这是可避免，直接标明cross join.）
组合联接：就是联接条件涉及两边多个列的查询
SELECT * FROM tabName1 AS A INNER JOIN tabName2 AS B ON A.id1 = B.id AND A.id = B.id2
不等联接：如果联接条件只包含等号运算符，那么这样的联接叫做等值联接，如果联接条件包含除等号以外的其他运算符，那么这样的联接叫做不等联接。
SELECT * FROM tabName1 AS A INNER JOIN tabName2 AS B ON A.id1 > B.id
多表联接
一个联接表运算符只对两个表进行操作，而一条查询语句可以包含多个联接。
通常，当From子句中包含多个表运算符时，表运算符在逻辑上是按从左到右的顺序处理的。
SELECT c.custid ,c.companyname ,o.orderid ,od.productid ,od.qty
FROM Sales.Customers AS c
INNER JOIN Sales.Orders AS o ON c.custid = o.custid
INNER JOIN Sales.OrderDetails AS od ON o.orderid = od.orderid
外联接
外联接是在ANSI SQL-92中才被引入的，因此它只有一种标准语法。逻辑步骤：笛卡尔积、on过滤、添加外部行。
在外联接中，要把一个表标记为“保留的”表，可以在表名之间使用关键字 left outer join、right outer join、full outer join，其中outer关键字是可选的(left join 、right join、full join )。
left关键字表示左边的表的行是保留的，right关键字表示右边表的行是保留的，full关键字则表示左右两边表的行都是保留的。对于来自联接的非保留表的那些列，追加的外部行中的这些列则用null作为占位符。
SELECT C.custid ,C.companyname ,O.orderid
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O ON C.custid = O.custid
题目：查询订单表中所有订单，查出从2007年1月1日到2010年12月12日之间的每个日期输订单个数。
DECLARE @table TABLE
(
[num] INT NOT NULL PRIMARY KEY ,
[date] DATETIME NOT NULL
)
SET NOCOUNT ON
DECLARE @i INT= 1
BEGIN TRAN
WHILE ( @i <= 3000 )
BEGIN
INSERT INTO @table
( num, date )
VALUES ( @i, -- num - int
DATEADD(DAY, @i - 1, '20060101') -- date - datetime
) ;
SET @i = @i + 1 ;
END
COMMIT TRAN
SET NOCOUNT OFF
SELECT CAST(d.date AS DATE) AS [date] ,
COUNT(o.orderid) AS [count]
FROM @table AS D
LEFT JOIN Sales.Orders AS O ON CAST(d.date AS DATE) = CAST(o.orderdate AS DATE)
WHERE CAST(d.date AS DATE) BETWEEN '20070101'
AND '20101212'
GROUP BY CAST(d.date AS DATE)
思路：
--1 建立一个辅助表，往里面添加数据，从2006年1月1日到2014年03月17日
--2 利用外联接查询
ISNULL()函数
语法
isnull(check_expression,replacement_value) returns int
SELECT [date] ,ISNULL(orderid, 0) AS 'default' ,orderid FROM @table AS D LEFT JOIN Sales.Orders AS O ON CAST(d.date AS DATE) = CAST(o.orderdate AS DATE)
图
外联接的条件过滤
对外联接表中非保留表的列值进行过滤，这通常就是存在错误的一个标志。因为外联接得到的外部行中的值有可能是null，而null<运算符><值>运算，得到的只会是Unknow。
例子
SELECT c.custid ,c.companyname ,o.orderid ,o.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O ON C.custid = O.custid
WHERE O.orderdate >= '20070101'
上面这条查询语句是对Customers表和Orders表执行左联接操作，Customers作为保留表，在where条件筛选之前，o.orderdate是有两条为null的值使用O.orderdate >= '20070101'过滤条件后，直接将这两条剔除了，因为null和任何比较都是unknow，而where只支持逻辑值为true的表达式，这样的查询条件会让所有外部行都被过滤掉，效果上相当于抵消了外联接的作用，换句话说，这里就是把外联接当成内联接用了。
练习
1-1.执行以下代码
SET NOCOUNT ON; USE TSQLFundamentals2008; IF OBJECT_ID('dbo.Nums','U') IS NOT NULL DROP TABLE dbo.Nums; CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY) DECLARE @i INT = 1 ; BEGIN TRAN WHILE ( @i <= 100000 ) BEGIN INSERT INTO Nums ( n ) VALUES ( @i -- n - int ) SET @i = @i + 1 ; END COMMIT TRAN SET NOCOUNT OFF
1-2 写一条查询语句，把所有雇员记录复制5次。
SELECT empid ,firstname ,lastname ,n
FROM HR.Employees AS E
CROSS JOIN dbo.Nums AS T
WHERE n < = 5
1-3 写一个查询，为每个雇员从2009年06月12日致2009年6月12日范围内的每天返回一行。
SELECT H.empid ,
DATEADD(DAY, n - 1, '20090101') AS [date]
FROM dbo.Nums AS N
CROSS JOIN HR.Employees AS H
WHERE DATEADD(DAY, n - 1, '20090101') BETWEEN '20090612'
AND '20090616'
ORDER BY h.empid
2.返回来自美国的客户，并为每个客户返回其订单总数和商品交易总数量。
SELECT C.custid ,
COUNT(DISTINCT O.orderid)AS [count] ,
SUM(OD.qty) AS totalqty
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O ON C.custid = O.custid
INNER JOIN Sales.OrderDetails AS OD ON O.orderid = OD.orderid
WHERE C.country = 'USA'
GROUP BY C.custid
3.返回客户及其订单信息，包括没有下过任何订单的客户.
SELECT c.custid ,c.companyname ,o.orderid ,o.orderdate
FROM Sales.Customers AS C
LEFT JOIN Sales.Orders AS O ON C.custid = O.custid
4.返回在2007年02月12日下过订单的客户，以及他们的订单，同时也返回在2007年2月12日没有下过订单的客户.
SELECT c.custid ,c.companyname ,o.orderid ,o.orderdate
FROM Sales.Customers AS C
LEFT JOIN ( SELECT custid ,orderid ,orderdate
FROM Sales.Orders
WHERE CAST(orderdate AS DATE) = '20070212'
) AS O ON C.custid = O.custid
图
5.返回所有客户信息，并根据客户是否在2007年2月12日日下过订单，再为每个客户返回一列 Yes/No值。上一题的扩展。用case when
SELECT c.custid ,c.companyname ,
CASE WHEN o.orderid IS NULL THEN 'No'
ELSE 'Yes'
END HasOrderOn20070212
FROM Sales.Customers AS C
LEFT JOIN ( SELECT custid ,
orderid ,
orderdate
FROM Sales.Orders
WHERE CAST(orderdate AS DATE) = '20070212'
) AS O ON C.custid = O.custid
posted on 2014-03-18 11:37 Aidou_dream 阅读(1498) 评论(2) 编辑 收藏 举报
