2008技术内幕:T-SQL语言基础 联接查询摘记

 

续 2008技术内幕:T-SQL语言基础 单表查询摘记

第三章 联接查询

  Microsoft SQL Server 2008 支持四种表运算符 join(ANSI标准)、apply(T-SQL扩展)、pivot(T-SQL扩展)、unpivot(T-SQL扩展)。

  join表运算符对两个输入表进行操作,有三种基本类型:交叉联接内联接外联接

  处理步骤

      交叉联接只有一个步骤--笛卡尔积

      内联接有两个步骤--笛卡尔积过滤

      外联接有三个步骤--笛卡尔积过滤添加外部行

  出于优化的目的,SQL Server关系引擎经常会采用很多处理捷径(数据库引擎并没有严格按照逻辑查询处理步骤),因为他知道这样的处理仍然能够生成正确的结果。

 

  交叉联接

  SQL Server支持交叉联接的两种标准语法--ANSI SQL-92 和 ANSI SQL-89 推荐用ANSI SQL-92(下面有推荐说明)。 

2008技术内幕:T-SQL语言基础 联接查询摘记
--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
2008技术内幕:T-SQL语言基础 联接查询摘记

  这两种语法在逻辑上和性能上都没有区别,Customers表有91行而Employees有9行数据,所以上面的查询会生成一个819行数据的结果集。

  笛卡尔积:如果一个表有m行,而另个表有n行,将得到m*n行的结果集。

  图11

2008技术内幕:T-SQL语言基础 联接查询摘记

  自交叉联接:顾名思义一个表实现自己连接自己。交叉联接、内链接、外连接都支持自联接。上面笛卡尔积图就是一个自交叉联接实例。 

2008技术内幕:T-SQL语言基础 联接查询摘记
SELECT  E1.empid ,E1.firstname ,E1.lastname ,
        E2.empid ,E2.firstname ,E2.lastname
FROM    hr.Employees AS E1
        CROSS JOIN hr.Employees AS E2
2008技术内幕:T-SQL语言基础 联接查询摘记

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

2008技术内幕:T-SQL语言基础 联接查询摘记
--利用自联接生成数字表
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
2008技术内幕:T-SQL语言基础 联接查询摘记

2008技术内幕:T-SQL语言基础 联接查询摘记

 

  内联接
    两个逻辑步骤:一,对两个输入的表进行笛卡尔积运算,二,过滤。

   --ANSI SQL-92标准
    两表之间必须指定inner join关键字,inner关键字是可选的,因为内联接是默认的联接方式,可以单独指定join关键字。 

2008技术内幕:T-SQL语言基础 联接查询摘记
SELECT  e.empid ,e.firstname ,e.lastname ,o.orderid
FROM    hr.Employees AS e
        JOIN Sales.Orders AS o ON e.empid = o.empid
2008技术内幕:T-SQL语言基础 联接查询摘记

    on条件子句和where、having子句一样,只返回结果为true的行,而不会返回谓词计算结果为false、unknow的行

2008技术内幕:T-SQL语言基础 联接查询摘记
--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
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  语法推荐说明

    在这里,还是强烈推荐使用ANSI SQL-92标准的联接语法,因为在某些方面它用起来更安全。如果你写一条内联接查询但忘记了指定联接条件,如果这时候用的是ANSI SQL-92标准,查询语句将是无效的,语法分析器会报错。即使错误提示信息没有马上指出错误是缺少了联接条件,但最终还是可以想办法找到问题在哪,修改好查询语句。

    而ANSI SQL-89语法,如果忘记了指定联接条件,查询仍然是有效的,执行的结果集却是一个交叉查询

  

   错误例子 

2008技术内幕:T-SQL语言基础 联接查询摘记
SELECT  e.empid ,e.firstname ,e.lastname ,o.orderid
FROM    hr.Employees AS eJOIN
        JOIN Sales.Orders AS o

错误:Incorrect syntax near ‘o‘.
2008技术内幕:T-SQL语言基础 联接查询摘记

   如果使用的是ANSI SQL-89标准语法,忘记了写连接条件, 

2008技术内幕:T-SQL语言基础 联接查询摘记
SELECT  e.empid ,e.firstname ,e.lastname ,o.orderid
FROM    hr.Employees AS e ,
        Sales.Orders AS o
2008技术内幕:T-SQL语言基础 联接查询摘记

  那么恭喜得了一个大数据的大奖!爽歪歪!逻辑一复杂,错误在哪里也不知。

  坚持推荐使用ANSI-92的原因:

  1.是保持一致性(都有关键字)

  2.减少错误(以防逻辑条件被遗漏)

  3.提高维护性(一个写好的存储过程,你也不知道会被后面的人改几次,本来是个交叉联接改来改去成了内联接!这个是你无法预算的,但是这是可避免,直接标明cross join.)

 

  组合联接:就是联接条件涉及两边多个列的查询 

2008技术内幕:T-SQL语言基础 联接查询摘记
SELECT  *
FROM    tabName1 AS A
        INNER JOIN tabName2 AS B ON A.id1 = B.id
                                    AND A.id = B.id2
2008技术内幕:T-SQL语言基础 联接查询摘记

 

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

2008技术内幕:T-SQL语言基础 联接查询摘记
SELECT  *
FROM    tabName1 AS A
        INNER JOIN tabName2 AS B ON A.id1 > B.id
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  多表联接

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

    通常,当From子句中包含多个表运算符时,表运算符在逻辑上是按从左到右的顺序处理的。 

2008技术内幕:T-SQL语言基础 联接查询摘记
 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
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  外联接

    外联接是在ANSI SQL-92中才被引入的,因此它只有一种标准语法。逻辑步骤:笛卡尔积、on过滤、添加外部行。

  在外联接中,要把一个表标记为“保留的”表,可以在表名之间使用关键字 left outer join、right outer join、full outer join,其中outer关键字是可选的(left join 、right join、full join )。

  left关键字表示左边的表的行是保留的,right关键字表示右边表的行是保留的,full关键字则表示左右两边表的行都是保留的。对于来自联接的非保留表的那些列,追加的外部行中的这些列则用null作为占位符。

2008技术内幕:T-SQL语言基础 联接查询摘记
SELECT  C.custid ,C.companyname ,O.orderid
FROM    Sales.Customers AS C
        LEFT JOIN Sales.Orders AS O ON C.custid = O.custid
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  题目:查询订单表中所有订单,查出从2007年1月1日到2010年12月12日之间的每个日期输订单个数。

2008技术内幕:T-SQL语言基础 联接查询摘记
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) 
2008技术内幕:T-SQL语言基础 联接查询摘记

  思路:

    --1 建立一个辅助表,往里面添加数据,从2006年1月1日到2014年03月17日
    --2 利用外联接查询

2008技术内幕:T-SQL语言基础 联接查询摘记

 

  ISNULL()函数

  语法

  isnull(check_expression,replacement_value) returns int

 

2008技术内幕:T-SQL语言基础 联接查询摘记
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)
2008技术内幕:T-SQL语言基础 联接查询摘记

  图

2008技术内幕:T-SQL语言基础 联接查询摘记

 

  外联接的条件过滤

  对外联接表中非保留表的列值进行过滤,这通常就是存在错误的一个标志。因为外联接得到的外部行中的值有可能是null,而null<运算符><值>运算,得到的只会是Unknow。

  例子

 

2008技术内幕:T-SQL语言基础 联接查询摘记
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
2008技术内幕:T-SQL语言基础 联接查询摘记

  上面这条查询语句是对Customers表和Orders表执行左联接操作,Customers作为保留表,在where条件筛选之前,o.orderdate是有两条为null的值使用O.orderdate >= ‘20070101‘过滤条件后,直接将这两条剔除了,因为null和任何比较都是unknow,而where只支持逻辑值为true的表达式,这样的查询条件会让所有外部行都被过滤掉,效果上相当于抵消了外联接的作用,换句话说,这里就是把外联接当成内联接用了。

 

  练习
    1-1.执行以下代码 

2008技术内幕:T-SQL语言基础 联接查询摘记
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
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  1-2 写一条查询语句,把所有雇员记录复制5次。

2008技术内幕:T-SQL语言基础 联接查询摘记
SELECT  empid ,firstname ,lastname ,n
FROM    HR.Employees AS E
        CROSS JOIN dbo.Nums AS T
WHERE   n < = 5
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  1-3 写一个查询,为每个雇员从2009年06月12日致2009年6月12日范围内的每天返回一行。

2008技术内幕:T-SQL语言基础 联接查询摘记
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 
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  2.返回来自美国的客户,并为每个客户返回其订单总数和商品交易总数量。

2008技术内幕:T-SQL语言基础 联接查询摘记
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
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  3.返回客户及其订单信息,包括没有下过任何订单的客户.

2008技术内幕:T-SQL语言基础 联接查询摘记
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
2008技术内幕:T-SQL语言基础 联接查询摘记

 

  4.返回在2007年02月12日下过订单的客户,以及他们的订单,同时也返回在2007年2月12日没有下过订单的客户.

2008技术内幕:T-SQL语言基础 联接查询摘记
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
2008技术内幕:T-SQL语言基础 联接查询摘记

2008技术内幕:T-SQL语言基础 联接查询摘记

  5.返回所有客户信息,并根据客户是否在2007年2月12日日下过订单,再为每个客户返回一列 Yes/No值。上一题的扩展。用case when

2008技术内幕:T-SQL语言基础 联接查询摘记
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
2008技术内幕:T-SQL语言基础 联接查询摘记

2008技术内幕:T-SQL语言基础 联接查询摘记,布布扣,bubuko.com

2008技术内幕:T-SQL语言基础 联接查询摘记

上一篇:安装&初始化mysql过程中的问题


下一篇:MSSQLSERVER2005安装指导