MySQL でのサブクエリの基本的な使用法

1. サブクエリの定義

意味：

サブクエリを使用すると、1 つのクエリを別のクエリ内にネストできます。

サブクエリは内部クエリとも呼ばれ、内部クエリと比較して外部クエリと呼ばれます。

サブクエリには、distinct、group by、order by、limit、join、union など、通常の select に含めることができる任意の句を含めることができますが、対応する外部クエリは、select、insert、update、delete、set、do のいずれかのステートメントである必要があります。

サブクエリの位置: select 内、from の後、where 内。group by および order by では実質的な意味はありません。

2. サブクエリの分類

サブクエリは次のカテゴリに分類されます。
1. スカラーサブクエリ: 単一の値を返す、最も単純な形式のスカラーサブクエリ。
2. 列サブクエリ: 返される結果セットは N 行と 1 列です。
3. 行サブクエリ: 返される結果セットは、N 列の 1 行です。
4. テーブルサブクエリ: 返される結果セットは N 行と N 列です。

使用可能な演算子: = > < >= <= <> ANY IN SOME ALL EXISTS

サブクエリは、スカラー (値のみ)、行、列、またはテーブルを返します。これらのサブクエリは、スカラーサブクエリ、行サブクエリ、列サブクエリ、およびテーブルサブクエリと呼ばれます。

サブクエリがスカラー値 (1 つの値のみ) を返す場合、外部クエリは比較に =、>、<、>=、<=、<> の記号を使用できます。サブクエリがスカラー値を返さず、外部クエリが比較演算子を使用してサブクエリの結果を比較する場合、例外がスローされます。

1. スカラーサブクエリ:

これは、サブクエリが数値や文字列などの単一の値スカラーを返すことを意味し、サブクエリ内で最も単純な戻り形式でもあります。演算子 = > < >= <= <> を使用して、サブクエリのスカラー結果を比較できます。通常、サブクエリは比較の右側に配置されます。

例：

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

2. MySQLサブクエリ:

これは、サブクエリによって返される結果セットが N 行と 1 列であることを意味します。結果は通常、テーブルのフィールドに対するクエリから返されます。
=> < => <= <> などの演算子を使用して、サブクエリのスカラー結果を比較できます。通常、サブクエリは比較式の右側に配置され、IN、ANY、SOME、ALL などの演算子を使用できます。=> < => <= <> などの演算子を直接使用してスカラー結果を比較することはできません。
例：

 SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)
テーブル1からs1を選択します。s1 > ANY (テーブル2からs2を選択します)
テーブル1からs1を選択 WHERE s1 > ALL (テーブル2からs2を選択)

NOT IN は <> ALL の別名であり、これらは同じです。

特別な事情

table2 が空のテーブルの場合、ALL の後の結果は TRUE になります。
サブクエリが (0,NULL,1) のような結果を返し、s1 が返された結果より大きいが空白行がある場合、ALL の後の結果は UNKNOWN になります。

注: table2 が空の場合、次のステートメントは NULL を返します。

テーブル1からs1を選択します。s1 > (テーブル2からs2を選択します)
テーブル1からs1を選択します。s1 > ALL (テーブル2からMAX(s1)を選択します)

3. MySQL 行サブクエリ:

これは、サブクエリによって返される結果セットが 1 行と N 列であることを意味します。サブクエリの結果は通常、テーブル内のデータ行をクエリすることによって返される結果セットです。

例：

SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
注: (1,2)は行(1,2)と同等です。
SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)

4. MySQLテーブルサブクエリ:

これは、サブクエリによって返される結果セットが N 行と N 列を持つテーブルデータであることを意味します。

例：

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)

3. 単語検索の例

1. ANYサブクエリ

any キーワードは、「サブクエリによって返される列の任意の値について、比較結果が TRUE の場合は TRUE を返す」という意味です。

たとえば、「10 >any(11, 20, 2, 30)」の場合、10>2 なので、この判定は TRUE を返します。10 をセット内のいずれかと比較して TRUE が得られれば、TRUE を返します。

テーブル1.customer_id、city、count(order_id)を選択します。
テーブル1からテーブル2を結合
table1.customer_id=table2.customer_id の場合
ここで、table1.customer_id<>'tx' かつ table1.customer_id<>'9you'
顧客IDでグループ化
count(order_id) を持つ >
どれでも （
count(order_id) を選択
表2より
customer_id='tx' または customer_id='9you' の場合
顧客IDでグループ化);

any の意味は比較的理解しやすいです。文字通り、いずれか 1 つを意味します。条件のいずれかが満たされている限り、TRUE が返されます。

2. サブクエリにINを使用する

サブクエリに in を使用することは、日常生活で SQL を記述するときによく遭遇するものです。 in は指定された値がこのセット内に存在するかどうかを意味し、存在する場合は TRUE を返し、存在しない場合は FALSE を返します。

in は “=any” のエイリアスです。 “=any” が使用されている場所であればどこでも “in” を使って置き換えることができます。

中にある場合、中にない中があるはずです。中にないは <>any と同じ意味ではなく、中にない、および <>all は同じ意味です。

3. サブクエリにSOMEを使用する

Some は any の別名であり、あまり一般的には使用されません。

4. サブクエリにALLを使用する

すべては比較演算子と一緒に使用する必要があります。 all は「サブクエリによって返される列のすべての値について、比較が TRUE と評価された場合は TRUE を返す」ことを意味します。

たとえば、「10 >all(2, 4, 5, 1)」の場合、10 はセット内のすべての値より大きいため、この判定は TRUE を返します。また、「10 >all(20, 3, 2, 1, 4)」の場合、10 は 20 より小さいため、この判定は FALSE を返します。

<>all の同義語は not in で、これはセット内のすべての値と等しくないことを意味します。これは <>any と混同されやすいので、注意してください。

5. スカラーサブクエリ

サブクエリによって返される値の数に応じて、サブクエリはスカラーサブクエリと複数値サブクエリに分けられます。サブクエリに比較演算子を使用する場合は、スカラーサブクエリである必要があります。比較演算子を複数値サブクエリに使用すると、例外がスローされます。

6. 複数値サブクエリ

スカラーサブクエリに対応するのは、セットを形成する列、行、またはテーブルを返す複数値サブクエリです。通常、外部クエリとサブクエリの結果を判断するには、any、in、all、some などの単語を使用します。スカラーサブクエリで any、in、all、some などの単語を使用すると、空の結果が返されます。

7. 独立したサブクエリ

独立サブクエリは、外部クエリに依存せずに実行されるサブクエリです。外部クエリに依存するとはどういう意味ですか?まず、次の 2 つの SQL ステートメントを見てみましょう。

SQL ステートメント 1: 杭州のすべての顧客の注文番号を取得します。

注文IDを選択
表2より
顧客IDin
          （顧客IDを選択
          表1より
          ここで、city='杭州');
   SQL ステートメント 2: 都市が杭州で注文があるユーザーを取得します。
 
*を選択
表1より
ここで、city='hangzhou'であり、
                (選択*
                表2より
                ここで、table1.customer_id=table2.customer_id);

上記の 2 つの SQL ステートメントは、示されている例はあまり適切ではありませんが、ここでの問題を説明するには十分です。

SQL ステートメント 1 では、サブクエリを個別にコピーし、個別に実行できます。つまり、サブクエリは外部クエリとは関係ありません。

SQL ステートメント 2 の場合、サブクエリを個別にコピーすると、個別に実行できなくなります。SQL ステートメント 2 のサブクエリは外部クエリの特定のフィールドに依存しているため、サブクエリは外部クエリに依存し、相関関係が作成されます。

サブクエリの場合、効率性は多くの場合考慮されます。選択ステートメントを実行するときに、explain キーワードを追加して、クエリの種類、クエリ中に使用されるインデックス、およびその他の情報を表示できます。たとえば、次のようにします。

説明選択 order_id
  表2より
  顧客IDin
            （顧客IDを選択
            表1より
            ここで、city='杭州');

独立したサブクエリを使用すると、サブクエリ部分のコレクションへのトラバーサルの最大数が n で、外部クエリのトラバーサルの最大数が m の場合、これを O(m+n) として記録できます。相関サブクエリを使用すると、トラバーサルの回数は O(m+m*n) に達する可能性があります。ご覧のとおり、効率は指数関数的に低下します。したがって、サブクエリを使用する場合は、サブクエリの関連性を考慮する必要があります。

8. 相関サブクエリ

相関サブクエリは、外部クエリ内の列を参照するサブクエリです。つまり、サブクエリは外部クエリ内の行ごとに 1 回計算されます。ただし、MySQL 内部では動的な最適化が実行され、状況に応じて変化する可能性があります。相関サブクエリを使用すると、パフォーマンスの問題が発生する可能性が最も高くなります。 SQL ステートメントの最適化に関しては、これは非常に大きなトピックです。実践的な経験を積み重ねることでのみ、最適化の方法をより深く理解することができます。

9.EXISTS述語

EXISTS は非常に強力な述語であり、これによりデータベースは、指定されたクエリが特定の行を生成するかどうかを効率的に確認できるようになります。この述語は、サブクエリが行を返すかどうかに応じて、TRUE または FALSE を返します。他の述語や論理式とは異なり、入力サブクエリが行を返すかどうかに関係なく、EXISTS は UNKNOWN を返しません。EXISTS の場合、UNKNOWN は FALSE です。上記のステートメントを使用すると、都市が杭州で注文があるユーザーを取得できます。

*を選択
表1より
ここで、city='hangzhou'であり、
                (選択*
                表2より
                ここで、table1.customer_id=table2.customer_id);

IN と EXISTS の主な違いは、3 値ロジックの判断にあります。 EXISTS は常に TRUE または FALSE を返しますが、IN の場合は TRUE と FALSE の値に加えて、NULL 値に対して UNKNOWN を返すこともあります。ただし、フィルターでは UNKNOWN は FALSE と同じように扱われるため、SQL オプティマイザーは IN を使用する場合も EXISTS を使用する場合も同じ実行プランを選択します。

IN と EXISTS はほぼ同じであると述べましたが、NOT IN と NOT EXISTS についても説明する必要があります。入力リストに NULL 値が含まれている場合、NOT EXISTS と NOT IN の違いは非常に顕著になります。入力リストに NULL 値が含まれている場合、IN は常に TRUE と UNKNOWN を返すため、NOT IN は NOT TRUE と NOT UNKNOWN、つまり FALSE と UNKNOWN を取得します。

10. 派生テーブル

前述の通り、サブクエリの戻り値にテーブルが返されることもあります。サブクエリが返した仮想テーブルを再度 FROM 句の入力として使用すると、サブクエリの仮想テーブルは派生テーブルになります。文法構造は次のとおりです。

FROM (サブクエリ式) AS derived_table_alias

派生テーブルは完全に仮想的なテーブルであるため、物理的に実現することはできず、実現できません。

4. サブクエリの最適化

多くのクエリではサブクエリの使用が必要です。サブクエリを使用すると、論理的には一度に複数のステップを完了する必要がある多くの SQL 操作を完了でき、トランザクションまたはテーブルロックを回避することもできます。サブクエリを使用するとクエリステートメントを非常に柔軟にすることができますが、サブクエリの実行効率は高くありません。

サブクエリを使用する場合、MySQL は内部クエリステートメントのクエリ結果用の一時テーブルを作成する必要があります。次に、外部クエリステートメントが一時テーブル内のレコードをクエリします。クエリが完了したら、MySQL はこれらの一時テーブルを削除する必要があります。したがって、サブクエリの速度はある程度影響を受けます。クエリされるデータの量が多い場合、この影響もそれに応じて大きくなります。

MySQL では、サブクエリの代わりに結合クエリを使用できます。結合クエリでは一時テーブルを作成する必要がなく、サブクエリよりも高速です。

サブクエリの代わりにJOINを使用する

のように：

例1:

t1から*を選択
t1.a1 が (t2 から a2 を選択) に含まれない場所
最適化後:
t1から*を選択
t2 を t1.a1=t2.a2 に左結合
t2.a2がNULLの場合

例2:

SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
最適化後:
記事から*を選択
インナージョインブログ
(article.title=blog.title かつ article.content=blog.content かつ article.uid=blog.uid) で

最適化できないサブクエリ:
1. MySQL はサブクエリのマージと集計関数サブクエリの最適化をサポートしていませんが、MariaDB は集計関数サブクエリに対してマテリアライズド最適化を実行します。
2. MySQL は from サブ句クエリの最適化をサポートしていませんが、MariaDB は from サブ句クエリでサブクエリプルアップ最適化を実行します。
3. MySQL と MariaDB はサブクエリ拡張のサポートを限定的に提供しています。たとえば、プルアップサブクエリを最適化するために使用できるのは主キーに対する操作のみです。
4. MySQL は EXISTS サブクエリの最適化をサポートしていません。MariaDB は EXISTS 関連サブクエリに対してセミ結合最適化を実行しますが、EXISTS 非関連サブクエリをさらに最適化しません。
5. MySQL と MariaDB は NOT EXISTS サブクエリの最適化をサポートしていません。
6. MySQL と MariaDB は、IN サブクエリとセミ結合セマンティクスを満たすクエリに対してセミ結合最適化を実行し、コスト評価に基づいて最適化します。この 2 つでは、セミ結合のコスト評価選択方法が異なります。
7. MySQL は not in サブクエリの最適化をサポートしていません。MariaDB は、非相関 not in サブクエリに対してマテリアライズド最適化を使用し、相関 not in サブクエリは最適化しません。
8. MySQL と MariaDB は、非相関サブクエリ > all に対して max 関数を使用し、非相関サブクエリ < all に対して min 関数を使用し、= all および非相関サブクエリに対しては exists 最適化を使用します。
9. >some および >any の非相関サブクエリには min 関数を使用し、<some および <any の非相関サブクエリには max 関数を使用し、=any および =some サブクエリの最適化には semi-join を使用し、>some および >any の相関サブクエリと <some および <any の相関サブクエリには exists 最適化のみを使用します。

これで、MySQL のサブクエリの基本的な使用法に関するこの記事は終了です。MySQL のサブクエリに関するより詳しい情報については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: