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

- 日理万妓 2022-03-30 06:24 306阅读 0赞

**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

create table Customers(

customer\_id int,

namechar(20),

genderchar(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\_idint,

customer\_idint,

product\_idchar(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)

,@namechar(20)

,@genderchar(11)

,@order\_idchar(13)

,@o\_customer\_id char(16)

,@product\_idchar(15)

print 'GROUPSRAW'

print 'c.customer\_idc.customer\_id c.name'

\+'c.gendero.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)

,@namechar(20)

,@genderchar(11)

,@order\_idchar(13)

,@o\_customer\_id char(16)

,@product\_idchar(15)

,@order\_count char(11)

print 'GROUPSRAW'

print 'c.customer\_idc.customer\_id c.name'

\+'c.gendero.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

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选项**