FROM (Transact-SQL)
DELETE、SELECT、および UPDATE ステートメントの中で使用される、テーブル、ビュー、派生テーブル、および結合テーブルを指定します。SELECT ステートメントでは、選択リストに定数、変数、および数式のみが含まれて列名が含まれていない場合を除き、FROM 句を指定する必要があります。
.gif "トピック リンク アイコン")
構文
[ FROM { <table_source> } [ ,...n ] ]
<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
[ WITH ( < table_hint > [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ] ]
| OPENXML <openxml_clause>
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| <joined_table>
| <pivoted_table>
| <unpivoted_table>
| @variable [ [ AS ] table_alias ]
| @variable.function_call ( expression [ ,...n ] ) [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
}
<tablesample_clause> ::=
TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] )
[ REPEATABLE ( repeat_seed ) ]
<joined_table> ::=
{
<table_source> <join_type> <table_source> ON <search_condition>
| <table_source> CROSS JOIN <table_source>
| left_table_source { CROSS | OUTER } APPLY right_table_source
| [ ( ] <joined_table> [ ) ]
}
<join_type> ::=
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
JOIN
<pivoted_table> ::=
table_source PIVOT <pivot_clause> [ AS ] table_alias
<pivot_clause> ::=
( aggregate_function ( value_column [ [ , ]...n ])
FOR pivot_column
IN ( <column_list> )
)
<unpivoted_table> ::=
table_source UNPIVOT <unpivot_clause> [ AS ] table_alias
<unpivot_clause> ::=
( value_column FOR pivot_column IN ( <column_list> ) )
<column_list> ::=
column_name [ ,...n ]
引数
<table_source>
Transact-SQL ステートメントの中で使用する、テーブル、ビュー、テーブル変数、または派生テーブル ソースを指定します。別名を付けて指定することができます。1 つのステートメント内で 256 個までのテーブル ソースを使用できますが、この上限値は、使用可能なメモリとクエリ内の他の式の複雑さに応じて変化します。個別のクエリは、256 個までのテーブル ソースをサポートできません。.gif "注意")
注意クエリのパフォーマンスは、クエリで参照される多くのテーブルによって低下する可能性があります。コンパイルと最適化にかかる時間も、追加の要素によって影響を受けます。これらの要素には、各 <table_source> 上のインデックスとインデックス付きビューの存在、および SELECT ステートメント内の <select_list> のサイズが含まれます。
FROM キーワードの後のテーブル ソースの順序は、返される結果セットには影響しません。FROM 句内に重複した名前を指定すると、SQL Server はエラーを返します。
table_or_view_name
テーブルまたはビューの名前です。SQL Server の同じインスタンス上の別のデータベース内にテーブルまたはビューが存在する場合は、database.schema.object_name という形式の完全修飾名を使用します。
テーブルまたはビューが SQL Server インスタンスの外部に存在する場合は、linked_server.catalog.schema.object という形式の 4 部構成の名前を使用します。詳細については、「sp_addlinkedserver (Transact-SQL)」を参照してください。名前の中のサーバー部分として OPENDATASOURCE 関数を使用して構成される 4 部構成の名前は、リモート テーブル ソースを指定するためにも使用できます。OPENDATASOURCE を指定した場合は、database_name および schema_name がすべてのデータ ソースに適用されるとは限らず、リモート オブジェクトにアクセスする OLE DB プロバイダの機能により制限されます。詳細については、「分散クエリ」を参照してください。
[AS] table_alias
table_source の別名です。別名は、便宜上、または自己結合やサブクエリでテーブルまたはビューを区別するために使用できます。別名にはテーブル名を短縮したものが指定されることが多く、結合されたテーブルの特定の列を参照するために使用されます。結合された複数のテーブルに同じ列名が存在する場合、SQL Server では、テーブル名、ビュー名、または別名で列名を修飾する必要があります。別名が定義されている場合、テーブル名は使用できません。派生テーブル、行セット、またはテーブル値関数、または演算子句 (PIVOT や UNPIVOT など) が使用されている場合、句の末尾に必要な table_alias は、グループ化列を含む、すべての返された列の関連テーブル名になります。
WITH (<table_hint> )
クエリ オプティマイザが、このテーブルを使用して、このステートメントに対し最適化またはロックを使用することを指定します。詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。rowset_function
OPENROWSET など、テーブル参照の代わりに使用できるオブジェクトを返す行セット関数のいずれかを指定します。行セット関数の一覧の詳細については、「行セット関数 (Transact-SQL)」を参照してください。OPENROWSET 関数および OPENQUERY 関数を使用したリモート オブジェクトの指定は、そのオブジェクトにアクセスする OLE DB プロバイダの機能に依存します。詳細については、「分散クエリ」を参照してください。
bulk_column_alias
結果セット内の列名に対する別名です。このパラメータは省略可能です。列の別名は、BULK オプションが指定された OPENROWSET 関数を使用する SELECT ステートメント内でのみ使用できます。bulk_column_alias を使用する場合は、ファイル内の列と同じ順序ですべてのテーブル列に対して別名を指定します。注意この別名は、XML 形式のファイルの COLUMN 要素内に NAME 属性が存在する場合は、それを上書きします。
user_defined_function
テーブル値関数を指定します。OPENXML <openxml_clause>
XML ドキュメントに対して行セット ビューを提供します。詳細については、「OPENXML (Transact-SQL)」を参照してください。derived_table
データベースから行を取得するサブクエリです。derived_table は 1 つ上のレベルのクエリの入力として使用されます。derived_table では、Transact-SQL テーブル値コンストラクタ機能を使用して、複数の行を指定できます。たとえば、SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b); のようになります。詳細については、「テーブル値コンストラクタ (Transact-SQL)」を参照してください。
column_alias
派生テーブルの結果セット内の列名に対する別名です。このパラメータは省略可能です。選択リストの各列の別名を 1 つずつ含みます。列の別名リスト全体をかっこで囲みます。<tablesample_clause>
テーブルからのデータのサンプルが返されることを指定します。サンプルは、概数になる可能性があります。この句は、SELECT、UPDATE、または DELETE ステートメント内の主テーブルまたは結合テーブルで使用できます。TABLESAMPLE はビューを使用して指定できません。詳細については、「TABLESAMPLE 句の使用による結果セットの制限」を参照してください。注意SQL Server にアップグレードしたデータベースに対して TABLESAMPLE を使用する場合は、データベースの互換性レベルを 90 以上に設定する必要があります。データベースの互換性レベルを設定するには、「ALTER DATABASE 互換性レベル (Transact-SQL)」を参照してください。
SYSTEM
ISO 標準で指定された、実装に依存するサンプリング方法です。SQL Server では、これが唯一のサンプリング方法であり、既定で適用されます。SYSTEM は、サンプルとしてテーブルからページがランダムに選択され、それらのページのすべての行がサンプル サブセットとして返される、ページ ベースのサンプリング方法を適用します。詳細については、「TABLESAMPLE 句の使用による結果セットの制限」を参照してください。sample_number
行数または行数に対応する割合を表す真数または概数値の定数値式です。PERCENT を使用して指定されている場合、sample_number は、暗黙的に float 値に変換されます。それ以外の場合は bigint に変換されます。PERCENT は既定値です。PERCENT
sample_number で指定した割合の行がテーブルから取得されることを指定します。PERCENT が指定されている場合、SQL Server は指定された割合の概数を返します。PERCENT が指定されている場合、sample_number 式は、0 ～ 100 の値に評価される必要があります。ROWS
sample_number に指定した概数の行が取得されることを指定します。ROWS が指定されている場合、SQL Server は指定された行数の概数を返します。ROWS が指定されている場合、sample_number 式は、0 より大きい整数値に評価される必要があります。REPEATABLE
選択されたサンプルを再度返すことができることを示します。同じ repeat_seed 値を使用して指定されている場合、SQL Server はテーブル内の行に変更が行われない限り同じ行のサブセットを返します。異なる repeat_seed 値を使用して指定されている場合、SQL Server は、テーブル内の異なる行のサンプルをいくつか返す可能性があります。テーブルに対する挿入、更新、削除、インデックスの再構築またはデフラグ、およびデータベースの復元またはアタッチは、変更と見なされます。repeat_seed
乱数を生成するために SQL Server によって使用される整数の定数式です。repeat_seed のデータ型は bigint です。repeat_seed が指定されていない場合は、SQL Server によってランダムに値が割り当てられます。テーブルに変更が適用されていない場合は、特定の repeat_seed 値に対して、サンプル結果は常に同じになります。repeat_seed 式は、0 より大きい整数値に評価される必要があります。<joined_table>
2 つ以上のテーブルが組み合わされた結果セットです。複数の結合については、かっこを使って結合の順序を変更できます。<join_type>
結合操作の種類を指定します。INNER
一致するすべての行をペアで返すことを指定します。両方のテーブルで一致しない行は廃棄します。結合の種類が指定されていない場合は、これが既定値になります。FULL [ OUTER ]
結合条件に合わない左側または右側のテーブルの行も結果セットに含まれ、他方のテーブルに対応する出力列は NULL に設定されることを指定します。この処理は、INNER JOIN によって通常返される行も含めて、すべての行を返します。LEFT [ OUTER ]
内部結合によって返されるすべての行に加えて、結合条件に合わない左側のテーブルのすべての行も結果セットに含まれます。右側のテーブルからの出力列は NULL に設定されることを指定します。RIGHT [OUTER]
内部結合によって返されるすべての行に加えて、結合条件に合わない右側のテーブルのすべての行も結果セットに含まれます。左側のテーブルからの出力列は NULL に設定されることを指定します。<join_hint>
SQL Server クエリ オプティマイザが、クエリの FROM 句で指定される結合ごとに、1 つの結合ヒントまたは実行アルゴリズムを使用することを指定します。詳細については、「結合ヒント (Transact-SQL)」を参照してください。JOIN
指定されたテーブル ソースまたはビューの間で、指定された結合操作が行われることを指定します。ON <search_condition>
結合するときの条件を指定します。列と比較演算子はよく使用されますが、条件で任意の結合述語を指定できます。たとえば、次のようになります。SELECT p.ProductID, v.VendorID FROM Production.Product AS p JOIN Purchasing.ProductVendor AS v ON (p.ProductID = v.ProductID);列に結合条件を指定する場合、使用される列は、同じ列名、同じデータ型である必要はありません。ただし、データ型が異なる場合は、互換性のあるデータ型であるか、SQL Server が暗黙的に変換できるデータ型である必要があります。データ型が暗黙的に変換されない場合は、CONVERT 関数を使用して明示的に変換する必要があります。
ON 句に、結合されるテーブルのいずれかだけを指定している述語がある場合があります。このような述語は、クエリの WHERE 句にもある場合があります。このように述語を指定した場合、INNER 結合に対しては同じ結果になりますが、OUTER 結合が関係するときは結果が異なることがあります。ON 句内の述語は結合の前にテーブルに適用されるのに対し、WHERE 句は意味的に結合結果に適用されるためです。
検索条件および述語の詳細については、「検索条件 (Transact-SQL)」を参照してください。
CROSS JOIN
2 つのテーブルの結合を指定します。SQL-92 形式でない旧形式の結合で WHERE 句が指定されていない場合と同じ行が返されます。left_table_source{ CROSS | OUTER } APPLY right_table_source
APPLY 演算子の right_table_source を left_table_source の各行に対して評価することを指定します。この機能は、right_table_source に、引数として left_table_source から列の値を取得するテーブル値関数が含まれる場合に役立ちます。CROSS または OUTER は、APPLY を使用して指定する必要があります。CROSS を指定した場合は、left_table_source の指定行に対して right_table_source を評価し、空の結果セットが返されると、行は生成されません。
OUTER を指定した場合は、left_table_source の各行に対して right_table_source を評価し、空の結果セットが返されても、各行に対して 1 行が生成されます。
詳細については、「解説」および「APPLY の使用」を参照してください。
left_table_source
前の引数で定義されたテーブル ソースです。詳細については、「解説」を参照してください。right_table_source
前の引数で定義されたテーブル ソースです。詳細については、「解説」を参照してください。table_source PIVOT <pivot_clause>
table_source が pivot_column に基づいてピボット化されることを指定します。table_source は、テーブルまたはテーブル式です。出力は、pivot_column および value_column 以外の table_source のすべての列を含んでいるテーブルです。pivot_column および value_column 以外の table_source の列は、ピボット演算子のグループ化列と呼ばれます。PIVOT は、グループ化列に関する入力テーブルに対してグループ化の操作を実行し、各グループごとに 1 行のデータを返します。さらに、出力では、input_table の pivot_column に表示される column_list で指定された値ごとに 1 列のデータが含まれます。
詳細については、「解説」および「PIVOT と UNPIVOT の使用」を参照してください。
注意SQL Server にアップグレードしたデータベースに対して PIVOT を使用する場合は、データベースの互換性レベルを 90 以上に設定する必要があります。データベースの互換性レベルの設定の詳細については、「ALTER DATABASE 互換性レベル (Transact-SQL)」を参照してください。
aggregate_function
システムまたはユーザー定義の集計関数で、1 つ以上の入力を受け取ります。集計関数は、NULL 値に固定されます。NULL 値に固定された集計関数は、集計値を評価する際に、グループ内の NULL 値を考慮しません。COUNT(*) システム集計関数は使用できません。
value_column
PIVOT 演算子の値列です。UNPIVOT と共に使用される場合、value_column は、入力 table_source 内の既存の列の名前にすることはできません。FOR pivot_column
PIVOT 演算子のピボット列です。pivot_column のデータ型は、nvarchar() に暗黙的または明示的に変換できる型である必要があります。この列は image または rowversion にすることはできません。UNPIVOT が使用される場合、pivot_column は、table_source から絞り込まれる出力列の名前です。table_source 内に、この名前が付けられている既存の列がないことが条件となります。
IN ( column_list )
PIVOT 句で使用する場合は、出力テーブルの列名になる pivot_column の値の一覧を指定します。この一覧では、ピボットの対象となっている入力 table_source 内に既に存在している列名は指定できません。UNPIVOT 句で使用する場合は、単一の pivot_column に絞り込まれる table_source 内の列の一覧を指定します。
table_alias
出力テーブルの別名です。pivot_table_alias を指定する必要があります。UNPIVOT < unpivot_clause >
入力テーブルが column_list 内の複数の列から pivot_column と呼ばれる単一の列に絞り込まれることを指定します。詳細については、「解説」および「PIVOT と UNPIVOT の使用」を参照してください。
注意SQL Server にアップグレードしたデータベースに対して UNPIVOT を使用する場合は、データベースの互換性レベルを 90 以上に設定する必要があります。データベースの互換性レベルを設定するには、「ALTER DATABASE 互換性レベル (Transact-SQL)」を参照してください。
説明
FROM 句は、結合テーブルと派生テーブルに対して SQL-92-SQL 構文がサポートされています。SQL-92 構文には、INNER、LEFT OUTER、RIGHT OUTER、FULL OUTER、および CROSS 結合演算子が用意されています。
FROM 句内での UNION と JOIN は、ビュー内で、および派生テーブルやサブクエリ内でサポートされています。
自己結合は、そのテーブル自体に対して結合されるテーブルです。自己結合に基づいた挿入または更新の操作は、FROM 句の順序に従います。
旧バージョンの SQL Server では、WHERE 句内で *= 演算子と =* 演算子を使用する外部結合の定義をサポートしています。SQL Server Version 7.0 は ISO 標準をサポートしています。これによって FROM 句内の結合演算子が提供されます。データベースの互換性レベルが 90 以上に設定されている場合、外部結合演算子 (*= と =*) はサポートされません。
SQL Server は列の分布統計を提供するリンク サーバーからの分布統計および基数統計を検討するので、REMOTE 結合ヒントを使用して結合をリモートから評価する必要はありません。SQL Server クエリ プロセッサはリモートの統計量を検討し、リモート結合の方法が適切かどうかを決定します。REMOTE 結合ヒントは、列の分布統計を提供しないプロバイダに対しては便利です。詳細については、「OLE DB プロバイダを使用するための分布統計情報の要件」を参照してください。
結合の処理方法の詳細については、「結合の基礎」および「結合の使用」を参照してください。
APPLY の使用
APPLY 演算子の左右両方のオペランドはテーブル式です。これらのオペランドの主な相違点は、right_table_source は関数の引数として left_table_source から列を取得するテーブル値関数を使用できるということです。left_table_source には、テーブル値関数を含めることができますが、right_table_source からの列である引数を含めることはできません。
APPLY 演算子は、FROM 句のテーブル ソースを作成するために、次の方法で動作します。
left_table_source の各行に対して right_table_source を評価し、行セットを作成します。
right_table_source 内の値は、left_table_source に依存します。right_table_source は、テーブル値関数 TVF を使って、TVF(left_table_source.row) のように表すことができます。
UNION ALL 操作を実行し、right_table_source の評価の各行に対して作成された結果セットを left_table_source に結合します。
APPLY 演算子の結果として作成される列の一覧は、right_table_source からの列の一覧と結合された left_table_source からの列セットです。
PIVOT および UNPIVOT の使用
pivot_column および value_column は、PIVOT 演算子によって使用されるグループ化列です。PIVOT は、次のプロセスに従って出力結果セットを取得します。
グループ化列に対して input_table 上で GROUP BY を実行し、各グループに対して 1 行の出力行を作成します。
出力行内のグループ化列は、input_table 内の該当するグループに対応する列の値を取得します。
次の処理を実行して、各出力行の列の一覧内の列に対して値を生成します。
pivot_column に対して前の手順の GROUP BY で生成された行をさらにグループ化します。
column_list 内の各出力列に対して、次の条件を満たすサブグループを選択します。
pivot_column = CONVERT(<data type of pivot_column>, 'output_column')
aggregate_function は、このサブグループ上の value_column に対して評価され、その結果は対応する output_column の値として返されます。サブグループが空である場合は、SQL Server は、その output_column に対して NULL 値を生成します。集計関数が COUNT であり、サブグループが空である場合は、0 が返されます。
詳細については、「PIVOT と UNPIVOT の使用」を参照してください。
権限
DELETE、SELECT、または UPDATE ステートメントに対する権限が必要です。
例
A. 単純な FROM 句を使用する
次の例では、AdventureWorks サンプル データベース内の SalesTerritory テーブルから TerritoryID および Name 列を取得します。
USE AdventureWorks ;
GO
SELECT TerritoryID, Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID ;
以下に結果セットを示します。
TerritoryID Name
----------- ------------------------------
1 Northwest
2 Northeast
3 Central
4 Southwest
5 Southeast
6 Canada
7 France
8 Germany
9 Australia
10 United Kingdom
(10 row(s) affected)
B. TABLOCK および HOLDLOCK オプティマイザ ヒントを使用する
次の部分的なトランザクションでは、明示的な共有テーブル ロックを Employee に設定する方法と、インデックスを読み取る方法を示します。ロックはトランザクション全体をとおして保持されます。
USE AdventureWorks ;
GO
BEGIN TRAN
SELECT COUNT(*)
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;
C. SQL-92 CROSS JOIN 構文を使用する
次の例では、2 つのテーブル Employee と Department がクロスした結果を返します。EmployeeID 行とすべての Department の名前の行を組み合わせた場合に、作成される可能性があるすべての組み合わせの一覧が返されます。
USE AdventureWorks ;
GO
SELECT e.EmployeeID, d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.EmployeeID, d.Name ;
D. SQL-92 FULL OUTER JOIN 構文を使用する
次の例では、SalesOrderDetail テーブル内の製品名、および対応する販売注文を返します。また、Product テーブル内に製品が一覧表示されていない販売注文、および Product テーブル内に一覧表示されている製品以外の販売注文に対する製品も返します。
USE AdventureWorks ;
GO
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
FULL OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
WHERE p.ProductID IS NULL OR sod.ProductID IS NULL
ORDER BY p.Name ;
E. SQL-92 LEFT OUTER JOIN 構文を使用する
次の例では、ProductID の 2 つのテーブルを結合し、左側のテーブルから一致しない行を取り出します。Product テーブルは、各テーブル内の ProductID 列について SalesOrderDetail テーブルと照合されます。注文されたかどうかにかかわらず、すべての製品が結果セットに表示されます。
USE AdventureWorks ;
GO
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
F. SQL-92 INNER JOIN 構文を使用する
次の例では、すべての製品名と販売注文 ID を返します。
USE AdventureWorks ;
GO
-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
G. SQL-92 RIGHT OUTER JOIN 構文を使用する
次の例では、TerritoryID の 2 つのテーブルを結合し、右側のテーブルから一致しない行を取り出します。SalesTerritory テーブルは、各テーブル内の TerritoryID 列について SalesPerson テーブルと照合されます。販売区域に割り当てられているかどうかに関係なく、すべての販売員は結果セットに表示されます。
USE AdventureWorks ;
GO
SELECT st.Name AS Territory, sp.SalesPersonID
FROM Sales.SalesTerritory AS st
RIGHT OUTER JOIN Sales.SalesPerson AS sp
ON st.TerritoryID = sp.TerritoryID ;
H. HASH および MERGE 結合ヒントを使用する
次の例では、Product、ProductVendor、および Vendor テーブルの 3 つのテーブル結合を実行して、製品とその仕入先の一覧を作成します。クエリ オプティマイザは、MERGE 結合を使用して Product と ProductVendor (p と pv) を結合します。次に、Product と ProductVendor の MERGE 結合の結果が (p と pv)、Vendor テーブルに対して HASH 結合され、(p と pv) と v が作成されます。
.gif "重要な注意事項") 重要 |
|---|
結合ヒントを指定すると、INNER キーワードを省略することはできません。INNER JOIN を明示的に指定して、実行する必要があります。 |
USE AdventureWorks ;
GO
SELECT p.Name AS ProductName, v.Name AS VendorName
FROM Production.Product AS p
INNER MERGE JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
ON pv.VendorID = v.VendorID
ORDER BY p.Name, v.Name ;
I. 派生テーブルを使用する
次の例では、派生テーブル、つまり FROM 句の後に SELECT ステートメントを使用することで、すべての従業員の姓と名、およびそれぞれの住所のある都市を返します。
USE AdventureWorks ;
GO
SELECT RTRIM(c.FirstName) + ' ' + LTRIM(c.LastName) AS Name,
d.City
FROM Person.Contact AS c
INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID
INNER JOIN
(SELECT ea.AddressID, ea.EmployeeID, a.City
FROM Person.Address AS a
INNER JOIN HumanResources.EmployeeAddress AS ea
ON a.AddressID = ea.AddressID) AS d
ON e.EmployeeID = d.EmployeeID
ORDER BY c.LastName, c.FirstName;
J. TABLESAMPLE を使用してテーブル内のサンプル行からデータを読み取る
次の例では、FROM 句内で TABLESAMPLE を使用して、AdventureWorks データベース内の Customer テーブル内にあるすべての行の約 10% を返します。
USE AdventureWorks ;
GO
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;
K. APPLY を使用する
次の例では、次のスキーマを使用した次のテーブルがデータベース内に存在することを前提としています。
Departments: DeptID, DivisionID, DeptName, DeptMgrID
EmpMgr: MgrID, EmpID
Employees: EmpID, EmpLastName, EmpFirstName, EmpSalary
指定された MgrID の直接または間接の監督下にあるすべての従業員の一覧 (EmpID、EmpLastName、EmpSalary) を返すテーブル値関数 GetReports(MgrID) もあります。
この例では、APPLY を使用して、すべての部門と、各部門内のすべての従業員を返します。特定の部門に従業員が存在しない場合は、その部門には行が返されません。
SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;
EmpID、EmpLastName、および EmpSalary 列に対して NULL 値を作成する、従業員が存在しない部門に対してもクエリによって行を作成する場合は、代わりに OUTER APPLY を使用します。
SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d OUTER APPLY dbo.GetReports(d.DeptMgrID) ;
L. PIVOT および UNPIVOT を使用する
次の例では、従業員 ID 164、198、223、231、および 233 による購買発注の数を、仕入先 ID ごとに分類して返します。
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) AS p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;
以下に結果セットの一部を示します。
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
----------------------------------------------------------------
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
テーブルのピボット解除を行うには、前の例で作成された結果セットが pvt として保存されていることが前提となります。クエリは次のようになります。
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE dbo.pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
-- Insert 5 rows into the table.
INSERT INTO dbo.pvt VALUES (1,4,3,5,4,4),(2,4,1,5,5,5),(3,4,3,5,4,4),
(4,4,2,5,5,4),(5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM dbo.pvt) AS p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
以下に結果セットの一部を示します。
VendorID Employee Orders
------------------------------
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5