1.Not in和null

问题:在使用not in 进行查询的时候,如果括号里面有值为null,则无法查询到记录。

假设我们有如下2个表,想查询出没有在产品表中出现的颜色。

--颜色表
----------
Black
Blue
Green
Red

--产品表:
sku  product_description  color
---- -------------------- ------
1    Ball                 Red
2    Bike                 Blue
3    Tent                 NULL

一般我们会写出如下的查询语句:

SELECT  C.color
FROM    Colors AS C
WHERE   C.color NOT IN ( SELECT P.color
                         FROM   Products AS P );

你可能会期望返回两行,包含Black和Green,但是实际执行结果却是0条记录。

color
----------
 
(0 row(s) affected)

问题就出在Products表的NULL值。因为SQL使用了three-valued逻辑(包含true、false和unknown三种值)。NULL不是一个值,而是一个表示信息缺失的标记。当NOT IN操作符应用到前面子查询语句返回的值时,IN谓词里面会被转换成如下:

"color NOT IN (Red, Blue, NULL)"

这等效于下面的语句

"NOT(color=Red OR color=Blue OR color=NULL)"

表达式“color=NULL”的值是UNKNOWN,按照three-valued的逻辑,NOT UNKNOWN 的值也是UNKNOWN。结果所有的行都被排除掉了,从而返回空。当需求变更,一个非空的列被修改成可空时,这个错误就很可能发生。为了应对这种需求变更,在设计之初,我们就要确保查询语句能够处理这种错误。

一个解决办法就是使用Exists谓词来代替In,因为Exists使用two-valued逻辑(只存在true或者false)。

SELECT  C.color
FROM    Colors AS C
WHERE   NOT EXISTS ( SELECT *
                     FROM   Products AS P
                     WHERE  C.color = P.color );

还有另外三种解决办法:

/*子查中加上 IS NOT NULL  */
SELECT  C.color
FROM    Colors AS C
WHERE   C.color NOT IN ( SELECT P.color
                         FROM   Products AS P
                         WHERE  P.color IS NOT NULL );
 
/* EXCEPT */
SELECT  color
FROM    Colors
EXCEPT
SELECT  color
FROM    Products;
 
/* LEFT OUTER JOIN */
SELECT  C.color
FROM    Colors AS C
        LEFT OUTER JOIN Products AS P ON C.color = P.color
WHERE   P.color IS NULL;

备注:
EXCEPT是指在第一个集合中存在,但是不存在于第二个集合中的数据。
INTERSECT是指在两个集合中都存在的数据。
UNION 是取并集

2.Where条件中,在索引列上使用函数

SELECT  customer_name
FROM    Customers
WHERE   LEFT(customer_name, 1) = 'L';

虽然这个查询看起来很直接,但是你将发现在customer_name上的索引并没有被使用到,执行计划显示的是索引扫描(index scan),而不是我们希望的索引查找。
产生这个问题的原因是我们将索引列传递给了函数,这使得查询引擎必须计算每行的值。这种情况下,where语句被认为是“non-SARGable”(不可优化)的,然后查询优化器能做的最好操作就是执行全索引扫描或者是全表扫描。
为了保证索引被使用,我们要避免在索引列使用函数。
上面的查询可以改写成如下:

SELECT  customer_name
FROM    Customers
WHERE   customer_name LIKE 'L%';

下面是两种语句实际执行的情况截图:

3.不正确的子查询列

使用嵌套子查询的时候,最好在所有列名前面加上表的别名,这样不容易出错。
例如如下就是一个典型的错误:

--Order 表:
sale_date  sale_amount
---------- -----------
2009-01-01 120.50
2009-01-02 115.00
2009-01-03 140.80
2009-01-04 100.50

--Calendar 表:
calendar_date holiday_name
------------- ----------------
2009-01-01    New Year's Day
2009-01-02    NULL
2009-01-03    NULL
2009-01-04    NULL
2009-01-05    NULL

我们现在要查询节假日的销售金额:

SELECT  sale_date ,
        sale_amount
FROM    Sales AS S
WHERE   sale_date IN ( SELECT   sale_date
                       FROM     Calendar AS C
                       WHERE    holiday_name IS NOT NULL );

这个语句是错误的,因为子查询的字段应该是calendar_date,而不是sale_date。虽然这个语句的返回结果不是我们期望的,但是这个查询并不会报错,因为当使用子查询的时候,外部查询的列都会暴露给子查询。
解决方案就是在使用到子查询的时候,给所有列都加上表的别名,这样就不会把列名写错地方了。

4.查询条件中数据类型错误

这是另一个典型的错误,通常很难被发现。很容易在谓词中搞错数据类型,这可能是存储过程的参数是一种数据类型,但是在使用该参数在另一个数据类型的列上去查询数据。另一个例子是join的时候使用不同类型的字段。
例如:

CREATE TABLE Customers
    (
      customer_nbr INT NOT NULL
                       PRIMARY KEY ,
      first_name VARCHAR(35) NOT NULL ,
      last_name VARCHAR(35) NOT NULL
    );

下面的存储过程会查询客户信息:

CREATE PROCEDURE GetCustomerByLastName
    @last_name NVARCHAR(35)
AS
    SELECT  first_name ,
            last_name
    FROM    Customers
    WHERE   last_name = @last_name;

注意,参数@last_name的类型是NVARCHAR,虽然代码能正常工作,但是SQL Server必须执行隐式的转换,将last_name列的类型转换成NVARCHAR,因为NVARCHAR具有更高的优先权。这回导致性能损失。隐式的转换在执行计划中显示为CONVERT_IMPLICIT。基于排序规则以及其他的原因,类型不匹配也可能会导致排除使用索引。

5.谓词的执行顺序

如果你熟悉查询处理的逻辑,那么你会期望一个查询按照下面的顺序执行:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
上面的顺序概述了执行查询的逻辑顺序。逻辑上,FROM最先白处理,用来确定数据集,接下来是WHERE谓词,接下来是GROUP BY,然后继续。然而,实际上,查询被以不同的顺序执行,查询优化器可以自由的查询计划中的移动表达式,进而产生低成本最低效率最大的计划来获取数据。这导致了一个共同的误解,WHERE中的过滤语句在下一个过程之前被应用。实际上,在真实的执行计划中,一个断言可能会非常晚才被应用。同样,断言的执行也不是按照从左到右的顺序执行的。例如:如果你有WHERE x=1 AND y=3,那么不保证x=1会被先执行。他们可能以任何顺序被执行。

--account表
account_nbr account_type    account_reference
----------- --------------- -----------------
1           Personal        abc
2           Business Basic  101
3           Personal        def
4           Business Plus   5

假如我们针对上面的表执行下面的查询:

SELECT  account_nbr ,
        account_reference AS account_ref_nbr
FROM    Accounts
WHERE   account_type LIKE 'Business%'
        AND CAST(account_reference AS INT) > 20;

将会报错:

"Conversion failed when converting the varchar value 'abc' to data type int"

因为谓词的执行顺序是不确定的。
要解决这个问题,我们可以尝试使用子表或者公共表达式:

SELECT  account_nbr ,
        account_ref_nbr
FROM    ( SELECT    account_nbr ,
                    CAST(account_reference AS INT) AS account_ref_nbr
          FROM      Accounts
          WHERE     account_type LIKE 'Business%'
        ) AS A
WHERE   account_ref_nbr > 20;

然而,这也会得到同样的错误,因为子表和CTE都会在查询计划里扩展,最后生成一个查询计划,在这个计划里面,谓词的执行顺序可能会被调整。
这种问题,最好的解决就是使用CASE语句:

SELECT  account_nbr ,
        account_reference AS account_ref_nbr
FROM    Accounts
WHERE   account_type LIKE 'Business%'
        AND CASE WHEN account_reference NOT LIKE '%[^0-9]%'
                 THEN CAST(account_reference AS INT)
            END > 20;

6.外关联和条件语句的位置

外连接是很好的工具,但是经常被误解和滥用。有的人看起来非常喜欢使用它们,并且几乎每个查询都使用,不管需不需要。
正确使用外连接的关键是理解查询处理外连接的逻辑步骤。下面是相关的处理阶段:
1.From语句中关联的2个表会产生一个交叉连接(笛卡尔乘积)。这个笛卡尔乘积是由第一张表和第二张表的行组成的,每种组合配对都会包含(2个表的行数相乘)。
2.ON 语句谓词被用来过滤那些满足谓词逻辑的行。
3.主表里面任何被第二步排除掉的行,都会被添加到返回结果中。其中,主表的字段是其实际值,非主表的字段值是NULL。
4.WHERE语句的谓词被应用。

一个外关联查询,根据查询语句的写法、以及Where语句的位置,可能产生完全不同的结果,看一个例子:有客户表和订单表

Customers 表:
customer_nbr customer_name
------------ --------------
1            Jim Brown
2            Jeff Gordon
3            Peter Green
4            Julie Peters

Orders 表:
order_nbr   order_date customer_nbr order_amt
----------- ---------- ------------ ----------
1           2008-10-01 1            15.50
2           2008-12-15 2            25.00
3           2009-01-02 1            18.00
4           2009-02-20 3            10.25
5           2009-03-05 1            30.00

我们的任务是查询所有客户列表,计算出每个客户花在订单上的总金额,订单日期要从2009年1月1日起。本能地,我们会写出如下查询语句:

SELECT  C.customer_name ,
        SUM(COALESCE(O.order_amt, 0)) AS total_2009
FROM    Customers AS C
        LEFT OUTER JOIN Orders AS O ON C.customer_nbr = O.customer_nbr
WHERE   O.order_date >= '20090101'
GROUP BY C.customer_name;

但是结果却不太好(没有列出所有客户):

customer_name  total_2009
-------------- ------------
Jim Brown      48.00
Peter Green    10.25

为了明白错误出在哪里,让我们按照逻辑查询的顺序回放查询步骤,第一步是两个表的全连接(cross join)

SELECT  C.customer_name ,
        O.order_amt
FROM    Customers AS C
        CROSS JOIN Orders AS O;

结果是两个表所有可能的行的匹配记录集合。

customer_name    order_amt  order_date
---------------- ---------- ----------
Jim Brown        15.50      2008-10-01
Jim Brown        25.00      2008-12-15
Jim Brown        18.00      2009-01-02
Jim Brown        10.25      2009-02-20
Jim Brown        30.00      2009-03-05
Jeff Gordon      15.50      2008-10-01
Jeff Gordon      25.00      2008-12-15
Jeff Gordon      18.00      2009-01-02
Jeff Gordon      10.25      2009-02-20
Jeff Gordon      30.00      2009-03-05
Peter Green      15.50      2008-10-01
Peter Green      25.00      2008-12-15
Peter Green      18.00      2009-01-02
Peter Green      10.25      2009-02-20
Peter Green      30.00      2009-03-05
Julie Peters     15.50      2008-10-01
Julie Peters     25.00      2008-12-15
Julie Peters     18.00      2009-01-02
Julie Peters     10.25      2009-02-20
Julie Peters     30.00      2009-03-05

第二步是应用Join语句的on条件

SELECT  C.customer_name ,
        O.order_amt ,
        O.order_date
FROM    Customers AS C
        INNER JOIN Orders AS O ON C.customer_nbr = O.customer_nbr;

这个查询的结果是那些有订单的客户,Julie没有订单,所以被排除在结果之外了。

customer_name  order_amt  order_date
-------------- ---------- ----------
Jim Brown      15.50      2008-10-01
Jeff Gordon    25.00      2008-12-15
Jim Brown      18.00      2009-01-02
Peter Green    10.25      2009-02-20
Jim Brown      30.00      2009-03-05

第三步是将主表外关联过滤掉的行添加到结果中

SELECT  C.customer_name ,
        O.order_amt ,
        O.order_date
FROM    Customers AS C
        LEFT OUTER JOIN Orders AS O ON C.customer_nbr = O.customer_nbr;

现在Julie被添加到结果集中了。注意,那些被第二步排除掉有在第三步添加回来的记录,其非主表的字段值都是null。

customer_name  order_amt  order_date
-------------- ---------- ----------
Jim Brown      15.50      2008-10-01
Jim Brown      18.00      2009-01-02
Jim Brown      30.00      2009-03-05
Jeff Gordon    25.00      2008-12-15
Peter Green    10.25      2009-02-20
Julie Peters   NULL       NULL

第四步 应用Where语句

SELECT  C.customer_name ,
        O.order_amt ,
        O.order_date
FROM    Customers AS C
        LEFT OUTER JOIN Orders AS O ON C.customer_nbr = O.customer_nbr
WHERE   O.order_date >= '20090101';
到这一步应该清晰了,问题就在于Where语句。Jeff被排除掉是因为没有2009年1月1号以后的订单,Julie被排除是因为完全没有订单。
要修正我们最初的查询,需要将where条件移到join条件中。
SELECT  C.customer_name ,
        SUM(COALESCE(O.order_amt, 0)) AS total_2009
FROM    Customers AS C
        LEFT OUTER JOIN Orders AS O ON C.customer_nbr = O.customer_nbr
                                       AND O.order_date >= '20090101'
GROUP BY C.customer_name;

这样查询结果就正确了,因为Jeff和Julie在join条件中被过滤掉了,但是在外关联行被添加会结果集的时候,有被添加到结果中了。

7.子查询返回多个值

类似下面这样的子查询:

SELECT  sku ,
        product_description ,
        ( SELECT    plant_nbr
          FROM      ProductPlants AS B
          WHERE     B.sku = A.sku
        ) AS plant_nbr
FROM    Products AS A;

我们开始写的时候,可能是返回一行数据,不会报错。但是随着生产数据的变化,有可能会返回多行,这个时候,SQL语句就会报错。
解决办法是使用JOIN:

SELECT  A.sku ,
        A.product_description ,
        B.plant_nbr
FROM    Products AS A
        JOIN ProductPlants AS B ON A.sku = B.sku;

还有WHERE语句中也可能会出现这样的问题:

"… column = (SELECT value FROM Table)"

这种情况下应该使用IN。

8.使用select *

如果一开始使用这样的查询:

INSERT  INTO PlasticProducts
        SELECT  *
        FROM    Products
        WHERE   material_type = 'plastic';

那么当表Products加字段时,上面的语句就会报错,最好是select的时候指定列名:

INSERT  INTO PlasticProducts
        ( sku ,
          product_description ,
          material_type
        )
        SELECT  sku ,
                product_description ,
                material_type
        FROM    Products
        WHERE   material_type = 'plastic';

如果在视图中使用SELECT * ,在基础表添加字段后,会产生更坏的结果。
注意:如果创建视图时指定SCHEMABINDING选项,那么当基础表的修改会影响视图时,修改就不能成功。

9.自定义标量函数

CREATE FUNCTION dbo.GetTotalSales ( @sku INT )
RETURNS DECIMAL(15, 2)
AS
    BEGIN
        RETURN(SELECT SUM(sale_amount)
         FROM Sales
         WHERE sku = @sku);
    END;

使用上面的函数来查询:

SELECT  sku ,
        product_description ,
        dbo.GetTotalSales(sku) AS total_sales
FROM    Products;

上面的函数会每行都调用一次,性能消耗会与行数成比例增长。正确的方式是,使用表值函数,或者直接使用JOIN连接,在主查询中计算。

SELECT  P.sku ,
        P.product_description ,
        SUM(S.sale_amount) AS total_sales
FROM    Products AS P
        JOIN Sales AS S ON P.sku = S.sku
GROUP BY P.sku ,
        P.product_description;

使用表值函数:

CREATE FUNCTION dbo.GetTotalSales ( @sku INT )
RETURNS TABLE
AS
RETURN
    ( SELECT    SUM(sale_amount) AS total_sales
      FROM      Sales
      WHERE     sku = @sku
    );

SELECT  sku ,
        product_description ,
        total_sales
FROM    Products AS P
        CROSS APPLY dbo.GetTotalSales(P.sku) AS S;

10.过度使用游标

在进行数据库开发的时候,应该尽量避免使用游标。因为一行一行的处理数据并不是sql Server擅长的工作,sql Server天生就是出来集合的。游标会使我们的解决方案太长,执行起来很慢,并且难于维护。

发表评论

电子邮件地址不会被公开。 必填项已用*标注