--SQLServer 2005のクエリ文の実行順序

--1.from--2.on--3.outer(join)--4.where--5.group by--6.cube|rollup--7.having--8.select--9.distinct--10.order by--11.top
1.論理照会処理手順連番(8)SELECT(9)DISTINCT(11) (1)FROM (3)　 JOIN (2)　　　 ON (4)WHERE (5)GROUP BY (6)WITH {CUBE | ROLLUP}(7)HAVING (10)ORDER BY
 
各ステップは、次のステップの入力として使用される仮想テーブルを生成します.最後のステップで生成されたテーブルのみが呼び出し元に返されます.句がない場合は、対応する手順をスキップします.1.FROM:FROM句の最初の2つのテーブルにデカルト積を実行し、仮想テーブルVT 1を生成する.2.ON:VT 1にONフィルタを適用する.それらだけが本物の行にVT 2を挿入します.3.OUTER(JOIN):OUTER JOINが指定されている場合、保留表に一致しない行が外部行としてVT 2に追加され、VT 3が生成されます.FROM句に2つ以上のテーブルが含まれている場合、前の結合で生成された結果テーブルと次のテーブルについて、すべてのテーブルが処理されるまで手順1～3を繰り返します.4.VT 3に対してWHEREフィルタを適用する.TRUEの行はVT 4に挿入されない.5.GROUP BY:GROUUPBY句の列リストからVT 4の行をグループ化し、VT 5を生成する.6.CUBE|ROLLUP:スーパーグループをVT 5に挿入し、VT 6を生成する.7.HAVING:VT 6にHAVINGフィルタを適用します.TRUEのグループはVT 7に挿入されます.
注意:havingは単独で使用することはできません.having句はグループ化後の記録のフィルタリングですので、havingがある場合はgroup by 8が必要です.SELECT:SELECTリストを処理し、VT 8を生成する.9.DISTINCT:重複する行をVT 8から除去し、VT 9を生成する.10.ORDER BY:VT 9の行をORDER BY句の列リストで並べ替え、テーブル(VC 10)を生成します.11.TOP:VC 10の先頭から指定された数または割合の行を選択し、テーブルVT 11を生成し、呼び出し元に返す.
注意:top nはページングを実現できます
select top 20*from従業員------第1ページ
 
select top 20*from従業員
where身分証明書番号not in(select top 20身分証明書番号from従業員)---第2ページ
 
2.データの準備SET NOCOUNT ON;
USE tempdb;
GO
　
IF OBJECT_ID('dbo.Orders') IS NOT NULL
　DROP TABLE dbo.Orders;
GO
IF OBJECT_ID('dbo.Customers') IS NOT NULL
　DROP TABLE dbo.Customers;
GO
　
CREATE TABLE dbo.Customers
(
　customerid CHAR(5)　　　 NOT NULL PRIMARY KEY,
　city　　　　　　 VARCHAR(10) NOT NULL
);
　
INSERT INTO dbo.Customers(customerid,city) VALUES('FISSA', 'Madrid');
INSERT INTO dbo.Customers(customerid,city) VALUES('FRNDO', 'Madrid');
INSERT INTO dbo.Customers(customerid,city) VALUES('KRLOS', 'Madrid');
INSERT INTO dbo.Customers(customerid,city) VALUES('MRPHS', 'Zion');
　
CREATE TABLE dbo.Orders
(
　orderid　　　 INT　　　　 NOT NULL PRIMARY KEY,
　customerid CHAR(5) NULL　　　　 REFERENCES Customers(customerid)
);
　
INSERT INTO dbo.Orders(orderid, customerid) VALUES(1,'FRNDO');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(2,'FRNDO');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(3,'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(4,'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(5,'KRLOS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(6,'MRPHS');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(7, NULL);
実行結果:
3.クエリ文USE tempdb;
GO
SELECT C.customerid, COUNT(O.orderid) AS numorders
FROM dbo.Customers AS C
　LEFT OUTER JOIN dbo.Orders AS O
　　　 ON C.customerid = O.customerid
WHERE C.city = 'Madrid'
GROUP BY C.customerid
HAVING COUNT(O.orderid) < 3
ORDER BY numorders;
実行結果:
4.論理クエリー処理手順の詳細
 
1.デカルト積を実行し、VT 1を形成する.左のテーブルがn行、右のテーブルがm行を含む場合、VT 1はnを含む×m行です.
実行結果VT 1:
 
2.ONフィルタを適用し、TRUEの行はVT 2に含まれる.ON C.customerid = O.customerid
3値論理:
TRUE、FALSE、UNKNOWNは、SQLにおける論理式の可能な値である.
UNKNOWN値は通常、NULL>42のようなNULL値を含む論理式に現れる.NULL = NULL; X + NULL > Y.
NOT TRUEはFALSEに等しい
NOT FALSEはTRUEに等しい
NOT UNKNOWN=UNKNOWN
すべてのクエリーフィルタ、例えばON、WHERE、HAVINGはUNKNOWNをFALSEとして扱います.
CHECK制約におけるUNKNOWN値はTRUEとして扱われる.テーブルにCHECK制約が含まれている場合、salary列の値が0より大きい必要がある場合は、salaryがNULLの行を挿入するときに受け入れることができます.
UNIQUE制約、ソート操作、パケット操作の2つのNULL値は等しいと考えられる.UNIQUE制約が定義されているカラムがある場合、カラム値NULLの2つのローをテーブルに挿入できません.GROUP BY句はすべてのNULL値を一組に分ける.ORDERB BY句はすべてのNULL値を並べている.
VT 1にONフィルタを追加した結果VT 2:
　　
3.外部行を追加し、LEFT、RIGHT、FULLのいずれかのOUTER JOINを指定することで、左、右、すべての表を保留表としてマークすることができます.フィルタが実行されました.予約表のこれらの行は外部行と呼ばれ、外部行の非予約表の属性はNULLを与えられ、最後にVT 3を生成する.
　　
4.WHEREフィルタを適用し、の行はVT 4の一部になります.データはまだグループ化されていないため、WHERE orderdate=MAX(orderdate)などの集約フィルタは使用できません.SELECTリストの別名も飲めません.SELECTリストはまだ処理されていないので、例えばSELECT YEAR(orderdate)AS orderyear WHERE orderyear>2000です.
OUTER JOIN句を含むクエリについて,ONフィルタであるかWHEREフィルタであるかの論理式を指定する方法:ONは外部行を追加する前に適用され,WHEREは外部行を追加した後に適用される.ONフィルタは、外部行を追加するステップを実行するため、保留表の一部の行に対して最終的なものではありません.WHEREフィルタは、これらの行の除去が最終的なものです.
このような論理的制限は、外部結合を使用する場合にのみONとWHERE句に存在し、内部結合を使用する場合には、上記のステップ3がないため、そこで論理式を指定しても構わない.WHERE C.city = 'Madrid'
仮想テーブルVT 4を生成する:
　　
5.グループ化.GROUP BY句中の列リストの各一意の値の組み合わせが一組となり、VT 5が生成される.
  　　Groups  
  　　Raw  
  　　C.customerid  
   
  　　FISSA  
  　　FRNDO  
  　　KRLOS  
VT 5は、GroupSectionとRaw Sectionの2つの部分から構成される.
クエリーでGROUP BY句が指定されている場合、以降のすべてのステップ(HAVING、SELECTなど)は、グループ化して得られるスカラー値の式のみを指定できます.すなわち、式の結果は、GROUP BYリストの列/式(例えば、C.customer)または集約関数(例えば、COUNT(O.orderid))である.この制限は、最終的な結果セットに最大1つのグループに1行しか含まれないためです.
この段階では2つのNULLが等しいと考えられる.すべてのNULL値がグループに割り当てられます.
GROUP BY ALLを指定すると、WHEREフィルタで除去されたグループがVT 5に追加され、元の部分が空のセットになります.次のステップでは、このグループにCOUNT集約関数を適用した結果が0になり、他の集約関数を適用した結果がNULLになります.GROUP BY ALLは使用しない方が良いです.
　　
6.CUBEまたはROLLUPオプションを使用して、スーパーグループを作成し、前のステップで戻ってきた仮想テーブルに追加し、VT 6を生成します.
　　
7.HAVINGフィルタを適用し、のグループはVT 7の一部になります.HAVINGは、パケット化されたデータに適用される唯一のフィルタです.HAVING COUNT(O.orderid) < 3
ここではCOUNT(*)ではなくCOUNT(O.orderid)を使用しているので、外部行はO.orderidがNULLなのでCOUNTには計上されません.FISSAのようなグループのCOUNT(O.orderid)は0.
赤い部分はHAVINGで選別されたグループです.
  　　Groups  
  　　Raw  
  　　C.customerid  
   
  　　FISSA  
  　　FRNDO  
  KRLOS  
　　
8.SELECTリストを処理し、ベース列ではない式に別名を適用し、結果表に名前を付ける.SELECTリストに作成されたエイリアスは、前の手順では使用できません.SELECTリストでは使用できません.ORDER BYでのみ使用できます.SELECT C.customerid, COUNT(O.orderid) AS numorders
VT 8の生成:
論理的には、すべての操作が同時に発生すると仮定しなければならない.
　　
9.DISTINCT句を適用し、クエリにDISTINCT句が指定されている場合、前のステップで返された仮想テーブルから重複行を削除し、仮想テーブルVT 9を生成する.GROUP BYを使い、DISTINCTを使うのは余計です.
　　
10.ORDER BY句を適用し、前のステップで返された行をORDER BY句の列リストに従って並べ替え、カーソルVC 10を返す.このステップだけでSELECTエイリアスを使用できます.DISTINCTが指定されている場合、ORDER BY句の式は、前のステップで返された仮想テーブルにのみアクセスでき、すでにSELECTされた列でのみソートできます.
ANSI SQL 1999ではORDER BYのサポートが強化され、SELECTフェーズの入力仮想テーブルと出力仮想テーブルにアクセスできます.つまりDISTINCTが指定されていない場合、ORDER BY句にSELECT句で使用できる式を指定し、最終結果セットに存在しない式でソートすることができます.ORDER BY numorders;
ORDER BY句でSELECTリストの結果列の番号を指定することもできます.ORDER BY 2, 1;
しかし、できるだけそうしないでください.SELECTリストを変えたのにORDER BYリストを修正するのを忘れた可能性があります.また、SELECTリストが長い場合、番号を調べるのは良い方法ではありません.
このステップは、テーブルを返すのではなくカーソルを返すため、ORDER BY句を使用したクエリはテーブル式として使用できません.表式には、ビュー、インライン表値関数、サブクエリ、派生表、および共通表式(CTE)が含まれます.
テーブル内のローの順序を仮定しないでください.順序付けされたローが必要でない限り、ORDER BY句は指定しません.ソートにはコストがかかります.SQL Serverでは、順序付きインデックススキャンを実行するか、ソート演算子を使用する必要があります.
ORDER BYはこのステップで2つのNULLが等しいと考えられ、すべてのNULLが並べられ、ANSIはNULLが既知値より高いか低いかを規定するのではなく、この問題を具体的な実装に残し、T-SQLではNULLが既知値より低い位置にランクされている.ORDER BY numorders
返されるカーソルVC 10:
　
11.TOPオプションを適用し、カーソルの先頭から指定された行数を選択し、テーブルVT 11を生成して呼び出し元に返す.SQLServer 2000では、TOPの入力は定数でなければならないが、2005では任意の独立した式であってもよい.
ORDER BY句またはWITH TIESオプションがない場合、返される行が物理的に最初にアクセスした行である場合、異なる結果が生じる可能性があります.
テーブル式でORDER BY句を含むクエリを使用できるのは、TOPオプションが指定されている場合のみです.SELECT *
FROM (SELECT TOP 100 PERCENT orderid, customerid
　　　　　　　 FROM dbo.Orders
　　　　　　　 ORDER BY orderid) AS D;
  ：http://tech.ddvip.com/2009-04/1239449159114603.html