SELECT 语法和逻辑处理顺序 (含示例)

 

SELECT 语法和逻辑处理顺序

 

虽然 select语句的完整语法较复杂，但其主要子句可归纳如下：

 

(8)  select (9)distinct (11)<top_specification> <select_list>

(12)into <new_table>

(1)  from <left_table>

(3)      <join_type> join <right_table>

(2)      on <join_condition>

(4)  where <where_condition>

(5)  group by<group_by_list>

(6)  with {cube| rollup}

(7)  having <having_condition>

(10)order by <order_by_list>

 

其中，每个关键字都是一个独立的逻辑处理步骤，而关键字之前的数字代表了它在查询语句中的逻辑处理顺序。

 

SQL和其他编程语言最显著的不同之处就是代码处理的顺序。对于大多数编程语言，代码的书写顺序就是它的处理顺序。对于SQL，第一个处理的子句是from子句，而select子句，虽然是第一个出现的，但几乎是最后一个处理的。

 

   每个步骤生成的一个虚拟表会作为下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或外部查询）来说是不可见的。返回给调用者的，只是最后一个步骤生成的表。如果某个子句不在查询里，那么相应的步骤就会直接被跳过。

 

基于客户/订单情景的样例查询

 

   为了详细描述逻辑处理的各阶段，我们一起来完成一个样例查询。首先，运行以下脚本来创建表Customers和Orders，并写入样例数据。

 

use tempdb

if object_id('Customers','U')is not nullbegin

  drop table Customers

end

if object_id('Orders','U')is not nullbegin

  drop table Orders

end

Go

create table Customers(

customer_id int,

name       char(20),

gender     char(1)

)

insert Customers select 1,'AmitPaul','M'

insert Customers select 2,'DhaniLennevald','M'

insert Customers select 3,'MarieFredriksson','F'

insert Customers select 4,'PerGessle','M'

create table Orders(

order_id       int,

customer_id    int,

product_id     char(10)

)

insert Orders select 1,3,'B000XGJH1O'

insert Orders select 2,2,'B000GP8448'

insert Orders select 3,2,'B000FPOJOS'

insert Orders select 4,1,'B000FQ2D5E'

insert Orders select 5,3,'B000G0HJ3K'

insert Orders select 6,2,'B0011WMIME'

insert Orders select 7,1,'B000IONGWM'

select *from Customers

select *from Orders

 

查询语句的要求是返回订单总数不超过2的男性客户的客户编号、姓名及订单总数，并按照订单总数升序排序。

 

select c.customer_id,min(c.name)as name

,count(o.order_id)as order_count

  from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

  where c.gender='M'

  group by c.customer_id

  having count(o.order_id)<=2

  order by order_count

 

步骤1：生成笛卡尔积（CROSS JOIN）

 

笛卡尔积是在from子句中出现的头两个表之间进行的，结果便是生成了虚拟表VT1。VT1包含了左表中每一行和右表中每一行的所有可能的组合（左表就是查询语句中出现在join关键字之前的表）。如果左表有n行，右表有m行，VT1就有n×m行。VT1中的字段是由源表的名称限定的（作前缀的），如果查询中指定了表的别名，这个前缀也可以使用别名。在随后的步骤中（步骤2以及后面的步骤），如果对某个字段名的引用是有歧义的（字段名出现在多个输入表中），那么该字段名必须是表限定的（比如，c.customer_id）。对于只出现在一个输入表中的字段名，指定表限定符是可选的（比如，o.order_id或order_id）。

 

select c.customer_id as [c.customer_id],c.nameas [c.name]

  ,c.gender as[c.gender],o.order_id as[o.order_id]

  ,o.customer_id as [o.customer_id],o.product_idas [o.product_id]

  from Customers as c crossjoin Orders as o

 

   以上脚本可以用来模拟出虚拟表VT1，共28行（4×7）。

 

步骤2：应用ON过滤条件（JOIN条件）

 

   on过滤条件是查询中可以指定的三个可能的过滤条件（on、where和having）中最先出现的一个。on过滤条件中的逻辑表达式应用到前一个步骤返回的虚拟表VT1中的所有行。只有对于<join_condition>条件为真的行最终写入到当前步骤所返回的虚拟表VT2中。

 

select [Match?]=

  case

     when c.customer_id=o.customer_id then 'TRUE'

     else 'FALSE'

  end,c.customer_id as [c.customer_id],c.nameas [c.name]

  ,c.gender as[c.gender],o.order_id as[o.order_id]

  ,o.customer_id as [o.customer_id],o.product_idas [o.product_id]

  from Customers as c crossjoin Orders as o

 

以上语句可以用来模拟出虚拟表VT1应用on过滤条件之后的逻辑结果，其中只有<join_condition>='TRUE'的记录被写入到虚拟表VT2中，可以用以下脚本来模拟VT2：

 

select [Match?]=

  case

     when c.customer_id=o.customer_id then 'TRUE'

     else 'FALSE'

  end,c.customer_id as [c.customer_id],c.nameas [c.name]

  ,c.gender as[c.gender],o.order_id as[o.order_id]

  ,o.customer_id as [o.customer_id],o.product_idas [o.product_id]

  from Customers as c crossjoin Orders as o

  where c.customer_id=o.customer_id

 

步骤3：添加外部行

 

这个步骤只和outer join有关。通过指定outer join的类型（left、right或full），可以把一个或两个输入表标记为保留表。将一个表标记为保留表，意味着该表的所有行都会被返回，即使是被<join_condition>滤除的。left outer join将左表标记为保留表，right outer join将右表标记为保留表，而full outer join把两个表都标记为保留表。步骤3返回VT2中的行，加上保留表中没有在步骤2中匹配到的行。这些添加进来的行被称作外部行。外部行中属于非保留表的属性（字段值）被赋值为null。虚拟表VT3就这样生成了。

在这个例子中，要求返回订单总数不超过2的男性客户信息及订单总数，因此结果中可能包含订单数为0的客户，也就是说在Customers表中有用户信息，但是在Orders表中没有匹配的行。通过在Customers和Orders表之间执行left outer join，将Customers表标记为保留表，就可以够返回没有下过订单的客户，这里只有Per Gessle没有匹配的订单，因此作为外部行添加到虚拟表VT3中，而对于Orders表中的属性被赋予null。

 

select [Match?]='OUTER',c.customer_id as [c.customer_id]

  ,c.name as[c.name],c.gender as[c.gender],null as [o.order_id]

  ,null as [o.customer_id],null as[o.product_id]

  from Customers as c wherec.customer_id not in(

     select o.customer_id from Orders as o)

 

以上脚本可以模拟外部行，再加上虚拟表VT2中的行，就生成了虚拟表VT3，模拟VT3的脚本如下：

 

select [Match?]=

  case

     when c.customer_id=o.customer_id then 'TRUE'

     else 'FALSE'

  end,c.customer_id as [c.customer_id],c.nameas [c.name]

  ,c.gender as[c.gender],o.order_id as[o.order_id]

  ,o.customer_id as [o.customer_id],o.product_idas [o.product_id]

  from Customers as c crossjoin Orders as o

  where c.customer_id=o.customer_id

union

select [Match?]='OUTER',c.customer_id as [c.customer_id]

  ,c.name as[c.name],c.gender as[c.gender],null as [o.order_id]

  ,null as [o.customer_id],null as[o.product_id]

  from Customers as c wherec.customer_id not in(

     select o.customer_id from Orders as o)

 

以上脚本可以简化为：

 

select c.customer_id as [c.customer_id],c.nameas [c.name]

  ,c.gender as[c.gender],o.order_id as[o.order_id]

  ,o.customer_id as [o.customer_id],o.product_idas [o.product_id]

  from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

 

注意： 如果进行join的表超过两个，那么在VT3与from子句中的第三个表之间执行步骤1至3。如果from子句中有更多的表，就需要继续重复这个过程，而最终的虚拟表就作为下一个步骤的输入。

 

步骤4：应用WHERE过滤条件

 

where过滤条件应用到上个步骤返回的虚拟表中的所有行。只有对于<where_condition>条件为真的行成为这个步骤返回的虚拟表VT4的一部分。

VT3中的客户Marie Fredriksson这一行被去掉了，因为该客户的gender属性为'F'。此时，虚拟表VT4就生成了。模拟VT4的脚本如下：

 

select c.customer_id as [c.customer_id],c.nameas [c.name]

  ,c.gender as[c.gender],o.order_id as[o.order_id]

  ,o.customer_id as [o.customer_id],o.product_idas[o.product_id]

  from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

  where c.gender='M'

 

注意1：因为此时数据还未进行分组（group by），所以不能使用聚集过滤条件，比如，不可以这样写：where orderdate = max (orderdate)。同样，也不能引用select列表中创建的字段别名，因为select列表还没有被处理，比如，不可以这样写：select year(orderdate) as orderyear...where orderyear > 2008。

 

注意2：包含outer join子句的查询有个令人困惑的地方，应该在on过滤条件中还是在where过滤条件中指定逻辑表达式。两者主要的区别是，on是在添加外部行（步骤3）之前应用的，where是在步骤3之后应用的。保留表中被on过滤条件排除的行不是确定的，因为步骤3会把它添加回来；而被where过滤条件排除的行是确定的。

查询语句要求只保留来自男性客户的记录，所以必须在where子句中指定该过滤条件（where c.gender='M'）。如果将该条件放在on子句中，那么在步骤3中，不满足on过滤条件，但是在保留表中出现的记录，比如客户Marie Fredriksson，就会被添加回来，结果就可能和用户所期望的不一致：

 

select c.customer_id as [c.customer_id],c.nameas [c.name]

  ,c.gender as[c.gender],o.order_id as[o.order_id]

  ,o.customer_id as [o.customer_id],o.product_idas [o.product_id]

   from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

  and c.gender='M'

 

提示： 只有在使用outer join的时候，on和where子句之间才有逻辑区别。当使用inner join的时候，在哪里指定逻辑表达式都无所谓了，因为步骤3被跳过了。过滤条件一个接一个地被应用，不会有中间步骤。

 

步骤5：分组（GROUPBY）

 

上个步骤中返回的表中的记录按照组来排列。group by子句的字段列表中的每个唯一的值的组合，就成了一个组。上个步骤中的每个基本行都附属于一个（并且只有一个）组。虚拟表VT5生成了。VT5包含两个部分：GROUPS部分由实际的组构成，而RAW部分由附加在组上的来自上个步骤的基本行构成。

 

set nocount on

declare my_cursor cursor

for select c.customer_id as [c.customer_id],c.nameas [c.name]

  ,c.gender as[c.gender],o.order_id as[o.order_id]

  ,o.customer_id as [o.customer_id],o.product_idas [o.product_id]

  from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

  where c.gender='M'

open my_cursor

declare @c_customer_id char(16)

      ,@p_customer_id char(16)

      ,@name         char(20)

      ,@gender       char(11)

      ,@order_id     char(13)

      ,@o_customer_id char(16)

      ,@product_id   char(15)

print 'GROUPS         RAW'

print 'c.customer_id  c.customer_id  c.name             '

    +'c.gender  o.order_id  o.customer_id  o.product_id'

fetch nextfrom my_cursor into @c_customer_id,@name,@gender

  ,@order_id,@o_customer_id,@product_id

while @@fetch_status=0

  begin

     if @p_customer_id=@c_customer_id begin

        print replicate('',16)+@c_customer_id+@name+@gender

        +isnull(@order_id,'NULL')+isnull(@o_customer_id,'NULL')

        +isnull(@product_id,'NULL')

     end else begin

        print replicate('-',104)

        print @c_customer_id+@c_customer_id+@name+@gender

        +isnull(@order_id,'NULL')+isnull(@o_customer_id,'NULL')

        +isnull(@product_id,'NULL')

     end

     set @p_customer_id=@c_customer_id

     fetch next frommy_cursor into@c_customer_id,@name,@gender

        ,@order_id,@o_customer_id,@product_id

  end

print replicate('-',104)

close my_cursor

deallocate my_cursor

 

如果查询中指定了group by子句，接下来的所有步骤（having、select等等）就只能使用在每个组中的结果为标量（单个）值的表达式。换句话说，结果要么是group by列表中含有的字段/表达式，例如c.customer_id，要么是聚集函数，比如count(o.order_id)。有这个限制的原因是，最终结果集中的每个组只能生成一条记录（被过滤掉的除外）。设想一下如果select列表中指定的是select c.customer_id,o.order_id，那么对于客户Dhani Lennevald来说应该返回什么样的结果。该组中将出现两个不同的order_id值；因此答案就是不确定的。SQL不会接受这样的请求。另一方面，如果指定：select c.customer_id,count(o.order_id)as order_count，那么Dhani Lennevald的答案就是确定的：就是3。

 

在这个阶段中，认为NULL之间是相等的。就是说，所有的NULL都被分到一个组里，好比是一个已知的值。

 

步骤6：应用CUBE或ROLLUP选项

 

   如果指定了cube或rollup，就会产生超组（supergroups），并且添加到上个步骤返回的虚拟表的组中。虚拟表VT6就生成了。

   在这个例子中步骤6被跳过了，因为在样例查询中没有指定cube和rollup。

 

步骤7：应用HAVING过滤条件

 

having过滤条件作用在上个步骤返回的虚拟表的组上。只有对于<having_condition>条件为真的组，才会加入到这个步骤返回的虚拟表VT7中。having过滤条件是第一个，也是唯一一个作用在分组数据上的过滤条件。模拟VT7的脚本如下：

 

set nocount on

set ansi_warnings off

if object_id('tempdb.dbo.#VT','U')is not nullbegin

  drop table #VT

end

;with CTE_OrderCount(customer_id,order_count)as(

select c.customer_id,count(o.order_id)

  from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

  group by c.customer_id

)select c.customer_id as [c.customer_id],c.nameas [c.name]

   ,c.gender as[c.gender],o.order_id as[o.order_id]

   ,o.customer_id as [o.customer_id],o.product_idas [o.product_id]

   ,oc.order_count

   into #VT

   from Customers as c

   inner join CTE_OrderCount as oc onoc.customer_id=c.customer_id

   left outer joinOrders as o on c.customer_id=o.customer_id

   where c.gender='M'and order_count<=2

declare my_cursor cursor

for select *from #VT

open my_cursor

declare @c_customer_id char(16)

      ,@p_customer_id char(16)

      ,@name         char(20)

      ,@gender       char(11)

      ,@order_id     char(13)

      ,@o_customer_id char(16)

      ,@product_id   char(15)

      ,@order_count  char(11)

print 'GROUPS         RAW'

print 'c.customer_id  c.customer_id  c.name             '

    +'c.gender  o.order_id  o.customer_id  o.product_id'

    +'  order_count'

fetch nextfrom my_cursor into @c_customer_id,@name,@gender

  ,@order_id,@o_customer_id,@product_id,@order_count

while @@fetch_status=0

  begin

     if @p_customer_id=@c_customer_id begin

        print replicate('',16)+@c_customer_id+@name+@gender

        +isnull(@order_id,'NULL')+isnull(@o_customer_id,'NULL')

        +isnull(@product_id,'NULL')+isnull(@order_count,'NULL')

     end else begin

        print replicate('-',118)

        print @c_customer_id+@c_customer_id+@name+@gender

        +isnull(@order_id,'NULL')+isnull(@o_customer_id,'NULL')

        +isnull(@product_id,'NULL')+isnull(@order_count,'NULL')

     end

     set @p_customer_id=@c_customer_id

     fetch next frommy_cursor into@c_customer_id,@name,@gender

        ,@order_id,@o_customer_id,@product_id,@order_count

  end

print replicate('-',118)

close my_cursor

deallocate my_cursor

 

注意： 在这里使用count(o.order_id)而不是count(*)，这一点是很重要的。因为这里是outer join，虽然客户Per Gessle没有订单，但是结果集里包含该客户的信息，因此count(*)的值为1；然而o.order_id的值为null，count(<表达式>)会像其他聚集函数一样忽略null，所以count(o.order_id)的值为0。

 

步骤8：处理SELECT列表

 

尽管select列表最先出现在查询中，但却是在第八个步骤处理的。select阶段构建出最终返回给调用者的表。select列表中的表达式可以使用上个步骤的虚拟表中的基本字段和经过处理的基本字段。模拟VT8的脚本如下：

 

select c.customer_id,min(c.name)as name

  ,count(o.order_id)as order_count

  from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

  where c.gender='M'

  group by c.customer_id

  having count(o.order_id)<=2

 

重要1： 如果是个聚集查询，即查询中包含group by这个步骤，那么select列表中可以直接引用出现在GROUPS部分（group by列表）中的字段；如果引用的是RAW部分的字段，就必须是聚集的。

在这个例子中，要求返回的字段是客户编号、姓名及订单总数，其中，customer_id是GROUPS部分中的字段，因此可以直接引用；除此之外，name是RAW部分中的字段，因此要放到聚集函数中，由于同一组中的name值是一样的，所以这个聚集函数可以是min()、max()或其他函数；而订单总数是计算出来的列，是在order_id（RAW部分中的字段）上使用了count()聚集函数。

对于不是基本字段的表达式应该使用别名（Alias），从而在结果表中获得一个字段名。例如，min(c.name)as name,count(o.order_id)as order_count。

 

   重要2： select列表中创建的别名不能被用在之前的步骤中。事实上，表达式别名甚至不能被用在同一个select列表里的其他表达式中。有该限制的原因是SQL的另一个独特的地方，瞬时操作（All-At-Once Operation）。例如，在下面的select列表中，表达式求值的逻辑顺序应该是无关紧要的，也是不能保证的：select c1+1as e1,c2+1as e2。因此，下面的SELECT列表是不予支持的：select c1+1as e1,e1+1as e2。你只能在select列表之后的步骤中，比如order by步骤中，再次使用字段别名。例如，select year(orderdate)as orderyear ... orderby orderyear。

 

这个瞬时操作的概念可能比较难理解。例如，在大多数编程环境中，在变量之间交换值的时候需要一个临时变量。然而，在SQL中交换表中字段的值，可以这样做：

 

use tempdb

if object_id('Table_A','U')is not nullbegin

  drop table Table_A

end

Go

create table Table_A(column_1 int,column_2int)

insert Table_A select 1,23

insert Table_A select 4,56

insert Table_A select 7,89

select *from Table_A

 

update Table_A set column_1=column_2,column_2=column_1

select *from Table_A

 

   逻辑上，你应该假设整个操作是即刻发生的。就好像表T1在操作过程中始终没有改变，而直到整个操作完成后，结果才覆盖了源数据。

 

步骤9：应用DISTINCT子句

 

   如果在查询中指定了distinct子句，上个步骤返回的虚拟表中重复的行会被删除，虚拟表VT9就生成了。

   这个例子中，步骤9被跳过了，因为查询中没有指定distinct。事实上，如果已经使用了group by，那么distinct就是冗余的，就不再会删除任何行。

 

步骤10：应用ORDERBY子句

 

上个步骤中返回的行会按照order by子句中指定的字段列表来排序，从而返回游标VC10。这个步骤是第一个，也是唯一可以重新使用select列表中创建的字段别名的步骤。模拟VC10的脚本如下：

 

select c.customer_id,min(c.name)as name

,count(o.order_id)as order_count

  from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

  where c.gender='M'

  group by c.customer_id

  having count(o.order_id)<=2

  order by order_count

 

按照ANSISQL：1992和ANSISQL：1999的标准，如果指定了distinct，那么order by子句中的表达式就只能访问上个步骤返回的虚拟表VT9。也就是说，只能对select列表中的表达式进行排序。ANSI SQL：1992中即使在没有指定distinct的时候也有同样的限制。然而，ANSI SQL：1999通过允许select阶段中的输入和输出虚拟表都能被访问，来加强对order by的支持。就是说，如果没有指定distinct，就可以在order by子句中指定任何select子句中允许的表达式。就是说，最终结果集中没有返回的表达式也可以排序。T-SQL始终采用ANSI SQL：1999的执行方式。

 

select distinct customer_id

  from Orders

  order by order_id

 

以上脚本的目的是按照订单编号的顺序返回唯一的客户编号，但无法通过语法检查。因为编号为3的客户有1、5两张订单，编号为2的客户有2、3和6三张订单，在使用distinct消除重复的customer_id过程中，同一customer_id的不同order_id将无法处理。

如果把上述脚本中的distinct去掉，就可以执行。其含义是：按照订单编号的顺序返回客户编号。

 

   重要： 这个步骤与所有其他步骤不同的地方是，它返回的不是一个有效的表，而是一个游标。要记住SQL是基于“集合”的理论。一个集合中的行没有预先确定的顺序；它是成员的逻辑积聚，而不考虑成员之间的顺序。对表中的行进行排序的查询返回这样一个对象：按照特定的物理顺序组织的行集。ANSI称这种对象为游标（cursor）。理解这个步骤是正确理解SQL的最基本的前提之一。

 

   注意： 尽管SQL不会为表中的行假定一个顺序，但事实上还是会按照创建的先后顺序来为字段维护位置序数（数据页上的Slot ID）。指定select * 保证字段会按照创建时的顺序返回。

 

   因为这个步骤并不返回一个表（而是一个游标），所以带有order by子句的查询不能被用作为一个表表达式。比如说，视图、嵌入式表值函数、子查询、衍生表或者公用表表达式（CTE）。确切地说，返回给客户端应用程序的必须是物理结果集。以下子查询就是无效的，会产生一个错误：

 

select *from Customers where customer_id in (

  select customer_id from Orders order byorder_id)

 

   在SQL中，不允许在表表达式中使用order by子句。在T-SQL中，对这个规则有个例外，就是应用top选项，下一个步骤中有专门的描述。

 

order by步骤中认为null之间是相等的。就是说，null在排序时被分在一起。ANSI把有关排序时，null和已知值比较的大小问题留给实施方。T-SQL在排序时认为null比已知值小（即null排在前面）。

 

select c.customer_id,o.order_id

  from Customers as c

  left outer joinOrders as o on c.customer_id=o.customer_id

  where c.gender='M'

  order by o.order_id

 

步骤11：应用TOP选项

 

   top子句允许你指定返回的行的数量或是百分比（该百分比取整数）。在SQL Server 2000中，top的输入值必须是常量，而在SQL Server 2005中，输入值可以是任何独立表达式。返回VC10开始部分的指定数量的行，表VT11就生成了。

 

   注意： top子句是T-SQL中特定的，不是关系型的。

 

   当查询中同时指定了top子句和order by子句，并且order by列表是唯一的，那么结果就是确定的。

如果查询中同时指定了top子句和order by子句，但order by列表有重复值，那么结果就不确定了。要在这种情况下保证结果的确定性，就需要在top子句中使用with ties选项。此时，SQLServer会检查实际返回的最后一条记录，并返回表中所有与该记录排序值相同的其他记录。

之前提到过，带有order by子句的查询返回的是游标，而不是表表达式。通过指定非标准的、非关系型的top选项，含order by子句的查询返回的是关系型结果，因此可以用于表表达式：

 

select *from Customers where customer_id in (

  select top 3 customer_id from Orders order byorder_id)

 

如果查询中指定了top子句，但没有order by子句，那么SQLServer会返回最先访问到的指定数量的行。尽管执行计划被限制为表扫描或聚集索引扫描，但实际上只需读取所需的数据页。

这样的查询主要用来大致看一下表结构和数据样本，但如果用在过程化的代码中，则缺乏关系型含义，而且它返回的结果是不确定的。有人说，“不具确定性的编程是一种犯罪”。你同意吗？

 

   我们的例子中没有指定top，所以步骤11被跳过了。

 

步骤12：将查询结果保存到新创建的表中

 

   通过指定into子句，可以将查询结果保存到新创建的表中，该表的字段名称和数据类型的定义来源于select列表。如果已经存在同名的表，查询将会报错。这个例子中步骤12被跳过了。