LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@saoyagi2

関連するレコードが無いレコードを抽出するクエリ

Last updated at Posted at 2024-02-19

複数の関連づいたテーブルで構成されるデータベースで、関連付けられたレコードが無いレコードだけを抽出したいことがあります。

例えば以下の注文テーブルと入金テーブルがあったとして、未入金の注文だけを抽出したいとします。3件の注文履歴がありますが、未払いの鈴木さんの注文を抽出したいというケースです。

mysql> DESC orders;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int          | NO   | PRI | NULL    | auto_increment |
| customer_name | varchar(255) | YES  |     | NULL    |                |
| product_name  | varchar(255) | YES  |     | NULL    |                |
| ordered_at    | date         | YES  |     | NULL    |                |
| amount        | int          | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+----+---------------+--------------+------------+--------+
| id | customer_name | product_name | ordered_at | amount |
+----+---------------+--------------+------------+--------+
|  1 | 山田          | りんご       | 2024-02-01 |    100 |
|  2 | 鈴木          | みかん       | 2024-02-02 |    200 |
|  3 | 佐藤          | バナナ       | 2024-02-03 |    300 |
+----+---------------+--------------+------------+--------+
3 rows in set (0.00 sec)

mysql> DESC payments;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| order_id  | int          | YES  | MUL | NULL    |                |
| pay_means | varchar(255) | YES  |     | NULL    |                |
| paid_at   | date         | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM payments;
+----+----------+--------------------+------------+
| id | order_id | pay_means          | paid_at    |
+----+----------+--------------------+------------+
|  1 |        1 | 現金               | 2024-02-10 |
|  2 |        3 | 銀行振り込み       | 2024-02-15 |
+----+----------+--------------------+------------+
2 rows in set (0.00 sec)

サブクエリを使う方法

サブクエリで支払い済みの order_id 一覧を作成し、NOT IN で除外します。

mysql> SELECT * FROM orders WHERE id NOT IN (SELECT order_id FROM payments);
+----+---------------+--------------+------------+--------+
| id | customer_name | product_name | ordered_at | amount |
+----+---------------+--------------+------------+--------+
|  2 | 鈴木          | みかん       | 2024-02-02 |    200 |
+----+---------------+--------------+------------+--------+
1 row in set (0.00 sec)

鈴木さんの注文(orders.id = 2)のみが抽出できました。

外部結合を使う方法

外部結合では対応するレコードが無い場合は NULL になります。orders と payments を外部結合すると、全体として以下の結果が返ってきます。

mysql> SELECT * FROM orders LEFT JOIN payments ON orders.id=payments.order_id;
+----+---------------+--------------+------------+--------+------+----------+--------------------+------------+
| id | customer_name | product_name | ordered_at | amount | id   | order_id | pay_means          | paid_at    |
+----+---------------+--------------+------------+--------+------+----------+--------------------+------------+
|  1 | 山田          | りんご       | 2024-02-01 |    100 |    1 |        1 | 現金               | 2024-02-10 |
|  2 | 鈴木          | みかん       | 2024-02-02 |    200 | NULL |     NULL | NULL               | NULL       |
|  3 | 佐藤          | バナナ       | 2024-02-03 |    300 |    2 |        3 | 銀行振り込み       | 2024-02-15 |
+----+---------------+--------------+------------+--------+------+----------+--------------------+------------+
3 rows in set (0.00 sec)

payments のカラムが NULL になっているものだけを抽出すればよいということですね。

mysql> SELECT orders.* FROM orders LEFT JOIN payments ON orders.id=payments.order_id WHERE payments.id IS NULL;
+----+---------------+--------------+------------+--------+
| id | customer_name | product_name | ordered_at | amount |
+----+---------------+--------------+------------+--------+
|  2 | 鈴木          | みかん       | 2024-02-02 |    200 |
+----+---------------+--------------+------------+--------+
1 row in set (0.00 sec)

こちらも鈴木さんの注文(orders.id = 2)のみが抽出できました。

集合演算を用いる方法

SQL では集合演算が使えます。MySQL では 8.0.31 から使えるようになったそうです。

mysql> SELECT ID FROM orders EXCEPT SELECT order_id FROM payments;
+----+
| ID |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

集合演算ではカラム構成が一致していないといけないのですが、orders と payments は一致していません。そこで id/order_id 列のみを抽出するようにしています。

処理速度比較

三つの方法のいずれでも求める結果を得ることが出来ました。では処理速度に違いはあるのでしょうか。

事前の予想としては以下のように考えました。

  • サブクエリを使用する場合、入金の全 id 一覧を生成するため、入金レコードが大量にある場合は遅くなりそう。
  • 外部結合の場合、入金レコードは where 句で除外されるので、入金レコードが多くなっても遅くならなさそう。
  • 未払いレコードが大量にある場合は、三つの方法いずれでも遅くなりそう。

未払いレコードが大量にあるケース

注文レコードが100万件、入金レコードが1万件のテストデータを用意しました。

mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT COUNT(*) FROM payments;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

レコードそのものを出力するとコンソールの速度が影響してしまうので、COUNT() の結果のみとしています。

-- サブクエリ
mysql> SELECT COUNT(*) FROM orders WHERE id NOT IN (SELECT order_id FROM payments);
+----------+
| COUNT(*) |
+----------+
|   990000 |
+----------+
1 row in set (0.42 sec)

-- 外部結合
mysql> SELECT COUNT(*) FROM orders LEFT JOIN payments ON orders.id=payments.order_id WHERE payments.id IS NULL;
+----------+
| COUNT(*) |
+----------+
|   990000 |
+----------+
1 row in set (1.78 sec)

-- 集合演算
mysql> SELECT COUNT(*) FROM (SELECT ID FROM orders EXCEPT SELECT order_id FROM payments) AS T;
+----------+
| COUNT(*) |
+----------+
|   990000 |
+----------+
1 row in set (4.19 sec)

支払済みレコードが大量にあるケース

注文レコードが100万件、入金レコードが99万件のテストデータを用意しました。

mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.03 sec)

mysql> SELECT COUNT(*) FROM payments;
+----------+
| COUNT(*) |
+----------+
|   990000 |
+----------+
1 row in set (0.03 sec)

抽出結果です。

-- サブクエリ
mysql> SELECT COUNT(*) FROM orders WHERE id NOT IN (SELECT order_id FROM payments);
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (3.24 sec)

-- 外部結合
mysql> SELECT COUNT(*) FROM orders LEFT JOIN payments ON orders.id=payments.order_id WHERE payments.id IS NULL;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (3.79 sec)

-- 集合演算
mysql> SELECT COUNT(*) FROM (SELECT ID FROM orders EXCEPT SELECT order_id FROM payments) AS T;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (10.12 sec)

意外や意外、サブクエリが一番速かったですね。集合演算がほかの2倍以上の時間がかかっているのも少々驚きです。

もっとも、常にこの速度順というわけではありませんし、そもそもテストも簡易なものですから、あくまでもこの場合はこうだったととらえておくのがよいと思います。

NOT EXISTS を使う方法

(2024/2/20追記)

@jnchito さんからコメントで NOT EXISTS を使う方法を指摘いただきましたので追記します。

mysql> SELECT o.*
    -> FROM orders AS o
    -> WHERE NOT EXISTS (
    ->   SELECT *
    ->   FROM payments p
    ->   WHERE p.order_id = o.id
    -> );
+----+---------------+--------------+------------+--------+
| id | customer_name | product_name | ordered_at | amount |
+----+---------------+--------------+------------+--------+
|  2 | 鈴木          | みかん       | 2024-02-02 |    200 |
+----+---------------+--------------+------------+--------+
1 row in set (0.00 sec)

こちらでも鈴木さんのレコードのみ抽出できました。

処理速度も計ってみました。

-- 未払いレコードが大量にあるケース
mysql> SELECT COUNT(*) FROM orders AS o WHERE NOT EXISTS (SELECT * FROM payments p WHERE p.order_id
= o.id);
+----------+
| COUNT(*) |
+----------+
|   990000 |
+----------+
1 row in set (0.34 sec)

-- 支払済みレコードが大量にあるケース
mysql> SELECT COUNT(*) FROM orders AS o WHERE NOT EXISTS (SELECT * FROM payments p WHERE p.order_id = o.id);
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (2.95 sec)

NOT EXISTS がこれまでで一番速かったです。

とはいえ、あくまでも簡易的な計測結果でしかありませんので、常にこれが一番とは限りませんが。そもそも一度しか計測してないので、ばらつきで簡単に逆転しそうですしね。

0
1
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?