SQL.Server.2005.T-SQL.Querying 流水账 chapter1

1.sql语句执行顺序

 

(8)  SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(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>

 

这也解释了为什么

 

select customerid as mainid from customers where mainid ='111'

 

会报mainid找不到，因为这个时候还没执行as这句。

 

2.A confusing aspect of queries containing an OUTER JOIN clause is whether to specify a logical expression in the ON filter or in the WHERE filter. The main difference between the two is that ON is applied before adding outer rows (step 3), while WHERE is applied after step 3. An elimination of a row from the preserved table by the ON filter is not final because step 3 will add it back; 

while an elimination of a row by the WHERE filter is final.Inner Join 不存在此问题，因为跳过步骤3.

比如：

select a.* from customers a ,Orders b where a.customerid=b.customerid
select a.* from customers aleft outer join Orders b on a.customerid=b.customerid

后一种方法，Orders中没有的customerid，也会包含进来。就是相当于做完查询又做了一个JOIN？

 

3.all-at-once 

操作置换列可以用

 

UPDATE dbo.T1 SET c1 = c2, c2 = c1;

 

因为当整个操作都结束，table才会更新。

4.Table中的行是没有顺序的：SQL is based on set theory。

sql是建立在集合论基础上的，本来集合中的行是不关注顺序的，带有排序的查询返回拥有特定物理排序的对象，ANSI 称之为游标。

明白这点对理解SQL很重要。

5.Sql server 2005 新增操作JOIN, APPLY, PIVOT, and UNPIVOT

6.Set operations compare complete rows between the two inputs. UNION returns one result set with the rows from both inputs. If the ALL option is not specified, UNION removes duplicate rows from the result set. EXCEPT returns distinct rows that appear in the left input but not in the right. INTERSECT returns the distinct rows that appear in both inputs.