MariaDBで顧客分析をレベルアップ！EXCEPT句で注文履歴のない顧客を抽出

MariaDBのEXCEPT句は、セット演算と呼ばれる機能の一つで、2つのSELECTクエリの結果セットを比較し、左側のクエリ結果から右側のクエリ結果に存在する行を除いた行を抽出するものです。つまり、差集合を求める操作を行います。

構文

SELECT *
FROM table1
EXCEPT
SELECT *
FROM table2;

上記例では、table1 から table2 に存在するすべての行を除いた行をすべて選択します。

EXCEPT ALLとEXCEPT DISTINCTの違い

• EXCEPT DISTINCT: 重複行を考慮し、左側クエリ結果から右側クエリ結果の重複レコードを除いた行を削除します。
• EXCEPT ALL: 重複行を考慮せず、左側クエリ結果から右側クエリ結果のすべての行を削除します。

• EXCEPT句は、WHERE句やORDER BY句などの他のSQL句と組み合わせて使用できます。
• 比較するSELECTクエリは、同じ数の列を持ち、対応する列のデータ型が一致している必要があります。
• EXCEPT句は、2つ以上のSELECTクエリを比較する必要があります。

例

例1：顧客テーブルと注文テーブルを使用して、注文履歴のない顧客を抽出する

SELECT *
FROM customers
EXCEPT
SELECT customer_id
FROM orders;

例2：社員テーブルと部署テーブルを使用して、部署に所属していない社員を抽出する

SELECT *
FROM employees
EXCEPT DISTINCT
SELECT employee_id
FROM departments_employees;

メリット

• 複数のクエリ結果を比較して、差異を分析するのに役立ちます。
• 複雑なクエリをよりシンプルに記述できます。

• 他の方法で実現できる場合もあります。
• 習得にやや時間がかかる場合があります。

• EXCEPT句の代替手段として、NOT IN句やLEFT JOIN句を使用することもできます。
• EXCEPT句は、バージョン10.3.0以降のMariaDBでのみサポートされています。

---

-- テーブル定義
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- データ挿入
INSERT INTO customers (customer_id, name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie'),
  (4, 'David');

INSERT INTO orders (order_id, customer_id) VALUES
  (1, 1),
  (2, 2),
  (3, 1),
  (4, 2);

-- サンプルコード
SELECT *
FROM customers
EXCEPT
SELECT customer_id
FROM orders;

結果

customer_id	name
3	Charlie
4	David

例2：社員テーブルと部署テーブルを使用して、部署に所属していない社員を抽出する

-- テーブル定義
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE departments_employees (
  department_id INT,
  employee_id INT,
  PRIMARY KEY (department_id, employee_id),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

-- データ挿入
INSERT INTO employees (employee_id, name) VALUES
  (1, 'John'),
  (2, 'Jane'),
  (3, 'Peter'),
  (4, 'Mary');

INSERT INTO departments_employees (department_id, employee_id) VALUES
  (1, 1),
  (1, 2),
  (2, 3),
  (2, 4);

-- サンプルコード
SELECT *
FROM employees
EXCEPT DISTINCT
SELECT employee_id
FROM departments_employees;

結果

employee_id	name
3	Peter

• 結果として、最初のテーブルにのみ存在する行が抽出されます。
• 各例では、EXCEPT句を使用して、最初のテーブルから2番目のテーブルに存在する行を除外しています。
• 上記の例では、2つのテーブル（顧客テーブルと注文テーブル、社員テーブルと部署テーブル）を使用しています。

• 実際の使用例では、より複雑なクエリで使用されることがよくあります。
• これらの例は、EXCEPT句の基本的な使い方を示しています。

---

MariaDBのEXCEPT句は、2つのSELECTクエリの結果セットを比較し、差集合を求める便利な機能です。しかし、状況によっては、EXCEPT句よりも適切な代替方法が存在する場合があります。

代替方法

• 副問い合わせ
• LEFT JOIN句
• NOT IN句

NOT IN句

NOT IN句は、特定の値が別の列またはサブクエリに存在しないかどうかを確認するために使用されます。EXCEPT句の代替手段として使用できる場合がありますが、完全な一致のみを比較できるという点に注意が必要です。

例：注文履歴のない顧客を抽出する

SELECT *
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders
);

LEFT JOIN句

LEFT JOIN句は、左側テーブルのすべての行を結果セットに含め、右側テーブルと一致する行があれば右側テーブルの対応する列を追加します。一致する行がない場合は、NULL値を追加します。EXCEPT句の代替手段として使用できる場合がありますが、一致しない行にNULL値が含まれるという点に注意が必要です。

例：注文履歴のない顧客を抽出する

SELECT c.*, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

副問い合わせ

副問い合わせは、別のSELECTクエリを別のクエリの中で使用する方法です。EXCEPT句の代替手段として使用できますが、複雑なクエリになる可能性があるという点に注意が必要です。

例：注文履歴のない顧客を抽出する

SELECT *
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders
);

• 複雑な条件で差集合を求める必要がある場合: 副問い合わせが適切です。
• 一致しない行にNULL値を含める必要がある場合: LEFT JOIN句が適切です。
• 完全な一致のみを比較する必要がある場合: NOT IN句が適切です。
• シンプルな差集合を求める場合: EXCEPT句が最適です。

• 最適な方法は、具体的な状況によって異なります。
• 上記以外にも、状況によっては他の方法でEXCEPT句の機能を実現できる場合があります。