SQL JOIN と Set 操作の詳細

Feb 03 2023

今日私が JOIN について書いているのは、なぜそうではないのでしょうか? 私も先週習っていたので、少しわかりにくいと思いました。したがって、この記事は単に私がそれらが正しく理解できたことを確認するためのものであり、将来の参考のためにも目的としています。

Unsplash の Rubaitul Azad による写真

SQL では、結合操作により、テーブル間の関連する列に基づいて 2 つ以上のテーブルの行が結合されます。先週まで、SQL コースをいくつか受講したにもかかわらず、セミ結合とアンチ結合があることを知りませんでした。

私の知る限り存在するすべての結合の内訳を以下に示します。結合は緑色で表されます

INNER JOIN:両方のテーブルで一致する値を持つ行のみを返します。これは最も一般的に使用される結合です。

内部結合

SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

左結合

SELECT *
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

右結合

SELECT *
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

完全結合

SELECT *
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

クロスジョイン

SELECT *
FROM customers
CROSS JOIN orders;

合計行数 = 合計顧客数 * 合計注文数

このタイプの結合では結果セットが非常に大きくなる可能性があるため、使用には注意が必要です。ほとんどの場合、INNER、RIGHT、または LEFT JOIN で十分です。

セミ結合とアンチ結合

セミ結合とアンチ結合では、右側のテーブルを使用して、左側のテーブルにどのレコードを保持するかを決定します。つまり、2 番目のテーブルの値に応じた WHERE 句と同じように使用します。

セミ結合とアンチ結合には、INNER JOIN や LEFT JOIN と同じ組み込み SQL 構文がありません。

アンチ結合

アンチ結合は、2 番目のテーブルで条件が満たされない最初のテーブルのレコードを選択します。アンチ結合は、右側のテーブルの条件と一致しない左側のテーブルの列を選択します。

アンチ結合は、結合クエリに不正な数のレコードが表示される原因となっているレコードを特定するのに特に役立ちます。これは、予想よりも少ないまたは多いレコードを取得するという点で、他の結合に関する問題を診断するのに非常に役立ちます。

例として、説明のために顧客と注文という 2 つの構成されたテーブルを使用します。

SELECT *
FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM customers WHERE country = 'Kenya');

セミジョイン

準結合では、2 番目のテーブルで条件が満たされる最初のテーブルのレコードが選択されます。準結合は、右側のテーブルのキーフィールドごとにレコードを左側のテーブルと照合します。次に、その条件に一致する左側のテーブルの行のみを選択します。

2 番目のテーブルに設定された条件に基づいて最初のテーブルをフィルタリングする場合は、準結合を使用してタスクを完了する必要があります。代わりに、2 番目のテーブルで満たされない条件に基づいて最初のテーブルをフィルタリングしたい場合は、アンチ結合を使用する必要があります。

SELECT *
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'Kenya');

これらは、SQL におけるセミ結合とアンチ結合の基本的な例です。もちろん、特定の使用法や構文さえも、使用されているコンテキストやデータベースに応じて異なる場合があります。

SQL でのセット操作

SQL では、セット操作を使用して、2 つ以上の SELECT ステートメントの結果を 1 つの結果セットに結合します。そのうちのいくつかについては以下で説明します。

UNION は、2 つ以上の SELECT ステートメントの結果を結合する集合演算です。UNION の結果セットには、重複行が削除された、各 SELECT ステートメントのすべての行が含まれます。

各 SELECT ステートメントによって返される列は、同じデータ型で同じ順序である必要があります。これは単一のフィールドとして返されるため、重要です。つまり、文字フィールドの上に数字を重ねることはできません。共用体の基本的な構文は次のとおりです。

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

SQL では、INTERSECT と EXCEPT も UNION に似た集合演算です。

INTERSECT は、2 つ以上の SELECT ステートメントの両方の結果セットに存在する行のみを返します。各 SELECT ステートメントによって返される列は、同じデータ型で同じ順序である必要があります。INTERSECT の基本的な構文は次のとおりです。

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;

EXCEPT を使用すると、一方のテーブルにあるレコードのみを含めることができ、もう一方のテーブルには含めることができません。EXCEPT は PostgreSQL データベースで使用でき、MINUS は MySQL と Oracle で使用できます。

UNION複数のテーブルにわたるフィールドのすべての出現を判断するために使用することもできます。INTERSECT は、JOIN のような個別のキーフィールドではなく、共通の RECORDS を検索します。これは重要な違いです。

UNION と UNION ALL は、JOIN が行うこととまったく同じことを行いません。2 つのテーブルのフィールドを相互にバインドするだけです。INTERSECT は UNION および UNION ALL と同様に機能しますが、選択されたテーブルとフィールドの両方に共通するレコードのみが含まれます。

UNION ALLは 2 つのテーブルからすべてのレコードを抽出しますが、INTERSECT両方のテーブルに共通するレコードのみを返します。国名と同じ名前の都市が存在する国はどこですか? INTERSECTを使用して答えることができます。

SQL JOIN と Set 操作の詳細

セミ結合とアンチ結合

アンチ結合

セミジョイン

SQL でのセット操作

まとめ