SQLのサブクエリで混乱したので整理しながら理解してみた

サブクエリを含むSQL文を読んでいて、どのように処理が行われているのか混乱してしまったので、今後同様に混乱しないよう図も使いながら整理してみました。

今回使うクエリ

authors, books, sales テーブルがあり、ある著者がどのような本を出版しているか、そしてその本が何冊売れているかを管理しています。

mysql> SELECT * FROM authors;
+----+------+
| id | name |
+----+------+
|  1 | John |
|  2 | Paul |
|  3 | Alex |
+----+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM books;
+----+-----------+-------+
| id | author_id | title |
+----+-----------+-------+
|  1 |         1 | book1 |
|  2 |         1 | book2 |
|  3 |         2 | book3 |
|  4 |         2 | book4 |
|  5 |         3 | book5 |
|  6 |         3 | book6 |
+----+-----------+-------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM sales;
+----+---------+-----------------+
| id | book_id | number_of_books |
+----+---------+-----------------+
|  1 |       1 |              10 |
|  2 |       2 |               5 |
|  3 |       3 |               0 |
|  4 |       4 |               5 |
|  5 |       5 |              15 |
|  6 |       6 |              30 |
+----+---------+-----------------+
6 rows in set (0.00 sec)

そして今回は、全ての本が10冊以上売れている著者の情報を取得するクエリを使います。

SELECT authors.id, authors.name, books.title, sales.number_of_books
FROM
  authors
  LEFT JOIN books ON authors.id = books.author_id
  LEFT JOIN sales ON books.id = sales.book_id
WHERE
  NOT EXISTS (
    SELECT 1
    FROM
      books b
      INNER JOIN sales s ON b.id = s.book_id
    WHERE
      b.author_id = authors.id AND
      s.number_of_books <= 10
  );

このクエリをパッと見て何をしているか分かる方もいると思いますが、私はサブクエリの存在のために頭がこんがらがってしまいました。

select文の処理の仕組みを理解する

上のクエリを読む上で、select文がどのように処理されているのかを理解していることはとても重要でした。

select句、from句、where句だけの場合、次のように処理されます。

1. from句で検索に使うためのテーブルを準備する
2. そのテーブルの1行1行について、以下の処理を繰り返す
   1. where句の条件を満たすか判定
      • 満たす場合、select句で指定された値を出力
      • 満たさない場合何もしない
   2. 次の行の処理へ移る

このように処理が進んでいることを意識しながらクエリを見ていきます。

今回のクエリを理解する

SELECT authors.id, authors.name, books.title, sales.number_of_books
FROM
  authors
  LEFT JOIN books ON authors.id = books.author_id
  LEFT JOIN sales ON books.id = sales.book_id
WHERE
  NOT EXISTS (
    SELECT 1
    FROM
      books b
      INNER JOIN sales s ON b.id = s.book_id
    WHERE
      b.author_id = authors.id AND
      s.number_of_books <= 10
  );

これも

1. from句で検索に使うためのテーブルを準備する
2. 1行ずつwhere句の条件を満たすか判定してselect句で出力

を行っているだけです。

またwhere句にサブクエリが含まれていますが、このサブクエリも同様の処理が行われていると考えれば良いです。

1. from句で作られるテーブルをイメージする

FROM
  authors
  LEFT JOIN books ON authors.id = books.author_id
  LEFT JOIN sales ON books.id = sales.book_id

大体こんな感じになります。

2. from句で作ったテーブルについて1行ずつwhere句で判定していく

WHERE
  NOT EXISTS (
    SELECT 1
    FROM
      books b
      INNER JOIN sales s ON b.id = s.book_id
    WHERE
      b.author_id = authors.id AND
      s.number_of_books <= 10
  );

まず1行目がwhere句を満たすか判定します。

ここでサブクエリが出てくるので、サブクエリのfrom句で作られるテーブルをイメージ → 一行ずつwhere句で判定、を行いましょう。

サブクエリでは、where句のb.author_id = authors.id AND s.number_of_books <= 10を満たす行でselect句で指定された1が出力されます。1行目の場合は、サブクエリのfrom句で作られたテーブルのうち2行が1を返します。

そして外側のクエリのwhere句ではNOT EXISTSを使っています。 そのため、サブクエリが1行も返さない場合にtrueとなり、where句の条件を満たします。
1行目についてはサブクエリで2行返されているので、where句の条件は満たしません。

次の行に進んで、このwhere句での判定を繰り返します。

最終的な結果

全ての行についてこの処理を繰り返すと、Alexだけがwhere句の条件を満たし、select句の内容に従って最終的に以下のように出力されることが分かります。

+----+------+-------+-----------------+
| id | name | title | number_of_books |
+----+------+-------+-----------------+
|  3 | Alex | book5 |              15 |
|  3 | Alex | book6 |              30 |
+----+------+-------+-----------------+
2 rows in set (0.01 sec)

混乱しそうになっても一つ一つ処理を追っていけば確実に理解することができました。

補足: 今回使った環境やデータ

mysql 5.7

データ

CREATE TABLE authors (
  id       integer primary key,
  name     varchar(10) not null
);

CREATE TABLE books (
  id        integer primary key,
  author_id integer not null,
  title     varchar(10) not null
);

CREATE TABLE sales (
  id              integer primary key,
  book_id         integer not null,
  number_of_books integer not null
);

INSERT INTO authors VALUES (1, "John");
INSERT INTO authors VALUES (2, "Paul");
INSERT INTO authors VALUES (3, "Alex");

INSERT INTO books VALUES (1, 1, "book1");
INSERT INTO books VALUES (2, 1, "book2");
INSERT INTO books VALUES (3, 2, "book3");
INSERT INTO books VALUES (4, 2, "book4");
INSERT INTO books VALUES (5, 3, "book5");
INSERT INTO books VALUES (6, 3, "book6");

INSERT INTO sales VALUES (1, 1, 10);
INSERT INTO sales VALUES (2, 2, 5);
INSERT INTO sales VALUES (3, 3, 0);
INSERT INTO sales VALUES (4, 4, 5);
INSERT INTO sales VALUES (5, 5, 15);
INSERT INTO sales VALUES (6, 6, 30);