これは、サブクエリに関する一連の記事の2番目です。この記事では、SELECTステートメントの列リストのサブクエリについて説明します。他の記事では、他の条項での使用について説明しています。
このレッスンのすべての例は、Microsoft SQL Server ManagementStudioとAdventureWorks2012データベースに基づいています。これらの無料ツールの使用を開始するには、ガイド「SQLServer入門ガイド」を使用します。
Selectステートメントでのサブクエリの使用
サブクエリが列リスト内に配置されると、次のようになります。単一の値を返します。この場合、サブクエリは単一の値の式と考えることができます。返される結果は、式「2 + 2」と同じです。もちろん、サブクエリはテキストを返すこともできますが、要点はわかります！
サブクエリを操作する場合、メインステートメントは外部クエリと呼ばれることもあります。サブクエリは括弧で囲まれているため、簡単に見つけることができます。 。
サブクエリを使用するときは注意してください。使用するのは楽しいかもしれませんが、クエリにさらに追加すると、クエリの速度が低下し始める可能性があります。
平均を計算するための単純なサブクエリ
SalesOrderDetailを表示する簡単なクエリから始めて、それを全体の平均SalesOrderDetailLineTotalと比較しましょう。使用するSELECTステートメントは次のとおりです。
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
このクエリは次のように結果を返します。
上記の赤で示されているサブクエリが最初に実行されます平均LineTotalを取得します。
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
この結果は列リストにプラグインされ、クエリが続行されます。指摘したいことがいくつかあります。 ：
- サブクエリは括弧で囲まれています。
- SELECTステートメントでサブクエリを使用すると、1つの値しか返すことができません。これは理にかなっているはずです。列を選択するだけで行に1つの値が返され、同じパターンに従う必要があります。
- 通常、サブクエリはクエリ全体に対して1回だけ実行され、その結果が再利用されます。 。これは、返される行ごとにクエリ結果が変化しないためです。
- 読みやすさを向上させるために、列名にエイリアスを使用することが重要です。
式の単純なサブクエリ
ご想像のとおり、サブクエリの結果は他の式で使用できます。前の例に基づいて、サブクエリを使用して、LineTotalが平均からどれだけ変化するかを判断しましょう。
分散は、単にLineTotalから平均Line合計を引いたものです。次のサブクエリでは、青色に色付けしています。分散の式は次のとおりです。
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
括弧で囲まれたSELECTステートメントはサブクエリです。前の例と同様に、このクエリは1回実行され、数値を返します。この数値は、各LineTotal値から差し引かれます。
最終的な形式のクエリは次のとおりです。
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
結果は次のとおりです。
通常作成するselectステートメントでサブクエリを操作する場合最初にサブクエリをテストします。 SELECTステートメントは非常にすぐに複雑になる可能性があります。それらを少しずつ積み上げるのが最善です。さまざまな部分を個別に構築してテストすることで、デバッグに非常に役立ちます。
相関クエリ
外部クエリの値をサブクエリの句に組み込む方法はいくつかあります。これらのタイプのクエリは、サブクエリの結果が何らかの形で外部クエリの値に関連付けられているため、相関サブクエリと呼ばれます。相関クエリは同期クエリと呼ばれることもあります。
相関の意味がわからない場合は、Googleの次の定義を確認してください。
相関：「相互関係または接続があります。
相関サブクエリの一般的な使用法は、内部クエリのWHERE句の外部クエリの列の1つで使用されます。これは、多くの場合、必要な常識です。内部クエリをデータのサブセットに制限します。
相関サブクエリの例
各SalesOrderDetail LineTotalと、全体の売上の平均LineTotalを報告することにより、相関サブクエリの例を提供します。注文。
計算する平均は販売注文ごとに異なるため、このリクエストは以前の例とは大幅に異なります。
ここで、相関サブクエリが機能します。外部クエリからの値をサブクエリのフィルタ基準に組み込みます。
平均ライン合計の計算方法を見てください。これを行うために、サブクエリを使用したSELECTステートメントを示す図をまとめました。
さらに詳しく説明する図。 SELECTステートメントは、外部クエリとサブクエリの2つの部分で構成されます。外部クエリは、すべてのSalesOrderDetail行を取得するために使用されます。サブクエリは、特定のSalesOrderIDの販売注文詳細行を検索して要約するために使用されます。
手順を言語化する場合これらを次のように要約します。
- SalesOrderIDを取得します。
- SalesOrderIDが一致するすべてのSalesOrderDetailアイテムから平均LineTotalを返します。
- 外部クエリの次のSalesOrderIDに進み、手順1と2を繰り返します。
AdventureWork2012データベースで実行できるクエリは次のとおりです。
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
クエリの結果は次のとおりです。
いくつかあります指摘する項目。
- クエリ結果を読みやすくするために列エイリアスを使用したことがわかります。
- また、テーブルエイリアスSODを使用しました。外部クエリ。これにより、サブクエリで外部クエリの値を使用できるようになります。それ以外の場合、クエリは相関しません！
- テーブルエイリアスを使用すると、各テーブルの列が明確になります。
相関サブクエリの分解
SQLを使用してこれを分解してみましょう。
まず、SalesOrderDetailID20の例を取得するとします。対応するSalesOrderIDは43661です。
このアイテムの平均LineTotalを取得するのは簡単です
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
これにより、値2181.765240が返されます。
これで、可能な平均が得られました。クエリにプラグインします
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
サブクエリを使用すると
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
最終的なクエリは：
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
異なるテーブルを持つ相関サブクエリ
相関サブクエリ、またはさらに言えば、任意のサブクエリは、異なるテーブルを使用できます外側のクエリ。これは、SalesOrderHeaderなどの「親」テーブルを操作していて、SalesOrderDetailなどの子行の概要を結果に含めたい場合に便利です。
OrderDate、TotalDue、および販売注文詳細行の数。これを行うには、次の図を使用して方位を取得します。
これを行うには、SELECTステートメントに相関サブクエリを含めてSalesOrderDetail行のCOUNTを返します。外部クエリのSalesOrderIDでフィルタリングすることにより、正しいSalesOrderDetailアイテムをカウントしていることを確認します。
最後のSELECTステートメントは次のとおりです。
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
結果は次のとおりです。
この例で注意すべき点は次のとおりです。
- サブクエリは外部クエリとは異なるテーブルからデータを選択しています。
- テーブルとSQLと結果を読みやすくするための列エイリアス。
- 必ずダブルチェックしてください。 kあなたのwhere句！サブクエリのWHERE句にテーブル名またはエイリアスを含めるのを忘れると、クエリは相関しません。
相関するサブクエリと内部結合
重要です。サブクエリまたは結合のいずれかを使用して同じ結果を取得できることを理解するため。どちらも同じ結果を返しますが、それぞれの方法には長所と短所があります！
SalesHeaderアイテムのラインアイテムをカウントする最後の例を検討してください。
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
これと同じクエリは、INNERJOINとGROUPBYを使用して実行できます
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
どちらが速いですか？
サブクエリは遅いので、多くの人がサブクエリを避けるように言うでしょう。相関サブクエリは、外部クエリで返される行ごとに1回「実行」する必要があるのに対し、内部結合はデータを1回通過するだけでよいと彼らは主張します。
私自身？私はチェックアウトと言いますクエリプラン。上記の両方の例について自分のアドバイスに従いましたが、プランは同じであることがわかりました。
データが増えるとプランが変わるとは限りませんが、私のポイントです。ほとんどのSQLDBMSオプティマイザーは、クエリを実行するための最良の方法を見つけるのに非常に優れています。サブクエリやINNER JOINなどの構文を取得し、それらを使用してクエリを作成します。実際の実行プラン。
どちらが読みやすいですか？
使い慣れているものによっては、INNERJOINの例が相関クエリよりも読みやすい場合があります。個人的にはこの例では、相関サブクエリがより直接的に見えるので気に入っています。何がカウントされているかを確認するのが簡単です。
私の考えでは、INNERJOINはそれほど直接的ではありません。まず、すべての販売詳細行が手元に返され、要約されていることを確認する必要があります。ステートメント全体を読むまで、これは実際にはわかりません。
どちらが良いですか？
あなたの考えを教えてください。相関サブクエリとINNERJOINのどちらの例を使用するかを聞きたいのですが。