MySQL - Common Table Expressions: A Beginner's Guide

こんにちは、未来のデータベース魔术師たち！今日は、MySQLのCommon Table Expressions（CTE）の世界に足を踏み入れる興奮的な旅に出ます。プログラミングが初めてであっても心配しないでください。あなたの親切なガイドとして、私はステップバイステップで案内します。このチュートリアルの終わりには、プロのようにCTEを作成できるようになるでしょう！

MySQL - Common Table Expressions

What Are Common Table Expressions?（CTEとは何か？）

まずは基礎から始めましょう。大きなパーティを計画していると想像してみてください（誰だってデータベースのパーティが好きですよね？）。ゲストリスト、食事リスト、アクティビティリストを作成するかもしれません。これらのリストは、考えを整理し、計画を立てやすくする助けになります。Common Table Expressionsは、データベースクエリ用のそのようなリストのようなものです！

Common Table Expression、または略してCTEは、SELECT、INSERT、UPDATE、DELETE、MERGE文内で参照できる一時的な名前付きの結果セットです。クエリのdurationだけ存在する一時的なテーブルを作成するようなものです。すごいですね？

The MySQL WITH Clause: Your CTE Magic Wand（MySQLのWITH句：あなたのCTEの魔法の杖）

MySQLでは、WITH句を使ってCTEを作成します。これは、「MySQL、一時的な結果セットを作成したいんだし、それをXと呼びたい」と言うようなものです。簡単な例を見てみましょう：

WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;

これを分解してみましょう：

WITHはMySQLにCTEを定義すると伝えます。
cte_nameは私たちがCTEに与える名前です（好みに応じて名前を付けてください）。
ASはCTEのデータを定義するクエリに続きます。
CTEの定義の後には、CTEを使うメインクエリがあります。

実際の例を試してみましょう。学生とその成績のテーブルがあるとします：

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
grade INT
);

INSERT INTO students VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 78),
(4, 'Diana', 95),
(5, 'Eva', 88);

WITH high_achievers AS (
SELECT name, grade
FROM students
WHERE grade > 90
)
SELECT * FROM high_achievers;

この例では、成績が90以上の学生だけを含むCTE「high_achievers」を作成しました。その後、このCTEのすべての列を選択しました。結果はBobとDiana、私たちのハイアチーバーが表示されます！

CTEs from Multiple Tables: Joining the Party（複数のテーブルからのCTE：パーティに参加）

CTEは一つのテーブルに限定されません。あらゆる複雑さに対応できます！学校のデータベースにコーステーブルを追加してみましょう：

CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(50),
teacher VARCHAR(50)
);

INSERT INTO courses VALUES
(1, 'Math', 'Mr. Smith'),
(2, 'Science', 'Ms. Johnson'),
(3, 'History', 'Mrs. Brown');

CREATE TABLE enrollments (
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO enrollments VALUES
(1, 1), (1, 2), (2, 2), (3, 3), (4, 1), (4, 3), (5, 2);

WITH student_courses AS (
SELECT s.name AS student_name, c.name AS course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
),
science_students AS (
SELECT student_name
FROM student_courses
WHERE course_name = 'Science'
)
SELECT * FROM science_students;

これはすごいですね！分解してみましょう：

新しいテーブル「courses」と「enrollments」を作成しました。
「student_courses」というCTEを定義し、これらのテーブルを結合して学生とそのコースの一覧を取得しました。
「science_students」という別のCTEを定義し、最初のCTEを使って科学を履修している学生を探しました。
最後に、科学の学生を選択しました。

このクエリはAlice、Bob、Eva - 私たちの若い科学者たちを表示します！

The Power of CTEs: Why Should You Care?（CTEの力：なぜ関心を持つべきか？）

「こんな面倒なことをして何のために？subqueryで十分じゃない？」と疑問に思うかもしれません。CTEにはいくつかの利点があります：

Readability（可読性）：CTEは複雑なクエリを読みやすくし、理解しやすくします。大きな問題を小さな、管理しやすいチャンクに分けるようなものです。
Reusability（再利用性）：CTEはメインクエリ内で複数回参照できるため、subqueryを繰り返すよりも効率的です。
Recursion（再帰）：CTEは再帰的であり、階層的または木構造のデータを簡単に扱うことができます（でもこれは別の話題です）。

CTE Methods: Your Swiss Army Knife of Query Tools（CTEのメソッド：あなたのクエリツールの万能ナイフ）

CTEを使う様々な方法を簡単な表にまとめましょう：

メソッド	説明	例
Basic CTE	シンプルな名前付き結果セットを定義	`WITH cte AS (SELECT * FROM table)`
Multiple CTEs	一つのクエリで複数のCTEを定義	`WITH cte1 AS (...), cte2 AS (...)`
Nested CTEs	別のCTE内でCTEを使う	`WITH outer_cte AS (WITH inner_cte AS (...) SELECT * FROM inner_cte)`
Recursive CTEs	自身を参照するCTEを作成	`WITH RECURSIVE cte AS (...)`

CTEは強力なツールですが、賢く使ってください。複雑なクエリを整理するのに非常に便利ですが、簡単な操作にはストレートなSELECTが十分な場合もあります。

Conclusion: Your CTE Journey Begins!（結論：あなたのCTEの旅が始まる！）

おめでとうございます！Common Table Expressionsの世界の最初のステップを踏み出しました。基礎を学び、WITH句の使い方、複数のテーブルを使ったCTEも見てきました。実践を積むことで、プロのようにCTEを作成できるようになります。

MySQLの旅を続ける中で、CTEはあなたのクエリ作成ツールキットにおいて非常に価値のある存在になるでしょう。クリーンで効率的でメンテナンスしやすいコードを書けるようになります。そして、いつかあなたが他の人にCTEの魔法を教える日も来るかもしれません！

codingを続け、好奇心を持ち続け、あなたのクエリがいつも期待通りの結果を返すことを祈っています！次回まで、データを弄ぶ楽ししみを！

Credits: Image by storyset