PostgreSQL SQL構文:DML とは
DMLとはデータベース内のデータを操作するためのSQL文です。主に次の4項目があります。
SELECT
:データの取得INSERT
:データの挿入UPDATE
:データの更新DELETE
:データの削除
ここではSELECT文の基本について紹介します。
PostgreSQL SQL構文(DML):SELECT文
SELECT
文は、データベースからデータを取得するために使われるSQL文です。
SELECT文の基本構文
SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column ASC|DESC
LIMIT number OFFSET number;
SELECT
取得する列を指定します。
SELECT name, salary FROM employees;
employees テーブルから、name列とsalary列の値を取得します。
SELECT * FROM employees;
*を指定するとemployeesテーブルの全ての列の値を取得します。
WHERE
取得するデータの条件を指定します。
SELECT name FROM employees WHERE salary > 50000;
employees テーブルから、給与が5000より多い人の氏名(name)を取得します。
GROUP BY
指定した列で同じ値を持つ行をグループ化し、主に集計関数(COUNT
、SUM
、AVG
、MAX
、MIN
等)と共に使用します。
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
department(部署)
列でグループ化し、各部署の社員数を取得します。
HAVING
GROUP BY でグループ化した後の条件を指定します。
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;
各部署の給与の合計を算出し、その合計が10万を超える部署だけを取得します。
WHERE句とHAVINGの違い
WHERE
句はグループ化前のデータに対するフィルタリングに使われ、HAVING
句はグループ化後の集計結果に基づくフィルタリングに使われます。
ORDER BY ASC | DESC
指定した列のデータに従って、並び順を指定します。
SELECT name, salary FROM employees ORDER BY department ASC, salary DESC;
employees テーブルから、部署の昇順に給与の高い社員の氏名と給与を取得します。
LIMIT
取得するデータの行数を制限します。
SELECT name FROM employees ORDER BY name LIMIT 10;
employees テーブルから、氏名の昇順に10名の氏名を取得します。
OFFSET
SELECT
文の結果から指定された行数分をスキップし、その後の結果を返すために使用します。主にLIMIT
句と組み合わせて使用され、ページネーション(ページごとにデータを取得する)に役立ちます。
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 5;
給与の多い順にソートされた社員データから最初の5行をスキップし、その後の10行を取得します。
SELECT文のオプション
DISTINCT
値が重複する行を除外して、データを取得します。
SELECT DISTINCT department
FROM employees;
部署(department)には普通複数人が所属していますが、各部署名を一度だけ取得します。
JOIN
複数のテーブルを結合してデータを取得します。
- INNER JOIN:両方のテーブルで一致する行だけを取得します。
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
INNER JOIN departments d ON e.department_id = d.id;
employeesテーブルとdepartmentsテーブルを、employeesテーブルに存在する「department_id」カラムとdepartmentsテーブルに存在する「id」カラムをキーにして内部結合し、内部結合したキーの値あが一致する両方のテーブルから、データを取得しています。
- LEFT JOIN(LEFT OUTER JOIN):左側のテーブル (
table1
) のすべての行と、結合条件に一致する右側のテーブル (table2
) の行を取得します。右側に一致する行がない場合、その列にはNULL
が表示されます。
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;
LEFT JOIN departmentsON employees.department_id = departments.department_id;
employees.department_idカラム と departments.department_idカラムをLEFT JOINしています。
この場合、従業員(employees.name)が部門(departments.department_name)に所属していなくても、employees
テーブルのすべての行が結果に表示されます。部門がない場合、department_name
は NULL
になります。
- RIGHT JOIN (RIGHT OUTER JOIN):
RIGHT JOIN
は、右側のテーブル (table2
) のすべての行と、結合条件に一致する左側のテーブル (table1
) の行を取得します。左側に一致する行がない場合、その列にはNULL
が表示されます。
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
RIGHT JOIN departments ON employees.department_id = departments.department_id;
このクエリでは、departments
テーブルのすべての行が結果に表示されます。部門に従業員がいない場合、その部門の employees.name
は NULL
になります。
- FULL JOIN (FULL OUTER JOIN):左右のテーブルのすべての行を取得し、結合条件に一致する行を結合します。一致しない行は、それぞれのテーブルから
NULL
を埋め込んで結果に含めます。
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;
FULL JOIN departments ON employees.department_id = departments.department_id;
どちらのテーブルにも一致するデータがない場合は NULL
が結果に表示されます。
- CROSS JOIN:両方のテーブルのすべての行の組み合わせを生成します。条件は指定されず、直積 (Cartesian Product) を返します。
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;
FROM employees CROSS JOIN departments;
すべての従業員に対して、すべての部門との組み合わせを表示します。
- SELF JOIN:1つのテーブルを自分自身と結合します。これは、同じテーブル内のデータを比較したり、階層構造を表現するのに役立ちます。
SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.employee_id;
JOIN employees e2 ON e1.manager_id = e2.employee_id;
従業員テーブル(employees)に上司と部下の関係がある場合、従業員の名前とその上司の名前を表示します。
JOINのパフォーマンスに関する注意事項
大規模なデータセットに対して JOIN
を使用する際、パフォーマンスが重要となります。次のような最適化を検討する必要があります。
- インデックス
結合に使用される列にインデックスを付けることでパフォーマンスが向上します。 - フィルタリング
不要なデータを除外するために JOIN の前に適切な WHERE 句や ON 句を使用する事で速度が速くなります。
SELECT文のまとめ
- データベースからデータを取得するために使われるSQL文である。
- WHERE句やGROUP BY句、HAVING句等、取得する条件を絞り込む機能があり、
適切に組み合わせる事でパフォーマンスを向上させる事ができる。 - JOIN句をしようして複数のテーブルと結合して、条件にあったデータを複数のテーブルから取得できる。
- JOIN句を使用する場合は、インデックスを設定したり、あらかじめ条件を絞る等して、
パフォーマンスに考慮する。