mysql サブクエリと結合テーブルの詳細

1. サブクエリとは何ですか?

商品 TNT2 を注文したすべての顧客を一覧表示します。

cust_idを選択
注文から
order_num IN (SELECT order_num

注文商品から
prod_id = 'TNT2'の場合
）

サブクエリを含むSQL SELECTステートメントのフォーマットは、特に複雑な場合には読み取りやデバッグが困難です。上記のようにサブクエリを複数の行に分割し、適切にインデントすると、サブクエリの使用が大幅に簡素化されます。

ネストできるサブクエリの数に制限はありませんが、実際の使用では、パフォーマンスの制限により、あまり多くのサブクエリをネストすることはできません。

注記：

列は一致する必要があります。WHERE 句でサブクエリを使用する場合 (ここに示すように)、 SELECTステートメントの列数がWHERE句の列数と同じであることWHERE確認する必要があります。一般的に、
サブクエリは単一の列を返して一致させますが、必要に応じて複数の列を使用することもできます。

サブクエリはwhereに配置するだけでなく、 selectに配置することもできます。

customersテーブル内の各顧客の注文の合計数を表示する必要があるとします。

cust_name、cust_state、(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) を orders として選択します。
顧客から
cust_name で並べ替え

mysql操作プロセスでは、まずcustomersを実行してcust_name 、 cust_state 、 cust_idを検索し、次に 5 つのサブクエリを実行して結果を検索します。

サブクエリを段階的に追加してクエリを構築する サブクエリを使用するクエリのテストとデバッグは、特にこれらのステートメントの複雑さが増すにつれて、難しくなる可能性があります。サブクエリを使用してクエリを構築 (およびテスト) する最も信頼性の高い方法は、それを段階的に実行することです。これは、MySQL がサブクエリを処理する方法とほぼ同じです。まず、最も内側のクエリを構築してテストします。次に、ハードコードされたデータを使用して外部クエリを構築およびテストし、機能することを確認した後にのみサブクエリを埋め込みます。この時点で、再度テストします。追加するクエリごとにこれらの手順を繰り返します。これを行うと、クエリの構築にほんの少しだけ時間がかかりますが、後でクエリが機能しなかった理由を調べるのにかかる時間が大幅に節約され、そもそもクエリが機能する可能性が大幅に高まります。

接続は次のとおりです。

ベンダー名、製品名、製品価格を選択します
ベンダー、製品から
vendors.vend_id = products.vend_id の場合
ベンダー名、製品名で並べ替え

注記：

完全修飾列名 列への参照に曖昧さが生じる可能性がある場合は、完全修飾列名 (ドットで区切られたテーブル名と列名) を使用する必要があります。テーブル名で修飾せずにあいまいな列名を参照すると、MySQL はエラーを返します。

where ステートメントを使用して結合する役割は次のとおりです。

WHERE 句を使用して結合関係を確立するのは少し奇妙に思えるかもしれませんが、実際にはそれには非常に良い理由があります。 SELECT ステートメントで複数のテーブルを結合すると、対応する関係がその場で構築されることに留意してください。データベース テーブル定義には、MySQL にテーブルを結合する方法を指示するものは何もありません。これを自分でやらなければなりません。 2 つのテーブルを結合する場合、実際には最初のテーブルの各行を 2 番目のテーブルの各行とペアにすることになります。 WHERE 句はフィルター条件として機能し、指定された条件 (ここでは結合条件) に一致する行のみが含まれます。 WHERE 句がない場合、論理的に一緒になるかどうかに関係なく、最初のテーブルのすべての行が 2 番目のテーブルのすべての行とペアになります。

注記：

cartesian product積 結合条件のないテーブル関係によって返される結果はデカルト積です。取得される行数は、最初のテーブルの行数に 2 番目のテーブルの行数を掛けた数になります。これまで使用されてきた結合は、 equijoin呼ばれ、2 つのテーブル間の等価性テストに基づいています。このタイプの結合は内部結合とも呼ばれます。実際には、この種類の結合では少し異なる構文を使用して、結合のタイプを明示的に指定できます。

次の SELECT ステートメントは、前の例とまったく同じデータを返します。

ベンダー名、製品名、製品価格を選択します
vendors から、 vendors.vend_id の products を INNER JOIN して、 products.vend_id に変換します。
ベンダー名、製品名で並べ替え

どの構文を使用すればよいですか? ANSI SQL仕様では、 INNER JOIN構文が推奨されます。さらに、 WHERE句を使用して結合を定義する方が確かに簡単ですが、明示的な結合構文を使用すると、パフォーマンスに影響を与える可能性のある結合条件を忘れることがなくなります。

パフォーマンスの考慮事項MySQL実行時に指定された各テーブルを結合して結合を処理します。この処理はリソースを大量に消費する可能性があるため、不要なテーブルを結合しないように注意する必要があります。結合されるテーブルが増えるほど、パフォーマンスが低下します。

たくさん実験してください。ご覧のとおり、通常、特定の SQL 操作を実行する方法は複数あります。物事を行うのに絶対的に正しい方法や間違った方法というものはほとんどありません。パフォーマンスは、操作の種類、テーブル内のデータの量、インデックスやキーの存在、その他の条件によって影響を受ける可能性があります。したがって、さまざまな選択メカニズムを試して、特定の状況に最適なものを見つける必要があります。複数のテーブル結合を使用することもできますが、テーブル名が複数の場所で使用されるためテーブル名が非常に長くなり、テーブル別名を使用する必要があるという問題があります。

のように：

以下にいくつかの特殊な接続を紹介します。

2. 自己結合

ある品目 ( IDがDTNTR ) に問題があることがわかり、この品目を製造しているサプライヤーが製造した他の品目にも同じ問題があるかどうかを知りたいとします。このクエリでは、まずID DTNTRのアイテムを生産するサプライヤーを見つけ、次にこのサプライヤーが生産する他のアイテムを見つける必要があります。

この問題を解決する一つの方法は次のとおりです。

次のようにサブクエリを使用できます。

prod_id、prod_name を選択
製品から
vend_id = (製品から vend_id を選択、prod_id ='DTNTR' を選択)

自己結合も使用できます。

t1.prod_id、t2.prod_name を選択
製品t1、製品t2から
ここで、t1.vend_id = t2.vend_id かつ t1.prod_id='DTNTR'

サブクエリの代わりに自己結合を使用する 自己結合は、同じテーブルからデータを取得するときに使用されるサブクエリを置き換える外部ステートメントとしてよく使用されます。最終結果は同じですが、結合はサブクエリよりもはるかに高速に処理できる場合があります。どちらのアプローチが優れているかを判断するには、両方のアプローチを試してみる必要があります。

3. 自然な結合

テーブルを結合するときは、複数のテーブルに表示される列 (結合される列) が少なくとも 1 つ必要です。標準結合 (前の章で説明した内部結合) では、同じ列が複数回出現する場合でも、すべてのデータが返されます。自然結合により、複数の出現が排除され、各列が 1 回だけ返されるようになります。

この作業を完了するにはどうすればいいですか?答えは、システムが仕事をするのではなく、あなた自身がそれをやるということです。自然結合は、一意の列のみを選択する結合です。これは通常、テーブルでワイルドカード ( SELECT * ) を使用し、他のすべてのテーブルの列の明示的なサブセットを使用することによって行われます。

4. 外部接続

多くの結合は、あるテーブルの行を別のテーブルの行に関連付けます。しかし、場合によっては、関連付けられている行がない行を含める必要があります。たとえば、結合を使用して次の操作を実行できます。

たとえば、まだ注文していない顧客も含め、各顧客が注文した注文数をカウントします。

顧客ID、注文番号を選択します
顧客からのLEFT OUTER JOIN orders on customers.cust_id = orders.cust_id

このSELECTステートメントでは、キーワードOUTER JOINを使用して結合のタイプを指定します ( WHERE句で指定するのではなく)。ただし、2 つのテーブルの行を関連付ける内部結合とは異なり、外部結合には関連する行がない行も含まれます。 OUTER JOIN構文を使用する場合は、 RIGHTまたはLEFTキーワードを使用して、すべての行を含むテーブルを指定する必要があります ( RIGHT OUTER JOINの右側のテーブルを参照し、 LEFT はOUTER JOINの左側のテーブルを参照します)。

集計関数を使用した結合の使用:

すべての顧客と各顧客の注文数を取得するには:

customers.cust_id を選択し、COUNT(order_num) を num として取得します。
顧客からのLEFT OUTER JOIN orders on customers.cust_id = orders.cust_id
cust_id によるグループ化

注記：
1. 使用する接続の種類に注意してください。一般的には内部結合を使用しますが、外部結合も効果的です。
2. 正しい結合条件が使用されていることを確認します。そうでない場合、誤ったデータが返されます。
3. 結合条件は常に指定する必要があります。そうでない場合は、直積が取得されます。
4. 結合には複数のテーブルを含めることができ、結合ごとに異なる結合タイプを使用することもできます。これは合法であり、一般的には有用ですが、一緒にテストする前に各接続を個別にテストする必要があります。これによりトラブルシューティングが容易になります。

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