【データ分析のヒント】SQLとMariaDBで2つのリストの共通要素を抽出する活用方法
SQL と MariaDB で 2 つのリストの共通要素を抽出する SELECT クエリ
このチュートリアルでは、SQL と MariaDB を使用して、2 つのリストの共通要素を抽出する方法について説明します。 具体的には、EXISTS 句と INTERSECT 演算子を使用して、2 つのリストの共通要素を含むレコードのみを抽出する SELECT クエリを作成する方法を紹介します。
前提知識
このチュートリアルを理解するには、以下の知識が必要です。
- SQL の基本的な構文
- MariaDB データベースの使用
- 2 つのテーブルを結合する方法
手順
- 2 つのリストを作成する
まず、2 つのリストを作成します。ここでは、以下の例を使用します。
List 1: [1, 2, 3, 4, 5]
List 2: [3, 4, 5, 6, 7]
- テーブルを作成する
CREATE TABLE list1 (
id INT PRIMARY KEY
);
CREATE TABLE list2 (
id INT PRIMARY KEY
);
- データを挿入する
作成したテーブルにデータを挿入します。ここでは、以下の例を使用します。
INSERT INTO list1 (id) VALUES (1), (2), (3), (4), (5);
INSERT INTO list2 (id) VALUES (3), (4), (5), (6), (7);
- SELECT クエリを作成する
EXISTS 句と INTERSECT 演算子を使用して、2 つのリストの共通要素を含むレコードのみを抽出する SELECT クエリを作成します。
SELECT l1.id
FROM list1 l1
WHERE EXISTS (
SELECT 1
FROM list2 l2
WHERE l1.id = l2.id
)
INTERSECT
SELECT l2.id
FROM list2 l2
WHERE EXISTS (
SELECT 1
FROM list1 l1
WHERE l1.id = l2.id
);
このクエリは、以下の結果を返します。
id
---
3
4
5
解説
このクエリは、以下の 2 つの部分から構成されています。
- EXISTS 句
EXISTS 句は、サブクエリが 1 以上の行を返すかどうかを確認するために使用されます。 この場合、サブクエリは、list1 テーブルと list2 テーブルの id 列を比較し、一致するレコードがあるかどうかを確認します。
- INTERSECT 演算子
INTERSECT 演算子は、2 つのクエリの結果を比較し、共通する行のみを返します。 この場合、2 つのクエリは、EXISTS 句を使用して抽出されたレコードを比較します。
コード
CREATE TABLE list1 (
id INT PRIMARY KEY
);
CREATE TABLE list2 (
id INT PRIMARY KEY
);
INSERT INTO list1 (id) VALUES (1), (2), (3), (4), (5);
INSERT INTO list2 (id) VALUES (3), (4), (5), (6), (7);
SELECT l1.id
FROM list1 l1
WHERE EXISTS (
SELECT 1
FROM list2 l2
WHERE l1.id = l2.id
)
INTERSECT
SELECT l2.id
FROM list2 l2
WHERE EXISTS (
SELECT 1
FROM list1 l1
WHERE l1.id = l2.id
);
説明
list1 と list2 という名前の 2 つのテーブルを作成します。 これらのテーブルには、id という名前の列があり、これはプライマリ キーとして定義されます。
各テーブルに、5 つの値を含む id 列にデータを挿入します。
結果
id
---
3
4
5
補足
- このコードは、MariaDB 10.5 でテストされています。
- コードは、必要に応じて変更できます。
- コードを実行する前に、データベースに接続していることを確認してください。
SQL と MariaDB で 2 つのリストの共通要素を抽出するその他の方法
このチュートリアルでは、SQL と MariaDB で 2 つのリストの共通要素を抽出するその他の方法について説明します。 具体的には、以下の方法を紹介します。
IN句を使用した方法
方法
IN 句を使用した方法
SELECT id
FROM list1
WHERE id IN (
SELECT id
FROM list2
);
このクエリは、list1 テーブルの id 列の値が list2 テーブルの id 列の値のいずれかに一致するレコードのみを抽出します。 IN 句は、サブクエリを使用して、比較する値のリストを指定するために使用されます。
利点
- シンプルでわかりやすい
欠点
- 複数のリストを比較するには、
IN句をネストする必要がある
JOIN 操作を使用した方法
SELECT l1.id
FROM list1 l1
JOIN list2 l2 ON l1.id = l2.id;
このクエリは、list1 テーブルと list2 テーブルを id 列で結合し、一致するレコードのみを抽出します。 JOIN 操作は、複数のテーブルからデータを結合するために使用されます。
- 複数のリストを簡単に比較できる
- 関連するデータも一緒に抽出できる
IN句よりも複雑
2 つのリストの共通要素を抽出する方法はいくつかあります。 どの方法を使用するかは、データの構造と要件によって異なります。
上記以外にも、以下の方法を使用して 2 つのリストの共通要素を抽出することができます。
- サブクエリを使用した方法
- ウィンドウ関数を使用した方法
sql mariadb
