SQL入門ガイド: SELECT文を完全マスター【詳細解説】

MySQLは、データベース管理システムで広く使用されているオープンソースのリレーショナルデータベース管理システムです。その中でも、SELECT文は最も基本的で重要なクエリ文です。本記事では、MySQLのSELECT文について、初心者にも理解しやすいように丁寧に解説します。具体的なサンプルデータとコード例を用いながら、各機能とパターンについて詳細に説明し、注意点も徹底的に解説します。

基本的に殆どのデータベースで動作するSQLを解説しますが、一部MySQLの関数などを使用しています。

SELECT文とは

SELECT文とは、リレーショナルデータベース（RDB）を操作する言語であるSQL（Structured Query Language）に用意された構文の一つで、テーブルから条件に従って行（レコード）を抽出するものです。

構文

SELECT文は以下の構文で記述します。

SELECT 列名
FROM テーブル名
WHERE 条件式
GROUP BY 列名
HAVING 条件式
ORDER BY 列名 [ASC|DESC]
LIMIT 行数;

SELECT句: SELECT句では、取得したいデータの列を指定します。列名をカンマで区切って指定することもできます。また、特定の列名の代わりにワイルドカード(*)を使用することで、全ての列を選択することもできます。
FROM句: FROM句では、データを抽出する対象となるテーブルを指定します。テーブル名は、データベース内のテーブルを指定するか、必要に応じてテーブル名にエイリアス（別名）を付けることもできます。
WHERE句: WHERE句は省略可能です。WHERE句では、特定の条件を指定して抽出するデータを絞り込むことができます。条件式を使用して、列の値と比較したり、論理演算子（AND, OR, NOT）を使用して複数の条件を組み合わせることができます。
GROUP BY句: GROUP BY句は省略可能です。GROUP BY句を使用することで、指定した列に基づいてデータをグループ化することができます。グループ化された結果は、通常は集計関数（SUM、COUNT、AVGなど）と一緒に使用されます。
HAVING句: HAVING句は省略可能です。HAVING句は、GROUP BY句でグループ化されたデータに対して、追加の条件を指定するために使用されます。WHERE句と似ていますが、HAVING句はグループ化されたデータに対して条件を指定するのに対し、WHERE句は抽出する行全体に対して条件を指定します。
ORDER BY句: ORDER BY句は省略可能です。ORDER BY句を使用することで、抽出結果を指定した列の値に基づいて昇順（ASC）または降順（DESC）で並び替えることができます。
LIMIT句: LIMIT句は省略可能です。LIMIT句では、抽出結果の行数を制限することができます。

列名入力規則

入力規則は、SELECT文を作成する際に使用するための構文ルールです。具体的には下記の様なものがあります。

列名に使用できる文字は半角英数字および、$,#,_(アンダースコア）です。但し、一部のDBMSにおける日本語版に限り全角文字が使用可能なものもあります。。
列名の先頭は半角アルファベットです。
文字上限は半角30文字までです。

他にも使用するデータベースにより、制約があるので使用するデータベースの制約を調べてみて下さい。

今回の記事で使用するテーブルとデータの準備

解説を始める前にテーブルを用意します。また、結果を分かりやすくするためにデータも数件用意します。SQLは即座に結果を確認できますので、是非、テーブルを作り、実際に実行して結果を確認して下さい。

テーブル作成

テーブルの作成は、以下のSQLを流して下さい。

-- departmentsテーブルの定義
CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- managersテーブルの定義
CREATE TABLE managers (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- sexesテーブルの定義
CREATE TABLE sexes (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- employeesテーブルの定義
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  birthday DATE,
  mail_address VARCHAR(100),
  sex_id int,
  department_id INT,
  salary DECIMAL(10, 2),
  hire_date DATE,
  manager_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (manager_id) REFERENCES employees(id),
  FOREIGN KEY (sex_id) REFERENCES sexes(id)
); 

-- usersテーブルの作成
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  gender VARCHAR(10),
  email VARCHAR(100)
);

-- customersテーブルの作成
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  city VARCHAR(100),
  country VARCHAR(100),
  phone VARCHAR(20)
);

-- studentsテーブルの作成
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  major VARCHAR(100),
  age INT,
  gpa DECIMAL(3, 2)
);

-- productsテーブルの作成
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(8, 2),
  category_id INT
);

-- sales_tableテーブルの作成
CREATE TABLE sales_table (
  id INT PRIMARY KEY,
  product_id INT,
  customer_id INT,
  quantity INT,
  price DECIMAL(8, 2),
  sale_date DATE
);

-- salariesテーブルの作成
CREATE TABLE salaries (
  id INT PRIMARY KEY,
  employee_id INT,
  salary DECIMAL(8, 2),
  effective_date DATE
);

SELECT文ではテーブルの結合なども使用するので、テーブルは多めに作ります。

初期データ投入

先程作成したテーブルへ初期データを投入します。以下のSQLを実行して下さい。

-- departmentsテーブルへのデータ挿入
INSERT INTO departments (id, name)
VALUES
  (1, 'Sales'),
  (2, 'Marketing'),
  (3, 'Finance'),
  (4, 'Human Resources');

-- managersテーブルへのデータ挿入
INSERT INTO managers (id, name)
VALUES
  (1, 'John Smith'),
  (2, 'Jane Doe'),
  (3, 'Michael Johnson'),
  (4, 'Emily Williams');

-- sexesテーブルへのデータ挿入
INSERT INTO sexes (id, name)
VALUES
  (1, 'Male'),
  (2, 'Female');

-- employeesテーブルへのデータ挿入
INSERT INTO employees (id, name, age, birthday, mail_address, sex_id, department_id, salary, hire_date, manager_id)
VALUES
  (1, 'John Smith', 30, '1992-05-15', 'john.smith@example.com', 1, 1, 5000.00, '2020-01-01', NULL),
  (2, 'Jane Doe', 28, '1994-09-22', 'jane.doe@example.com', 2, 2, 4500.00, '2020-02-01', 1),
  (3, 'Michael Johnson', 35, '1987-12-10', 'michael.johnson@example.com', 1, 1, 5500.00, '2019-01-01', NULL),
  (4, 'Emily Williams', 32, '1990-07-18', 'emily.williams@example.com', 2, 2, 4800.00, '2019-02-01', 3),
  (5, 'David Brown', 27, '1995-04-05', 'david.brown@example.com', 1, 1, 5200.00, '2020-03-01', 1),
  (6, 'Sarah Davis', 29, '1993-11-27', 'sarah.davis@example.com', 2, 2, 4600.00, '2020-04-01', 2),
  (7, 'Matthew Taylor', 33, '1989-09-12', 'matthew.taylor@example.com', 1, 3, 5800.00, '2018-01-01', NULL),
  (8, 'Olivia Martinez', 31, '1991-03-08', 'olivia.martinez@example.com', 2, 3, 5200.00, '2018-02-01', 7),
  (9, 'Daniel Anderson', 26, '1996-08-25', 'daniel.anderson@example.com', 1, 3, 5500.00, '2019-03-01', 7),
  (10, 'Sophia Thomas', 30, '1992-01-20', 'sophia.thomas@example.com', 2, 4, 5000.00, '2019-04-01', NULL),
   (11, 'Christopher Wilson', 29, '1994-06-08', 'christopher.wilson@example.com', 1, 1, 4800.00, '2021-01-01', 1),
  (12, 'Jessica Anderson', 27, '1996-03-15', 'jessica.anderson@example.com', 2, 2, 4400.00, '2021-02-01', 2),
  (13, 'Andrew Thompson', 31, '1992-10-20', 'andrew.thompson@example.com', 1, 3, 5200.00, '2022-01-01', 7),
  (14, 'Emily Davis', 28, '1995-07-25', 'emily.davis@example.com', 2, 3, 4800.00, '2022-02-01', 7),
  (15, 'Jacob Rodriguez', 33, '1989-12-30', 'jacob.rodriguez@example.com', 1, 4, 5500.00, '2022-03-01', NULL);


-- usersテーブルへの初期データの挿入
INSERT INTO users (id, name, age, gender, email)
VALUES
  (1, 'John Smith', 30, 'Male', 'john.smith@example.com'),
  (2, 'Jane Doe', 28, 'Female', 'jane.doe@example.com'),
  (3, 'Michael Johnson', 35, 'Male', 'michael.johnson@example.com'),
  (4, 'Emily Williams', 32, 'Female', 'emily.williams@example.com'),
  (5, 'David Brown', 27, 'Male', 'david.brown@example.com');

-- customersテーブルへの初期データの挿入
INSERT INTO customers (id, name, city, country, phone)
VALUES
  (1, 'John Smith', 'New York', 'USA', '123-456-7890'),
  (2, 'Jane Doe', 'London', 'UK', '987-654-3210'),
  (3, 'Michael Johnson', 'Los Angeles', 'USA', '456-789-0123'),
  (4, 'Emily Williams', 'Paris', 'France', '789-012-3456'),
  (5, 'David Brown', 'Tokyo', 'Japan', '012-345-6789');

-- studentsテーブルへの初期データの挿入
INSERT INTO students (id, name, major, age, gpa)
VALUES
  (1, 'John Smith', 'Computer Science', 20, 3.75),
  (2, 'Jane Doe', 'Business Administration', 21, 3.90),
  (3, 'Michael Johnson', 'Electrical Engineering', 19, 3.60),
  (4, 'Emily Williams', 'Psychology', 22, 3.80),
  (5, 'David Brown', 'English Literature', 20, 3.65);

-- sales_tableテーブルへの初期データの挿入
INSERT INTO sales_table (id, product_id, customer_id, quantity, price, sale_date)
VALUES
  (1, 1, 1, 5, 10.99, '2022-01-01'),
  (2, 2, 2, 3, 15.99, '2022-01-02'),
  (3, 3, 3, 2, 20.99, '2022-01-03'),
  (4, 1, 4, 4, 12.99, '2022-01-04'),
  (5, 2, 5, 1, 18.99, '2022-01-05');

-- salariesテーブルへの初期データの挿入
INSERT INTO salaries (id, employee_id, salary, effective_date)
VALUES
  (1, 1, 5000.00, '2022-01-01'),
  (2, 2, 4500.00, '2022-01-01'),
  (3, 3, 5500.00, '2022-01-01'),
  (4, 4, 4800.00, '2022-01-01'),
  (5, 5, 5200.00, '2022-01-01');

-- productsテーブルへの初期データの挿入
INSERT INTO products (id, name, price, category_id)
VALUES
  (1, 'Laptop', 999.99, 3),
  (2, 'T-Shirt', 19.99, 5),
  (3, 'Mouse', 29.99, 4),
  (4, 'Keyboard', 49.99, 4),
  (5, 'Dress', 59.99, 6);

もし、CREATE文やINSERT文の内容が分からない方は下記記事で詳しく解説していますので、ご覧ください。

SQL入門ガイド：MySQLのCREATE, ALTER, DROPを網羅的に解説MySQLは非常に人気のあるリレーショナルデータベース管理システムです。データベースの作成や変更、削除など、データベース管理に関連するさまざまな操作を実行するためのSQLコマンドがあります。この記事では、MySQLの「Create, Alter, Drop」ステートメントに焦点を当て、基本構文から応用的な使い方まで網羅的に解説します。また、テーブル、ビュー、トリガー、インデックス、プロシージャ、関数など、さまざまなオブジェクトの作成と変更についても詳しく説明します。...

SQL入門ガイド: MySQLのINSERT、UPDATE、DELETEの基本から応用まで完全解説MySQLは人気のあるリレーショナルデータベース管理システムであり、データの操作にはINSERT、UPDATE、DELETE文が使われます。この記事では、MySQLのINSERT、UPDATE、DELETE文について、初心者でも理解しやすいように丁寧に解説します。基本的な構文から応用的な使用方法まで、幅広くカバーしますので、ぜひ最後までご覧ください。...

列に別名をつける

SQLでは、SELECT文で取得される列の名前を変更する事ができます。列別名を指定することで、取得結果やクエリの可読性を向上させることができます。

構文

SELECT 列名 AS 別名
FROM テーブル名;

列に別名をつける場合、「AS」（省略可能）を指定し、AS句の後に別名をつけます。
次のように使用します。

使用例

SELECT 
name AS 従業員名, age AS 年齢
FROM employees;

employeesテーブルからname列を”従業員名”、age列を”年齢”として選択します。出力結果を分かりやすい表示に変えられるので、頻繁に使用する機能です。

また、列別名は、計算された列や複数の列を組み合わせて作成することもできます。

SELECT 
name, age, (YEAR(CURDATE()) - YEAR(birthday)) AS 年齢計算
FROM employees;

employeesテーブルからname列とage列を選択し、”年齢計算”という列別名で現在の年から誕生日の年を引いた結果を表示します。

列別名を使用することで、取得結果の意味や計算結果をわかりやすくすることができます。また、複雑なクエリの場合でも、列別名を指定することで可読性を向上させることができます。

ALL / DISTINCTの指定

簡単に言うと、SELECT文で重複した行を取り除くためのオプションです。これは実際に動かしてみると分かりやすいので、実行してみて下さい。

構文

SELECT [ALL | DISTINCT] 列
FROM テーブル名;

ALLの指定

ALLはデフォルトの動作であり、重複した行を取り除きません。明示的にALLを指定することもできます。

SELECT ALL name
FROM employees;

employeesテーブルからname列を取得しますが、重複した行を取り除かずに全ての行を返します。

DISTINCTの指定

DISTINCTは、SELECT文の結果から重複した行を取り除きます。

SELECT DISTINCT department_id
FROM employees;

employeesテーブルからdepartment_id列を取得し、重複した値を除いたユニークなdepartment_idを返します。

使用時の注意点

DISTINCTは単一の列または複数の列に適用することができます。複数の列を指定した場合、指定された列の組み合わせが重複している場合に重複を取り除きます。
ALLとDISTINCTは、通常はSELECT文の最初のキーワードとして指定されますが、それぞれ複数の列を指定する場合には、各列の前に指定することもできます。

以上のように、ALLとDISTINCTを使用することで、重複した行を取り除いたり、重複を許容したままデータを抽出することができます。

単一行関数

単一行関数とは1つの入力値に対して処理を行い、1つの結果を返す関数です。
関数名(引数) の形式で指定します。

構文

SELECT 関数名(引数) AS 列別名
FROM テーブル名;

関数名: 使用する単一行関数の名前を指定します。
引数: 関数に渡す入力値を指定します。引数は1つ以上指定することができます。

使用例

文字列関数の例

SELECT UPPER(name) AS 大文字名
FROM employees;

employeesテーブルからname列を取得し、UPPER関数を使用して名前を大文字に変換します。

数値関数の例

SELECT ABS(salary) AS 絶対値給与
FROM employees;

employeesテーブルからsalary列を取得し、ABS関数を使用して給与の絶対値を計算します。

単一関数使用時の注意点

使用するデータベースによって利用可能な単一行関数は異なる場合があります。
引数の型や数に関する制約がある場合があります。
一部の関数のみを示していますが、他にも多くの単一行関数があります。関数の種類と使用方法については、それぞれのデータベースのドキュメントやリファレンスを参照してください。

単一行関数は、データの変換や計算、条件の評価などのさまざまな操作に使用されます。これらの関数を利用することで、データの加工や集計、条件に基づいたデータの絞り込みなどを行うことができます。

グループ関数

グループ関数は、複数の行をグループ化して処理を行い、1つの結果を返す関数です。
関数名(列または式) の形で使用します。

構文

SELECT 関数名(列または式) AS 列別名
FROM テーブル名
GROUP BY 列または式;

関数名: 使用するグループ関数の名前を指定します。
列または式: 関数に渡す列または式を指定します。
GROUP BY: グループ化するための列または式を指定します。

GROUP BY句を指定しないと全行まとまってしまいます。集計のキーを適切にGROUP BY句に指定して下さい。

グループ関数の使用例

COUNT関数の例

SELECT COUNT(*) AS レコード数
FROM employees;

employeesテーブルの全レコード数をカウントします。

SUM関数の例

SELECT SUM(salary) AS 給与総額
FROM employees;

employeesテーブルの給与の総額を計算します。

AVG関数の例

SELECT AVG(age) AS 平均年齢
FROM employees;

employeesテーブルの従業員の平均年齢を計算します。

使用時の注意点

グループ関数を使用する場合、通常はSELECT文にグループ化された列または式が含まれる必要があります。これにより、グループごとの結果を取得することができます。
GROUP BY句を使用して、グループ化するための列または式を指定する必要があります。GROUP BY句に指定された列または式に基づいて、結果がグループ化されます。
SELECT文にグループ化された列または式以外の列を含める場合、それらの列はグループ化された結果に対して適切な集計関数（SUM、COUNT、AVGなど）を使用する必要があります。

グループ関数は、データの集計や集約、グループごとの結果の取得など、さまざまな操作に使用されます。

集合演算

集合演算は、複数のクエリ結果を組み合わせて新しい結果セットを生成するための操作です。主な集合演算としてUNION（和集合）、INTERSECT（積集合）、EXCEPT（差集合）があります。

MySQL 8.0.31 以前のバージョンでは「INTERSECT、EXCEPT」がありません。代わりの方法も解説します。

構文

SELECT 列名
FROM テーブル1
[集合演算子]
SELECT 列名
FROM テーブル2;

クエリ1, クエリ2: 組み合わせるクエリを指定します。クエリ1とクエリ2は同じ列構造を持つ必要があります。
集合演算子: 使用する集合演算を指定します。
- UNION: クエリ1とクエリ2の結果セットの和集合を返します。重複する行は削除されます。
- INTERSECT: クエリ1とクエリ2の結果セットの積集合を返します。共通する行のみが返されます。
- EXCEPT: クエリ1の結果セットからクエリ2の結果セットを差し引いた差集合を返します。

使用例

UNIONの例

SELECT name
FROM users
UNION
SELECT name
FROM customers;

table1とtable2のname列の結果セットの和集合を取得します。
和集合とは 2 つの集合の少なくともどちらか 1 つに含まれる要素の集合です。

INTERSECTの例

SELECT name
FROM users
INTERSECT
SELECT name
FROM customers;

table1とtable2のname列の結果セットの積集合を取得します。
積集合とは 2 つの集合の両方に含まれる要素の集合です。

EXCEPTの例

SELECT name
FROM users
EXCEPT
SELECT name
FROM customers;

table1のname列の結果セットからtable2のname列の結果セットを差し引いた差集合を取得します。
差集合とは集合 1 に含まれているけれど集合 2 には含まれていない要素の集合です。

注意点

集合演算子を使用するためには、クエリ1とクエリ2の結果セットの列構造が一致している必要があります。
UNIONやINTERSECTでは、重複する行は削除されます。重複を許したい場合はUNION ALLを使用します。
EXCEPTは一部のデータベースシステムではMINUSとして知られています。
集合演算は複数の結果セットを結合するため、パフォーマンスに影響を与える場合があります。大量のデータを扱う場合には注意が必要です。
集合演算を使用する際は、結果セットのサイズに注意してください。特にUNIONやINTERSECTは、結果セットのサイズが増える可能性があります。十分なメモリと処理能力が必要な場合があります。
クエリ1とクエリ2の結果セットは、同じ列のデータ型と順序を持つ必要があります。データ型が異なる場合は適切な型変換を行う必要があります。
集合演算子は、主に複数のテーブルや結果セットを組み合わせるために使用されます。データのフィルタリングや集計を行いたい場合は、それぞれのクエリに条件や集約関数を追加することが推奨されます。

集合演算は、データの組み合わせや比較を行う際に非常に役立つ機能です。複数のテーブルや結果セットを効率的に結合したり、重複を除外したりする場合に活用してください。

過去のMySQLでINTERSECTとEXCEPTを表現する方法

先述しましたが、MySQL 8.0.31 以前のバージョンのMySQLでは、EXCEPTとINTERSECTありませんでした。次の様に、サブクエリとLEFT JOINを使用してどちらも表現する事が可能です。

-- EXCEPTの代替クエリ
SELECT name
FROM users
WHERE name NOT IN (
  SELECT name
  FROM customers
);

-- INTERSECTの代替クエリ
SELECT DISTINCT u.name
FROM users u
LEFT JOIN customers c ON u.name = c.name
WHERE c.name IS NOT NULL;

上記のクエリを使用することで、EXCEPTおよびINTERSECTの結果と同様の名前のデータを取得することができます。LEFT JOINを使用して、usersテーブルとcustomersテーブルの共通の名前を抽出します。EXCEPTの代替クエリでは、サブクエリとNOT IN演算子を使用してusersテーブルに存在し、customersテーブルに存在しない名前を取得します。INTERSECTの代替クエリでは、LEFT JOINとIS NOT NULLを使用して共通の名前を抽出します。

FROM句

FROM句は何度も出てきていますが、ここで詳しく解説したいと思います。
FROM句は、クエリで使用するテーブルやビューを指定するための部分です。データを抽出する対象となるデータベースオブジェクトを指定します。

構文

FROM テーブル名　とテーブルを指定します。

SELECT 列名
FROM テーブル名
[JOIN 結合条件]
[WHERE 条件]
[GROUP BY 列名]
[HAVING 条件]
[ORDER BY 列名 [ASC|DESC]]

FROM テーブル名: データを抽出するテーブルまたはビューの名前を指定します。
JOIN 結合条件: 複数のテーブルを結合する際に使用します。結合条件を指定してテーブルを結合します。
WHERE 条件: 抽出するデータの条件を指定します。指定した条件に一致する行のみが抽出されます。
GROUP BY 列名: 指定した列を基準にデータをグループ化します。集約関数と一緒に使用されることが一般的です。
HAVING 条件: GROUP BY 句でグループ化されたデータに対する条件を指定します。HAVING句は、WHERE句と同様の役割を果たしますが、グループ化されたデータに対してフィルタリングを行います。
ORDER BY 列名 [ASC|DESC]: 抽出したデータを指定した列の値で並び替えます。ASCは昇順、DESCは降順を意味します。

使用例

基本的なFROM句の例

SELECT *
FROM employees;

employeesテーブルからすべての列のデータを抽出します。

FROM句とJOINの例

結合（JOIN）の解説は後で詳しく行います。

SELECT employees.name, departments.name
FROM employees
JOIN departments ON employees.department_id = departments.id;

employeesテーブルとdepartmentsテーブルを結合して、従業員の名前と所属部門の名前を取得します。

GROUP BYとHAVINGの例

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 3;

employeesテーブルを部門ごとにグループ化し、従業員数が3人以上の部門のdepartment_idと従業員数を抽出します。

注意点

FROM句で指定するテーブルやビューは存在することを確認してください。
結合操作を行う場合は、適切な結合条件を指定してください。
テーブルやビューにエイリアスを使用することで、短い名前で参照できます。

FROM句は、クエリで使用するデータの出所を指定する重要な部分です。適切にテーブルやビューを選択し、必要な結合やエイリアスを指定することで、正確なデータの抽出や結果の取得が可能になります。

結合（JOIN句）

結合（JOIN句）は、複数のテーブルから関連するデータを結合するための操作です。結合を使用することで、複数のテーブルのデータを組み合わせてより複雑なクエリを作成できます。結合には以下の種類があります。

クロス結合
等価結合
非等価結合
自然結合
自己結合
外部結合

順にそれぞれをかいせつします。

クロス結合（CROSS JOIN）

クロス結合は、結合するテーブルのすべての行を組み合わせて結果を作成します。

構文

SELECT 列名
FROM テーブル1
CROSS JOIN テーブル2;

クロス結合をおこなうには、CROSS JOIN句を使用して結合するテーブルを指定します。

使用例

SELECT employees.name, departments.name
FROM employees
CROSS JOIN departments;

このクエリでは、employeesテーブルとdepartmentsテーブルのクロス結合を実行しています。結果は、従業員テーブルと部門テーブルのすべての組み合わせが表示されます。このような結合は、データのカートシアン積を作成するため、結果の行数が大幅に増えることに注意してください。

等価結合（INNER JOIN）

等価結合は、結合条件で指定した列の値が一致する行のみを結合します。

構文

SELECT 列名
FROM テーブル1
INNER JOIN テーブル2
ON 結合条件;

等価結合を行うには、INNER JOIN句で結合テーブルを指定し、ON句で結合条件を記述します。結合条件に一致した行のみ取得されます。INNERは省略可能です。

使用例

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

このクエリでは、employeesテーブルとdepartmentsテーブルの等価結合を実行しています。従業員テーブルと部門テーブルを部門ID（department_id）で結合しています。結果は、従業員の名前と所属部門の名前が一致する行のみ表示されます。

非等価結合（NON-EQUI JOIN）

非等価結合は、結合条件で指定した列の値が一致しない場合に行を結合します。
INNER JOIN句を使用します。INNERは省略可能です。

使用例

SELECT employees.name, departments.name
FROM employees
JOIN departments
ON employees.age > departments.min_age;

このクエリでは、employeesテーブル部門とdepartmentsテーブルの非等価結合を実行しています。最低年齢（departments.min_age）よりも年齢が高い従業員と部門の非等価結合を行っています。結果は、従業員の名前と所属部門の名前が条件を満たす行のみ表示されます。

自然結合（NATURAL JOIN）

自然結合は、テーブル間の共通列名を使用して自動的に結合する結合方法です。結合条件を指定する必要がなく、共通列名のみを使用します。

SELECT 列名
FROM テーブル1
NATURAL JOIN テーブル2;

自然結合を行うには、NATURAL JOIN句で結合テーブルを指定します。

使用例

SELECT employees.name, departments.name
FROM employees
NATURAL JOIN departments;

このクエリでは、employeesテーブルとdepartmentsテーブルの自然結合を実行しています。従業員テーブルと部門テーブルの同じ列名（ここでは”name”）を持つ列で結合が行われます。結果は、列名が同じであるため、重複する列名は1回だけ表示されます。

自己結合（SELF JOIN）

自己結合は、同じテーブル内の異なる行を結合する結合方法です。通常、自己結合ではテーブルにエイリアス（別名）を使用します。

構文

SELECT 列名
FROM テーブル AS t1
JOIN テーブル AS t2
ON 結合条件;

使用例

SELECT e1.name, e2.name
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.id;

このクエリでは、employeesテーブルを自己結合しています。従業員テーブル内で、manager_id（上司のID）が同じ従業員を結合しています。結果は、従業員の名前とその上司の名前の組み合わせが表示されます。

外部結合（OUTER JOIN）

外部結合は、結合条件に一致しない行も結果に含める結合方法です。外部結合には左外部結合、右外部結合、完全外部結合の3つの種類があります。

左外部結合（LEFT JOIN）

左側のテーブルのすべての行と、右側のテーブルの一致する行を結合します。

右外部結合（RIGHT JOIN）

右側のテーブルのすべての行と、左側のテーブルの一致する行を結合します。

構文

SELECT 列名
FROM テーブル1
LEFT / RIGHT OUTER JOIN テーブル2
ON 結合条件;

左外部結合の場合、LEFTを指定、右外部結合の場合、RIGHTを指定します。OUTERは省略可能です。

左外部結合、右外部結合の使用例

SELECT employees.name, departments.name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.id;

このクエリでは、employeesテーブルとdepartmentsテーブルの左外部結合（LEFT OUTER JOIN）を実行しています。従業員テーブルのすべての行を保持しながら、部門テーブルとの結合が行われます。結果は、従業員の名前と所属部門の名前が一致する行が表示されますが、従業員が部門に所属していない場合はNULLが表示されます。

注意点

結合するテーブルのカラム名が重複している場合は、明示的にテーブル名やエイリアスを付けて参照する必要があります。
結合条件はON句で指定され、結合する列の値が一致する行のみが結合されます。
クロス結合では結果の行数が爆発的に増えることに注意してください。
結合するテーブルのサイズや結合条件の適切な指定に注意してください。効率的な結合のためには、適切なインデックスの設定や条件の最適化が必要です。

結合は、複数のテーブルから必要なデータを取得するために重要な操作です。適切な結合方法の選択と条件の指定は、正確な結果を得るために重要です。

完全外部結合（FULL OUTER JOIN）

MySQLではFULL OUTER JOINが直接的にはサポートされていないため、LEFT JOINとRIGHT JOINを組み合わせる方法を使用して完全外部結合を模倣します。以下にサンプルコードを示します。

SELECT *
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
UNION
SELECT *
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id
WHERE employees.department_id IS NULL;

このクエリでは、employeesテーブルとdepartmentsテーブルの間でLEFT JOINとRIGHT JOINを実行しています。最初のSELECT文では、employeesテーブルを左外部結合し、次にdepartmentsテーブルを右外部結合しています。そして、UNION演算子を使用して2つの結果を結合しています。

また、2つ目のSELECT文では、employeesテーブルを右外部結合し、departmentsテーブルを左外部結合しています。そして、WHERE句を使用してemployeesテーブルのdepartment_idがNULL（つまり、employeesテーブルには存在しない）行のみを抽出しています。

これにより、employeesテーブルとdepartmentsテーブルの両方の行を含む完全外部結合の結果を取得することができます。

注意点として、UNION演算子は重複行を削除するため、重複する行を含む場合はUNION ALLを使用する必要があります。

WHERE句

これまでにも何度も出てきているWHERE句ですが、ここでしっかり解説したいと思います。

WHERE句は、SELECT文やUPDATE文、DELETE文などで使用される条件式を指定するためのクラウスです。WHERE句を使用することで、特定の条件に一致する行のみを取得したり、更新や削除の対象を絞り込んだりすることができます。

構文

SELECT 列名
FROM テーブル名
WHERE 条件式;

UPDATE テーブル名
SET 列名 = 値
WHERE 条件式;

UPDATE テーブル名
SET 列名 = 値
WHERE 条件式;

WHERE 条件式　と記述します。

特定の条件に基づいてデータをフィルタリングする: 例えば、特定の日付範囲内の注文データを取得するために、WHERE句を使用して「注文日 >= ‘2023-01-01’ AND 注文日 <= ‘2023-01-31’」といった条件を指定します。
複数の条件を組み合わせてデータを絞り込む: 複数の条件式をANDやORを使用して組み合わせることで、より具体的なデータの絞り込みが可能です。

使用例

-- employeesテーブルからageが30歳以上の従業員を取得する
SELECT *
FROM employees
WHERE age >= 30;

-- employeesテーブルのsalaryが5000以上かつdepartment_idが2の従業員を更新する
UPDATE employees
SET salary = salary * 1.1
WHERE salary >= 5000 AND department_id = 2;

-- employeesテーブルからsex_idが1または3の従業員を削除する
DELETE FROM employees
WHERE sex_id IN (1, 3);

SELECTの例では、employeesテーブルからage（年齢）が30歳以上の従業員を抽出しています。WHERE句には条件式「age >= 30」が指定されており、この条件を満たす行のみが結果に含まれます。

UPDATEの例では、employeesテーブルのsalary（給与）が5000以上かつdepartment_idが2の従業員の給与を10%増加させています。WHERE句には複数の条件式「salary >= 5000」および「department_id = 2」が指定されており、これらの条件を満たす行のみが更新の対象となります。

DELETEの例では、employeesテーブルのsex_id（性別ID）が1または3の従業員を削除しています。WHERE句には条件式「sex_id IN (1, 3)」が指定されており、この条件を満たす行が削除されます。

注意点

WHERE句の条件式は、比較演算子（=、<、>など）や論理演算子（AND、OR、NOT）を使用して複数の条件を組み合わせることができます。
文字列や日付の場合は、適切な引用符（シングルクォーテーション）で囲む必要があります。
複雑な条件を指定する場合は、カッコを使用して条件の優先順位を明示することができます。
WHERE句はSELECT文だけでなく、UPDATE文やDELETE文などのデータの変更や削除にも使用されます。
パフォーマンスの観点から、効率的な検索を行うためにはインデックスの使用や適切な条件の選択が重要です。

WHERE句はデータベースクエリの中で非常に重要な役割を果たし、データのフィルタリングや条件に基づいた操作を行う際に不可欠な機能です。適切に使用することで、必要なデータを正確に取得したり、データの更新や削除を効率的に行ったりすることができます。

パターンマッチング検索

パターンマッチング検索は、文字列の中から特定のパターンに一致する部分を検索するための手法です。MySQLでは、LIKE演算子とワイルドカードを使用してパターンマッチング検索を行います。

構文

SELECT 列名
FROM テーブル名
WHERE 列名 LIKE 'パターン';

列名 LIKE ‘パターン’　の様にしていします。

特定の文字列を含むデータを検索する: パターンにワイルドカードを組み合わせることで、特定の文字列を含むデータを検索することができます。例えば、カテゴリ名に「アクセサリー」を含む商品を検索するために、「%アクセサリー%」という検索パターンを使用します。
特定の文字列で始まるデータを検索する: パターンの先頭にワイルドカードを使用することで、特定の文字列で始まるデータを検索することができます。例えば、都道府県名が「北海道」で始まる顧客を検索するために、「北海道%」という検索パターンを使用します。
特定の文字列で終わるデータを検索する: パターンの末尾にワイルドカードを使用することで、特定の文字列で終わるデータを検索することができます。例えば、ファイル名が「.jpg」で終わる画像ファイルを検索するために、「%.jpg」という検索パターンを使用します。

使用例

-- employeesテーブルから名前が「John」で始まる従業員を取得する
SELECT *
FROM employees
WHERE name LIKE 'John%';

この例では、employeesテーブルから名前が「John」で始まる従業員を抽出しています。LIKE演算子を使用し、検索パターンとして「John%」を指定しています。ワイルドカードの「%」は、0文字以上の任意の文字列を表します。

-- employeesテーブルからメールアドレスが「example.com」で終わる従業員を取得する
SELECT *
FROM employees
WHERE mail_address LIKE '%@example.com';

この例では、employeesテーブルからメールアドレスが「example.com」で終わる従業員を取得しています。LIKE演算子を使用し、検索パターンとして「%@example.com」を指定しています。ワイルドカードの「%」は、0文字以上の任意の文字列を表します。

注意点

ワイルドカードの使用には注意が必要です。%は0文字以上の任意の文字列を表すため、検索パフォーマンスに影響を与える可能性があります。適切なインデックスが設定されていない場合や、大量のデータを検索する場合は、ワイルドカードの位置と使用方法に注意してください。
パターンマッチングは大文字と小文字を区別します。デフォルトでは、MySQLは大文字と小文字を区別しない場合もありますが、データベースの設定によっては区別される場合があります。正確なパターンマッチングを行うためには、大文字と小文字を正しく指定してください。
正規表現を使用した高度なパターンマッチングも可能です。MySQLではREGEXP演算子を使用して正規表現を利用したパターンマッチングを行うことができます。

パターンマッチング検索は、文字列データを柔軟に検索するための強力な手法です。ワイルドカードを使用してパターンを指定することで、さまざまな検索条件に一致するデータを見つけることができます。

BETWEEN

BETWEEN演算子は、指定された範囲内にある値を検索するために使用されます。

構文

SELECT 列名
FROM テーブル名
WHERE 列名 BETWEEN 値1 AND 値2;

列名 BETWEEN 値1 AND 値2 の様に範囲を下限値、上限値で指定します。

使用例

-- employeesテーブルから年齢が25歳から30歳の従業員を取得する
SELECT *
FROM employees
WHERE age BETWEEN 25 AND 30;

この例では、employeesテーブルから年齢が25歳から30歳までの従業員を抽出しています。BETWEEN演算子を使用し、検索範囲として25と30を指定しています。

-- employeesテーブルから給料が5000以上6000以下の従業員を取得する
SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 6000;

この例では、employeesテーブルから給料が5000以上6000以下の従業員を抽出しています。BETWEEN演算子を使用し、検索範囲として5000と6000を指定しています。

注意点

BETWEEN演算子は指定された範囲に等しい値も含みます。つまり、BETWEEN x AND yはx <= 列名 <= yと同等です。
BETWEEN演算子は数値や日付などの範囲を検索する際に便利ですが、文字列の場合には注意が必要です。文字列の場合、辞書順に比較されるため、意図した結果が得られない場合があります。文字列の範囲検索には注意して使用してください。
NULL値はBETWEEN演算子の対象外です。NULLを含む場合は別途条件を追加する必要があります。

BETWEEN演算子を使用することで、指定した範囲内にある値を簡単に検索することができます。適切な範囲とデータ型を指定して利用し、条件に一致するデータを正確に抽出しましょう。

NULL処理

NULL処理は、NULL値を含むデータの取り扱いに関する概念です。NULLはデータが存在しないことや未知であることを表す特殊な値であり、データベース内で頻繁に使用されます。

構文

SELECT 列名
FROM テーブル名
WHERE 列名 IS NULL;

SELECT 列名
FROM テーブル名
WHERE 列名 IS NOT NULL;

NULLは、「=」などの演算子で比較できません。IS NULLはNULLであるものを取得、IS NOT NULLはNULLを除外して取得するためにしようされます。

使用例

-- employeesテーブルからマネージャーが割り当てられていない従業員を取得する
SELECT *
FROM employees
WHERE manager_id IS NULL;

この例では、employeesテーブルからマネージャーが割り当てられていない（manager_idがNULLである）従業員を抽出しています。IS NULL演算子を使用して、NULL値をチェックしています。

-- employeesテーブルからマネージャーが割り当てられている従業員を取得する
SELECT *
FROM employees
WHERE manager_id IS NOT NULL;

この例では、employeesテーブルからマネージャーが割り当てられている（manager_idがNULLではない）従業員を抽出しています。IS NOT NULL演算子を使用して、NULLでない値をチェックしています。

-- employeesテーブルの従業員の給料を取得するが、給料がNULLの場合は0として表示する
SELECT COALESCE(salary, 0) AS salary
FROM employees;

この例では、employeesテーブルの従業員の給料を取得しています。給料がNULLの場合は代わりに0を表示するためにCOALESCE関数を使用しています。COALESCE関数は、最初の引数から順番に値を評価し、NULL以外の最初の値を返します。

注意点

NULL処理は、データベース内でNULL値を適切に扱うための重要な考慮事項です。NULL値を無視したクエリや演算は、正確な結果を提供しない可能性があります。
NULL値と他の値を比較する場合、NULL値は特別な扱いを受けます。例えば、NULL値との等値比較（=）や不等値比較（<>）は常にUNKNOWNを返します。
NULL値はインデックスや一意制約の検索に影響
を与える可能性があります。インデックスは通常、データの高速検索をサポートするために使用されますが、NULL値を含む列に対しては特別な処理が必要です。NULL値を持つ列を検索する場合、インデックスの効率性が低下することがあります。
NULL値を比較する際には、IS NULL演算子またはIS NOT NULL演算子を使用することが推奨されます。これらの演算子は、NULL値のチェックに特化しており、正確な結果を返します。
NULL値を含む列に対して演算を行う場合、その結果もNULLになる可能性があります。例えば、NULL値との加算や乗算は常にNULLを返します。そのため、演算の結果を正しく取り扱うためには、NULL値の存在を適切に考慮する必要があります。
COALESCE関数は、NULL値を他の値で置き換えるために使用されます。COALESCE関数は、引数として与えられた値を順番に評価し、最初にNULL以外の値を返します。これにより、NULL値を別のデフォルト値で置き換えることができます。

NULL処理はデータベースのクエリやデータ操作において重要な要素であり、データの正確性と一貫性を保つために適切に取り扱う必要があります。適切なNULL処理を行うことで、データの取得や処理の結果において予期しない動作やエラーを回避できます。

GROUP BY句

GROUP BY句は、SQLのクエリで使用される句の一つであり、結果セットをグループ化するために使用されます。GROUP BY句を使用することで、特定の列の値に基づいて行をグループ化し、グループごとに集計や演算を行うことができます。

構文

SELECT 列1, 列2, ... FROM テーブル名 WHERE 条件 GROUP BY 列1, 列2, ...

列1, 列2, …: グループ化する列を指定します。これらの列はSELECT句にも含まれている必要があります。
テーブル名: クエリの対象となるテーブルの名前を指定します。
条件: オプションで、結果をフィルタリングするための条件を指定します。

使用例

SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id;

上記の例では、「employees」テーブルの「department_id」列を基準に行をグループ化しています。そして、各グループごとの従業員数をCOUNT関数を使って集計しています。結果セットには、各部署のdepartment_idと従業員の数（employee_count）が含まれます。

他にも、下記の様にAVG関数を使用すると、部署ごとの平均給与を求められ、MAX関数を使用すると最高給与を求めることが出来ます。関数は色々便利なものがありますので、調べてみてください。

SELECT department_id, AVG(salary) as average_salary, MAX(salary) as max_salary
FROM employees
GROUP BY department_id;

注意点

GROUP BY句で指定する列は、SELECT句にも含まれている必要があります。ただし、MySQLでは非標準の拡張構文を使っている場合、SELECT句に含まれない列をGROUP BY句で指定することも可能ですが、予期しない結果が生じる可能性があるため、注意が必要です。
GROUP BY句を使用すると、結果セットがグループごとにまとめられるため、集計関数（COUNT、SUM、AVGなど）を使用する場合に有用です。
GROUP BY句を使用する際には、適切なインデックスが設定されていることが重要です。
GROUP BY句とHAVING句の組み合わせ: HAVING句は、GROUP BY句でグループ化された結果に対して条件を指定するために使用されます。HAVING句はWHERE句と似ていますが、WHERE句はグループ化される前にフィルタリングを行い、HAVING句はグループ化された後にフィルタリングを行います。
GROUP BY句とORDER BY句の組み合わせ: GROUP BY句と組み合わせて、グループごとの結果を特定の順序で表示することもできます。ORDER BY句はGROUP BY句の後に使用され、グループごとの結果の並び順を指定します。

GROUP BY句とHAVING句の組み合わせの例

SELECT department_id, AVG(salary) as average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

上記の例では、部署ごとの平均給与が5000より大きい部署のみを取得しています。

GROUP BY句とORDER BY句の組み合わせの例

SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;

上記の例では、従業員数が多い順に部署を並べ替えています。

これらのポイントに留意しながら、GROUP BY句を使用してデータをグループ化し、集計や結果の絞り込みを行うことができます。

ORDER BY句

ORDER BY句は、SELECT文で取得した結果セットの並び順を指定するために使用されます。デフォルトでは、取得した行は返される順序は保証されていませんが、ORDER BY句を使用することで特定の列や式に基づいてソートすることができます。

構文

SELECT 列名
FROM テーブル名
ORDER BY 列名 [ASC|DESC];

列名: ソートする列を指定します。SELECT文で指定した列や式を使用することができます。
ASC|DESC: 昇順（ASC）または降順（DESC）で結果をソートします。デフォルトは昇順です。

使用例

SELECT name, age, salary
FROM employees
ORDER BY age DESC;

上記の例では、employeesテーブルからname、age、salaryの列を取得し、age列を降順でソートしています。結果はageの降順になります。

SELECT name, hire_date, salary
FROM employees
ORDER BY hire_date DESC, salary ASC;

上記の例では、employeesテーブルからname、hire_date、salaryの列を取得し、hire_date列を降順、hire_dateが同じ場合はsalary列を昇順でソートしています。結果は入社日の降順に並び、入社日が同じ場合は給与の昇順に並びます。

注意点

ORDER BY句はSELECT文の最後に配置する必要があります。
ORDER BY句では、SELECT文で使用されている列のエイリアス（列別名）や式を指定することもできます。
複数の列でソートする場合、指定した列の順序に従ってソートされます。最初に指定した列が優先され、次に指定した列でソートされます。たとえば、ORDER BY col1, col2のように指定すると、col1でまずソートされ、col1が同じ場合にはcol2でソートされます。
ソートする列には適切なインデックスが設定されていることを確認すると効果的です。大量のデータをソートする場合、インデックスがあるとソートのパフォーマンスが向上します。
文字列の場合、ソートは辞書順で行われます。大文字と小文字は区別されます。
NULLの扱いに注意してください。デフォルトでは、NULL値は最後にソートされます。
NULLを任意の順序に並べ替えるには、こちらのページで紹介している内容をご覧ください。

ORDER BY句は、データのソートや並び替えを行う際に非常に重要です。正しい列の指定と適切なソート順序の選択により、求める結果を得ることができます。

CASE式

CASE式はSQLクエリで条件に応じて値を返すために使用される制御フロー構文です。条件に基づいて異なる結果を返すことができます。

構文

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  ELSE result_n
END

CASE式は複数のWHEN節と1つのELSE節から構成されます。各WHEN節では、特定の条件が満たされた場合に対応する結果を定義します。ELSE節は、いずれの条件も満たされなかった場合に返すデフォルトの結果を指定します。

使用例

SELECT
  name,
  CASE
    WHEN age < 30 THEN 'Young'
    WHEN age >= 30 AND age < 40 THEN 'Middle-aged'
    ELSE 'Senior'
  END AS age_group
FROM employees;

この例では、employeesテーブルから名前(name)と年齢(age)を取得しています。CASE式は年齢に応じて異なる年齢グループを返します。

SELECT
  departments.name AS department,
  MAX(CASE WHEN YEAR(employees.hire_date) = 2020 AND MONTH(employees.hire_date) = 1 THEN employees.name END) AS `2020-01`,
  MAX(CASE WHEN YEAR(employees.hire_date) = 2020 AND MONTH(employees.hire_date) = 2 THEN employees.name END) AS `2020-02`,
  MAX(CASE WHEN YEAR(employees.hire_date) = 2020 AND MONTH(employees.hire_date) = 3 THEN employees.name END) AS `2020-03`,
  MAX(CASE WHEN YEAR(employees.hire_date) = 2021 AND MONTH(employees.hire_date) = 1 THEN employees.name END) AS `2021-01`,
  MAX(CASE WHEN YEAR(employees.hire_date) = 2021 AND MONTH(employees.hire_date) = 2 THEN employees.name END) AS `2021-02`,
  MAX(CASE WHEN YEAR(employees.hire_date) = 2022 AND MONTH(employees.hire_date) = 1 THEN employees.name END) AS `2022-01`,
  MAX(CASE WHEN YEAR(employees.hire_date) = 2022 AND MONTH(employees.hire_date) = 2 THEN employees.name END) AS `2022-02`,
  MAX(CASE WHEN YEAR(employees.hire_date) = 2022 AND MONTH(employees.hire_date) = 3 THEN employees.name END) AS `2022-03`
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
GROUP BY departments.name;

この例では、YEAR(employees.hire_date)とMONTH(employees.hire_date)を使用して従業員の採用年と月を抽出しています。それに基づいて、各月ごとに従業員の名前をピボット集計しています。列名は各月の年と月を組み合わせています。

このクエリを実行すると、各部署ごとに月ごとの従業員の名前が表示されます。例えば売上推移表の様なものを作成する際に利用できる方法です。

注意点

CASE式を使用する際には、いくつかの注意点があります。

CASE式は単一の値を返します。各WHEN節やELSE節で1つの結果を指定する必要があります。
CASE式の条件は、一般的な比較演算子（<、>、=など）や論理演算子（AND、ORなど）を使用して指定します。
CASE式はネストすることもできます。つまり、CASE式の結果として別のCASE式を使用することができます。
CASE式は可読性を向上させるために使用されることがありますが、複雑なロジックを含む場合は理解しやすくなるようにコメントを追加することが重要です。
CASE式はデータの変換やカテゴリ分けなど、SQLのクエリの一部として使用されますが、それ以外の場合には他のプログラミング言語やツールを使用して条件分岐を行うことも検討する価値があります。

CASE式はSQLクエリの強力な機能であり、条件に基づいて値を返すため非常に柔軟にデータを操作できます。柔軟性の高いデータ変換や集計、条件付きの結果の取得などに活用してください。

副問合せ

副問合せ（Subquery）は、クエリ内に含まれる別のクエリです。副問合せは、外部のクエリと結果を共有することなく、独立して実行されます。副問合せは、データの抽出や条件の評価、集計など、さまざまな目的で使用されます。

構文

副問合せは、主クエリ内の任意の場所に記述することができます。一般的な構文は以下の通りです。

SELECT 列1, 列2, ...
FROM テーブル
WHERE 条件式 IN (副問合せ);

副問合せは主クエリとは独立して実行され、その結果を主クエリの処理に利用します。副問合せは、SELECT文、FROM句、WHERE句、HAVING句、ORDER BY句など、主クエリのさまざまな場所で使用することができます。
副問合せは、以下のような場面で活用されます。

条件式の評価: 副問合せを使用して、特定の条件を満たすデータを抽出することができます。例えば、特定の部門に所属する従業員の情報を抽出するなどがあります。
データの比較や参照: 副問合せを使用して、データの比較や参照を行うことができます。例えば、あるテーブルのデータと別のテーブルのデータを比較し、一致するレコードを抽出するなどがあります。
集計や統計の演算: 副問合せを使用して、データの集計や統計的な演算を行うことができます。例えば、平均値や合計値の計算などがあります。

使用例

SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

この例では、副問合せ (SELECT MAX(salary) FROM employees) を使用して、employeesテーブル内で最大の給与を持つ従業員の情報を取得しています。

SELECT *
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

この例では、副問合せを使用して、departmentsテーブルから部門名が ‘Sales’ である部門のIDを取得し、それを使用してemployeesテーブルから該当する従業員の情報を抽出しています。

SELECT department_id, COUNT(*) as employee_count
FROM employees
GROUP BY department_id
HAVING employee_count >
 (SELECT AVG(employee_count) 
    FROM (
      SELECT department_id, COUNT(*) as employee_count 
         FROM employees GROUP BY department_id) AS subquery);

この例では、副問合せを使用して、各部門の従業員数の平均値を計算し、それを使用して部門ごとに従業員数が平均を超える部門の情報を抽出しています。

注意点

副問合せ内のクエリは、必要なデータを正確に取得するために適切に設計する必要があります。パフォーマンスを最適化するために、必要なインデックスが存在することを確認してください。
副問合せは入れ子にすることもできます。複雑な条件や複数のデータの比較が必要な場合に使用することができますが、可読性には注意が必要です。
副問合せ内で使用する関数や演算子は、サブクエリの結果に適用されます。演算子や関数の結果がサブクエリの結果に影響を与える場合は、注意が必要です。
副問合せを使用する際には、データの一貫性やパフォーマンスに影響を与える可能性があるので、注意深く検討する必要があります。必要な場合にのみ使用し、効率的なクエリの作成を心がけましょう。

IN / EXISTS 演算子

INとEXISTSは、サブクエリを使用して主クエリとの条件のマッチングを行うための演算子です。これらの演算子は、特定の条件に基づいて結果セットをフィルタリングするために使用されます。

構文

-- IN演算子
expression IN (subquery)

-- EXISTS演算子
EXISTS (subquery)

expression: マッチングを行うための式やカラムを指定します。
subquery: 条件のマッチングに使用するサブクエリを指定します。

IN演算子の動作

IN演算子は、expressionがsubqueryの結果セットに含まれるかどうかを判定します。サブクエリの結果がexpressionと一致する場合、真となります。

使用例

SELECT *
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

employeesテーブルから部門IDがdepartmentsテーブルの’Sales’と一致する従業員を抽出しています。サブクエリは、departmentsテーブルから部門名が’Sales’である部門のIDを取得しています。

EXISTS演算子の動作

EXISTS演算子は、サブクエリの結果セットが空でないかどうかを判定します。サブクエリの結果が少なくとも1つの行を返す場合、真となります。

使用例

SELECT *
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

departmentsテーブルから従業員を持つ部門の情報を抽出しています。サブクエリは、employeesテーブルから該当部門IDの従業員が存在するかどうかをチェックしています。

注意点

IN演算子とEXISTS演算子は、サブクエリの結果を利用して条件のマッチングを行うため、効率的に使用することが重要です。適切なインデックスが設定されていることを確認し、クエリのパフォーマンスを最適化しましょう。
IN演算子は、一連の値との一致を確認する場合に使用されます。一方、EXISTS演算子は、特定の条件を満たす行が存在するかどうかを確認する場合に使用されます。
IN演算子は、サブクエリの結果が重複する値を含んでいる場合、それら全てにマッチングします。一方、EXISTS演算子は、サブクエリの結果が存在するかどうかのみを判定するため、重複する値は考慮されません。

LIMIT句

LIMIT句は、クエリ結果の取得件数を制限するために使用されます。結果セットの先頭から指定された数の行を取得することができます。

構文

SELECT 列名
FROM テーブル名
LIMIT 行数;

使用方法

SELECT * 
FROM employees 
LIMIT 5;

上記の例では、employeesテーブルから先頭の5行を取得しています。取得したい行数をLIMITの後に指定することで、その数だけの行が結果として返されます。

SELECT * 
FROM products 
LIMIT 10 OFFSET 20;

この例では、productsテーブルから20行目から10行を取得しています。OFFSET句は、先頭からのオフセット（スキップする行数）を指定するために使用されます。このようなページネーション処理をSQLで実装できます。

注意点

LIMIT句はデータベースエンジンに依存するため、使用するデータベースによって挙動が異なる場合があります。特にORDER BYと組み合わせた場合の振る舞いには注意が必要です。
LIMIT句はクエリ結果に適用されるため、効果的に使用するためには適切なORDER BY句との組み合わせが必要です。ソートされていない結果セットに対してLIMITを使用すると、結果が不正確になる可能性があります。
OFFSET句を使用する場合、データベースエンジンは指定されたオフセット分の行をスキップするため、パフォーマンスに影響を与える場合があります。大量のデータを処理する場合には注意が必要です。

再帰クエリ

再帰クエリ（Recursive Query）は、SQLの機能の一つであり、自己参照的なクエリを実行することができます。再帰クエリを使用すると、階層的なデータやツリー構造を持つデータを扱う際に便利です。

所謂グラフ理論など特別難しい概念の部分になります。解説はしますが、理解できなくても通常問題ありません。興味のある方は是非取り組んでみてください。

構文

再帰クエリは、WITH句とRECURSIVEキーワードを使用して定義されます。一般的な構文は以下の通りです。

WITH RECURSIVE クエリ名 (列名1, 列名2, ...)
AS (
  初期クエリ
  UNION [ALL]
  再帰部
)
SELECT 列名1, 列名2, ...
FROM クエリ名;

使用例

従業員テーブル（employees）が部門上司の階層関係を持つとします。再帰クエリを使用して、指定した部門以下のすべての部下を取得する処理を再帰クエリで作ると下記の様な形になります。

WITH RECURSIVE subordinates (id, name, manager_id, depth)
AS (
  SELECT id, name, manager_id, 0
  FROM employees
  WHERE id = :managerId -- 上司のIDを指定　任意のIDを指定してください。
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT id, name, depth
FROM subordinates;

subordinatesという再帰クエリを定義しています。初期クエリ部分では、指定した上司のIDを持つ従業員の情報を取得します。再帰部分では、従業員テーブル（employees）を自己結合し、従業員の部下を再帰的に取得します。再帰部分では、前のレベルの結果セット（subordinates）と結合することで、階層的なデータを生成します。再帰部分はUNION ALLキーワードを使用して定義されており、再帰的に繰り返されます。最後に、最終結果をSELECT文で取得します。

階層データの表示

部門テーブルが親部門IDを持つ階層的な構造を持つ場合、再帰クエリを使用して全階層を表示することが可能です。

WITH RECURSIVE hierarchy (id, name, parent_id, level)
AS (
  SELECT id, name, parent_id, 0
  FROM departments
  WHERE id = :parentId -- 親部門のIDを指定　任意のIDを指定してください。
  UNION ALL
  SELECT d.id, d.name, d.parent_id, h.level + 1
  FROM departments d
  INNER JOIN hierarchy h ON d.parent_id = h.id
)
SELECT id, name, level
FROM hierarchy;

上記の例では、departmentsテーブルが親部門IDを持つ階層構造を持つと仮定しています。再帰クエリを使用して、指定した親部門以下のすべての部門とその階層レベルを取得しています。

階層データを作成するクエリは、例えば製造業だと、BOM表（構成部品表）などを取得する場合や、WEBページだとカテゴリ階層の表示、ネットワーク階層の表示など利用できる幅はとても広いです。

注意点

再帰クエリを使用する際には、いくつかの注意点があります。

再帰クエリはデータベースのパフォーマンスに影響を与える可能性があるため、注意が必要です。大規模なデータセットや深い階層を持つ場合、クエリの実行時間が増加する可能性があります。
再帰クエリは適切な終了条件を持たない場合、無限ループに陥る可能性があります。再帰部分のクエリで終了条件を指定することが重要です。
再帰クエリはデータベースによってサポートされているか確認する必要があります

再帰クエリは階層的なデータを取得するための強力なツールであり、SQLの柔軟性を高める機能の一つですが慣れるまで扱いが非常に難しいので、じっくり学習してください。

ウィンドウ関数

ウィンドウ関数は、SQLの機能の一つであり、集約関数を使用しながら複数の行に対して計算や分析を行うための強力なツールです。ウィンドウ関数は、特定の範囲（ウィンドウ）内の行に対して値を計算するために使用されます。

構文

<ウィンドウ関数>([引数]) OVER (PARTITION BY 列1 [, 列2, ...] 
  ORDER BY 列1 [, 列2, ...] [ASC | DESC] [ROWS BETWEEN <start> AND <end>])

<ウィンドウ関数>: 使用するウィンドウ関数の名前（例: SUM、AVG、RANKなど）
[引数]: ウィンドウ関数に渡される引数（オプション）
OVER: ウィンドウ関数の範囲を指定するためのキーワード
PARTITION BY: ウィンドウをグループ化するための列を指定します。この列に基づいてウィンドウが分割されます（オプション）
ORDER BY: ウィンドウ内の行の順序を指定します（オプション）
ASC | DESC: ソート順を昇順（ASC）または降順（DESC）で指定します（オプション）
ROWS BETWEEN <start> AND <end>: ウィンドウ内の行の範囲を指定します。<start>と<end>は、UNBOUNDED PRECEDING、CURRENT ROW、UNBOUNDED FOLLOWINGなどのキーワードを使用して指定します（オプション）

ウィンドウ関数は、SELECT文内の列リストの一部として使用されます。ウィンドウ関数は通常、他の列と一緒に使用され、結果セットに新しい列を追加します。ウィンドウ関数を使用すると、集約関数（SUM、AVG、COUNTなど）を使用しながら個々の行に対して計算を行うことができます。ウィンドウ関数は、特にデータのランキング、集計、パーセンタイルの計算などの分析処理に役立ちます。

使用例

SELECT id, name, 
ROW_NUMBER() OVER (ORDER BY id) AS row_number
FROM table_name;

table_nameというテーブルからidとnameの列を取得し、ROW_NUMBER()ウィンドウ関数を使用して行番号を付与しています。ORDER BY句でid列を基準に昇順でソートしています。

SELECT date, 
sales, SUM(sales) OVER () AS total_sales
FROM sales_table;

sales_tableというテーブルから日付（date）と売上（sales）の列を取得し、SUM()ウィンドウ関数を使用して全体の売上合計を算出しています。OVER ()を使用して、全体の範囲で合計を計算しています。

SELECT name, score, 
RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank
FROM students;

studentsというテーブルから生徒の名前（name）、点数（score）、科目（subject）の列を取得し、RANK()ウィンドウ関数を使用して科目ごとに点数の降順でランキングを付与しています。PARTITION BY句を使用して科目ごとにグループ化し、ORDER BY句で点数を降順にソートしています。

SELECT date, sales, 
AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM sales_table;

sales_tableというテーブルから日付（date）と売上（sales）の列を取得し、AVG()ウィンドウ関数を使用して直近3日間の移動平均を計算しています。ORDER BY句で日付を基準に昇順でソートし、ROWS BETWEEN 2 PRECEDING AND CURRENT ROWを使用して直近2つの行と現在の行を範囲として平均を計算しています。

注意点

ウィンドウ関数は、通常の集約関数とは異なり、行の順序やグループ化の制御を行うために使用されます。
ウィンドウ関数は、より高度な分析や集計を行う際に非常に強力なツールです。
各データベース管理システムに固有のウィンドウ関数が存在する場合があり、構文やサポートされている関数には違いがあることに注意してください。

木構造 (データ構造)

SQLにおける木構造の実装について説明します。

ツリーテーブルの作成

まず、ツリー構造を表現するためのテーブルを作成します。テーブルには以下のような列が含まれます。

CREATE TABLE tree (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES tree(id)
);

treeというテーブルを作成し、id列、name列、parent_id列を持っています。idはノードの一意の識別子として使用され、nameはノードの名前を表します。また、parent_idは親ノードへの参照を表し、外部キー制約が設定されています。

ツリーデータの挿入

作成したテーブルにツリーデータを挿入します。

INSERT INTO tree (id, name, parent_id)
VALUES
  (1, 'Root', NULL),
  (2, 'Child 1', 1),
  (3, 'Child 2', 1),
  (4, 'Grandchild 1', 2),
  (5, 'Grandchild 2', 2),
  (6, 'Grandchild 3', 3);

treeテーブルにツリーデータを挿入しています。各行はツリーノードを表し、id列で識別されます。parent_id列には親ノードのidが指定されています。

ツリー構造のクエリ

ツリー構造のクエリを実行することで、特定のノードやその子孫ノードを取得できます。

すべてのノードを取得するクエリ

SELECT * FROM tree;

特定のノードの子ノードを取得するクエリ

SELECT * FROM tree WHERE parent_id = {親ノードのID};

特定のノードとそのすべての子孫ノードを再帰的に取得するクエリ

WITH RECURSIVE tree_recursive AS (
  SELECT * FROM tree WHERE id = {ノードのID}
  UNION ALL
  SELECT t.* FROM tree_recursive tr JOIN tree t ON tr.id = t.parent_id
)
SELECT * FROM tree_recursive;

上記の例では、再帰的なCTE（Common Table Expression）を使用して、特定のノードとそのすべての子孫ノードを取得しています。

注意点

ツリー構造を表現するためには、適切な親子関係を設定する必要があります。
ツリーのルートノードは通常、parent_id列にNULLを設定して表現されます。
ノードの追加や削除、親子関係の変更などを行う場合は、データの整合性を保つためにトランザクションを使用することが重要です。
ツリーが大きくなると、再帰的なクエリや階層的な操作が性能の面で影響を及ぼす場合があります。このような場合は、適切なインデックスの設計や最適化技術の使用が必要になる場合があります。

木構造の実用例

簡単な実用例として、組織図を表現するツリーテーブルを作成してみましょう。以下の例では、従業員のID、名前、役職、上司のIDを持つemployees2というテーブルを使用します。

CREATE TABLE employees2 (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  position VARCHAR(50),
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees2(id)
);

これにより、employees2テーブルは従業員の情報を格納するための基本的なテーブル構造が作成されます。

次に、組織図を表現するためにデータを挿入します。

INSERT INTO employees2 (id, name, position, manager_id) VALUES
(1, 'John Doe', 'CEO', NULL),
(2, 'Jane Smith', 'CTO', 1),
(3, 'Mark Johnson', 'Manager', 2),
(4, 'Emily Davis', 'Manager', 2),
(5, 'Michael Brown', 'Developer', 3),
(6, 'Jessica Wilson', 'Developer', 3),
(7, 'David Miller', 'Developer', 4);

上記のデータは、CEO（id: 1）をルートノードとし、CEOの直下にCTO（id: 2）があり、その下に2人のマネージャー（id: 3と4）が存在し、さらにその下に開発者（id: 5, 6, 7）が所属しているという構造です。

これで、組織図を表現するツリーテーブルが作成されました。このテーブルでは以下のようなクエリを実行することができます。

特定の従業員の直属の部下を取得するクエリ

SELECT id, name, position
FROM employees2
WHERE manager_id = 2;

このクエリは、CTO（id: 2）の直属の部下を取得します。

特定の従業員の部下全員を再帰的に取得するクエリ

WITH RECURSIVE subordinates AS (
  SELECT id, name, position
  FROM employees2
  WHERE id = 2 -- 開始点の従業員IDを指定
  UNION ALL
  SELECT e.id, e.name, e.position
  FROM employees2 e
  INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT id, name, position
FROM subordinates;

このクエリは、CTO（id: 2）の部下全員を再帰的に取得します。

ツリーテーブルを使用する際にはデータの整合性を保つために注意が必要です。従業員の上司のIDは存在する従業員のIDと一致している必要があります。また、無限ループや循環参照を避けるためにも、適切なデータ制約や制御ロジックを導入する必要があります。

ツリーテーブルを使用することで、組織図やカテゴリ階層などの階層的なデータ構造を効果的に表現し、クエリや分析の柔軟性を高めることができます。ただし、データの整合性やパフォーマンスに配慮しながら、適切なインデックスやクエリの最適化などを行う必要があります。