【SQL】SQL入門（DML編）
ここではSQLの中でも最も使用頻度の高いDMLについて解説します。
ここからは先のSQL（導入・DBMS概要・DDL）の記事で作成した、ユーザー「testuser」、データベース「testdb」に接続している状態と仮定して学習を進めます。
DDLの個所で作成した「items」テーブルを使用していきます。
前提知識・前提条件については下記の記事を参照ください。
コマンドプロンプト（あるいはターミナルなど）からの接続は以下のコマンドで接続できます。
psql -U testuser -d testdb
DMLはData Manipulation Languageの略です。
DDLは、データベースのオブジェクトに対する操作の構文でした。
DMLは、レコード(テーブルのデータ)に対する操作の構文です。
DDL, DML, DCLの3つの中で、最も使用頻度が高いのがDMLです。
DMLには大きく4つの構文があります。
- INSERT：データの挿入
- SELECT：データの取得
- UPDATE：データの更新
- DELETE：データの削除
この4つの中で最も使用頻度が高いのはSELECT文です。
そして、SELECT文を使いこなすことができれば、他の3つはそれほど難しくはありません。
なのでSELECTに重点をおいて解説していきます。
目次
- INSERT
- DELETE
- UPDATE
- SELECT
- GROUP BY
- 関数
- JOIN
- 集合演算
INSERT
INSERT文はテーブルにレコード(データ)を挿入するための構文です。
構文
-- 一般的なインサート文 INSERT INTO テーブル名(カラム1, …) VALUES (値1, …); -- 全カラムに値をセットするときはカラムのリストは省略可 INSERT INTO テーブル名 VALUES (値1, …); -- まとめてデータを入れることも可能 -- ただしDBMS製品によってはできない INSERT INTO テーブル名 VALUES (値1, …), (値1, …), ...; -- SELECTの実行結果をそのままインサートもできる INSERT INTO テーブル名 (カラム1, …) SELECT句;
サンプル
-- 一般的なインサート文 INSERT INTO items(id, item_name, price) VALUES(1, 'apple', 100); -- 全カラムに値をセットするときはカラムのリストは省略可 INSERT INTO items VALUES(2, 'orange', 200); -- まとめてデータを入れる INSERT INTO items VALUES(3, 'banana', 300), (4, 'grape', 400); -- レコードが挿入されていることの確認 SELECT * FROM items;
DELETE
DELETE文はレコードを削除するための構文です。
WHERE句による絞り込み条件についてはSELECT文で学習します。
構文
-- 一般的なデリート文 -- 条件を絞り込まないと全件削除されるので注意 DELETE FROM テーブル名 WHERE 絞り込み条件;
サンプル
-- idが1のレコードを削除する DELETE from items WHERE id = 1; -- レコードが削除されていることの確認 SELECT * FROM items; -- 元に戻しておく INSERT INTO items(id, item_name, price) VALUES(1, 'apple', 100);
UPDATE
UPDATE文は既存のレコードの値を更新するための構文です。
構文
-- 一般的なUPDATE文 UPDATE テーブル名 SET カラム1 = 値1 , カラム2 = 値2 … WHERE 絞り込み条件; -- Oracleで使用できる構文 UPDATE テーブル名 SET (カラム1, カラム2, …) = (値1, 値2, …) … WHERE 絞り込み条件;
サンプル
-- idが1のレコードの価格を+100する UPDATE items SET price = price + 100 WHERE id = 1; -- 更新されたことを確認 SELECT * FROM items; -- 元に戻す UPDATE items SET price = price - 100 WHERE id = 1;
SELECT
SELECT文はテーブルからレコードを取得するための構文です。
SELECT文で使用できる構文には様々なものがあるので、詳しく紹介していきます。
全カラムを取得
まず、レコードを取得するには、SELECT句で欲しいカラムの情報を書き、その後、FROM句でほしいレコードが格納されているテーブルを指定します。
テーブルの全カラムの情報を取得する場合は「*」を使用します。
構文
-- テーブルの全カラム・全レコードを取得 SELECT * FROM テーブル名;
サンプル
SELECT * FROM items;
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
| 2 | orange | 200 |
| 3 | banana | 300 |
| 4 | grape | 400 |
カラムの指定
特定のカラムの情報だけが欲しい場合は、カンマ区切りでカラムを指定します。
構文
-- カラムを指定してレコードを取得 SELECT カラム1, カラム2, .... FROM テーブル名;
サンプル
-- カラムを指定してレコードを取得 -- SELECT カラム1, カラム2, .... FROM テーブル名; SELECT item_name, price FROM items;
結果
| item_name | price |
|---|---|
| apple | 100 |
| orange | 200 |
| banana | 300 |
| grape | 400 |
レコードの絞り込み
レコードを絞り込みたい場合は、WHERE句を指定します。
構文
-- レコードを絞り込む SELECT * FROM テーブル名 WHERE 絞り込み条件
サンプル
-- idが1のレコードを取得 SELECT * FROM items WHERE id = 1;
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
AND
複数の条件を指定して絞り込みたい場合は、ANDを使用します。
サンプル
-- idが1かつitem_nameがapple SELECT * FROM items WHERE id = 1 AND item_name = 'apple';
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
OR
複数の条件を指定して、いずれかの条件を満たす場合のレコードを対象にしたい場合は、ORを使用します。
サンプル
-- idが1 または item_nameがorange SELECT * FROM items WHERE id = 1 OR item_name = 'orange';
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
| 2 | orange | 200 |
IN
同じカラムに対して、ORで複数の値を指定したい場合は、INを使うことで簡潔に書くことができます。
-- idが1 または 3 SELECT * FROM items WHERE id IN (1, 3); -- WHERE id = 1 OR id = 3; と書いても同じ
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
| 3 | banana | 300 |
BETWEEN
指定したカラムに対して、特定の範囲で絞り込みたい場合は、BETWEENを使用します。
-- idが1から3 SELECT * FROM items WHERE id BETWEEN 1 AND 3; -- WHERE id >= 1 AND id <= 3; -- とするのと同じ
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
| 2 | orange | 200 |
| 3 | banana | 300 |
LIKE
部分一致による検索、あいまい検索を行いたい場合は、LIKEを使用します。
任意の文字を指定する場合は、「%」を使用します。
任意の1文字を指定する場合は、「_」を使用します。
-- 部分一致 -- 最初がaから始まる SELECT * FROM items WHERE item_name LIKE 'a%'
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
-- 部分一致 -- 最後がaで終わる SELECT * FROM items WHERE item_name LIKE '%a'
結果
| id | item_name | price |
|---|---|---|
| 3 | banana | 300 |
-- 部分一致 -- item_nameにpが含まれるレコード SELECT * FROM items WHERE item_name LIKE '%p%'
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
| 4 | grape | 400 |
エイリアス
エイリアスは別名という意味です。
エイリアスはカラムやテーブルに対して別名を付けることができる機能です。
カラムにエイリアスを付けることで、結果のカラム名を別名に変更することができます。
-- ASはつけてもつけなくてもよい SELECT item_name AS 名前, price 値段 FROM items
結果
| 名前 | 値段 |
|---|---|
| apple | 100 |
| orange | 200 |
| banana | 300 |
| grape | 400 |
テーブルにエイリアスを付ける方法は、JOIN(結合)の説明の部分で使用します。
演算
SQLでは数値の演算や文字列の結合を行うこともできます。
また、SQLはFROM句がなくても実行することができる(ただしDBMSによってはダミーテーブルの指定が必要な場合もある)ので、合わせて覚えておきましょう。
SELECT 1 -- 直接値を指定 , 1 + 2 加算 , 5 - 2 減算 , 3 * 3 乗算 , 10 / 3 除算 , 10 % 3 剰余 , 'abc' || 'def' 文字列結合
結果
| 1 | 加算 | 減算 | 乗算 | 除算 | 剰余 | 文字列結合 |
|---|---|---|---|---|---|---|
| 1 | 3 | 3 | 9 | 3 | 1 | abcdef |
比較演算子
今までWHERE句での条件指定では、「=」を使って値が等しい場合のみを扱ってきましたが、それ以外の比較演算子もあります。
SELECT * FROM items WHERE id = 1 -- 等しい AND id <> 1 -- 等しくない AND id != 1 -- 等しくない AND price >= 500 -- 以上 AND price > 500 -- 大きい AND price <= 500 -- 以下 AND price < 500 -- より小さい AND item_name IS NULL -- null判定の場合はisを使用する AND item_name IS NOT NULL -- 否定の場合はNOTを付ける
NULLに対しては「=」や「<>」での比較ができないので注意してください。
ORDER BY
ORDER BY ではレコードの並び替えを行います。
並び替えの基準としたいカラムを指定し、ASC(昇順)かDESC(降順)かを指定します。
ASCは省略可能です。
また、複数のカラムを指定することも可能です。
その場合、先に書いたカラムの方が優先順位が高くなります。
構文
SELECT句 FROM句 WHERE句 ORDER BY column1 (ASC|DESC), column2 (ASC|DESC),…;
サンプル
SELECT * FROM items ORDER BY item_name ASC; -- 名前の昇順 ASCは省略可
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
| 3 | banana | 300 |
| 4 | grape | 400 |
| 2 | orange | 200 |
item_nameの昇順に並びます
SELECT * FROM items ORDER BY price DESC; -- 値段の降順
結果
| id | item_name | price |
|---|---|---|
| 4 | grape | 400 |
| 3 | banana | 300 |
| 2 | orange | 200 |
| 1 | apple | 100 |
priceの降順に並びます
DISTINCT
DISTINCTは取得結果から重複を排除するための構文です。
DISTINCTの結果をみるために以下のSQL文でサンプルデータを作成しておいてください。
create table ex_distinct as select '2018/9/1' sales_date, '顧客A' customer_name, '商品①' item_name, 10 quantity, 1000 amount union select '2018/9/1', '顧客B', '商品①', 20, 4000 union select '2018/9/2', '顧客A', '商品③', 10, 3000 union select '2018/9/2', '顧客B', '商品①', 5, 500 union select '2018/9/2', '顧客C', '商品③', 5, 1500 union select '2018/9/3', '顧客C', '商品③', 2, 600 union select '2018/9/4', '顧客A', '商品①', 5, 500 union select '2018/9/4', '顧客B', '商品②', 10, 2000 union select '2018/9/5', '顧客A', '商品②', 3, 600;
構文
SELECT DISTINCT column1, column2, … FROM table1
サンプル
SELECT DISTINCT customer_name, item_name FROM ex_distinct;
結果
| customer_name | item_name |
|---|---|
| 顧客A | 商品① |
| 顧客A | 商品② |
| 顧客A | 商品③ |
| 顧客B | 商品① |
| 顧客B | 商品② |
| 顧客C | 商品③ |
LIMITとOFFSET
LIMITはレコードの取得件数を制限したい場合に使用します。
合わせてOFFSETを指定すると、読み飛ばす件数も指定することができます。
構文
SELECT句
FROM句
WHERE句
ORDER BY句
LIMIT 取得件数
OFFSET 読み飛ばす件数
サンプル
SELECT * FROM items ORDER BY id LIMIT 2; -- 上位に2件を取得
結果
| id | item_name | price |
|---|---|---|
| 1 | apple | 100 |
| 2 | orange | 200 |
SELECT * FROM items ORDER BY id LIMIT 2 -- 2件を取得 OFFSET 2; -- 最初の2件を読み飛ばす
結果
| id | item_name | price |
|---|---|---|
| 3 | banana | 300 |
| 4 | grape | 400 |
GROUP BY
GROUP BY は特定のカラムで集約をすることで、集計したり、最大や最小を特定したりすることができる構文です。
例えば、学生のデータが登録されているテーブルがあった時に、性別ごとの人数を集計したり、誕生月ごとの人数を集計したり、性別ごとのテストの合計点や平均点、最大得点や最小得点などを求めたい場合などに使用します。
GROUP BY の結果を見るために、こちらのSQL文にてサンプルデータを作成しておいてください。
create table ex_group as select '2018/9/1' sales_date, '商品A' item_name, 300 price, 10 quantity union select '2018/9/2' , '商品A' , 300 , 5 union select '2018/9/3' , '商品A' , 300 , 20 union select '2018/9/4' , '商品B' , 400 , 8 union select '2018/9/5' , '商品B' , 400 , 5 union select '2018/9/6' , '商品C' , 500 , 5 union select '2018/9/7' , '商品C' , 500 , 10 union select '2018/9/8' , '商品C' , 500 , 8 union select '2018/9/9' , '商品C' , 500 , 7 union select '2018/9/10' , '商品C' , 500 , 10 ;
構文
SELECT column1, SUM(column2), COUNT(*), ... -- 集約関数が使用可能 FROM table1 GROUP BY column1 -- 集約の対象となるカラム HAVING SUM(column2) > 10000; -- 集約結果を使った絞り込み条件
SELECT item_name -- カラムを直接指定できるのはgroup by で指定したカラムのみ , COUNT(*) count -- レコード件数 , SUM(quantity) sum_quantity -- 数量の合計 , SUM(price * quantity) total -- 金額の合計 , MAX(quantity) max_quantity -- 数量の最大値 , MIN(quantity) min_quantity -- 数量の最小値 , MAX(sales_date) new_sales_date -- 日付の最大(最新) FROM ex_group GROUP BY item_name ORDER BY item_name;
結果
| item_name | count | sum_quantity | total | max_quantity | min_quantity | new_sales_date |
|---|---|---|---|---|---|---|
| 商品A | 3 | 35 | 10500 | 20 | 5 | 2018/9/3 |
| 商品B | 2 | 13 | 5200 | 8 | 5 | 2018/9/5 |
| 商品C | 5 | 40 | 20000 | 10 | 5 | 2018/9/9 |
軽く解説
GTOUP BY を使用すると、SELECT句の中で直接カラムを指定できるのは、GROUP BY で指定したカラムのみになります。
GROUP BY で指定したカラム以外では、集約関数しか指定できません。
以下、よく使用される集約関数です。
- COUNT：レコード数を算出
- SUM：合計値を算出
- MAX：最大値を算出。数値以外も指定可能
- MIN：最小値を算出。数値以外も指定可能
- AVG：平均を算出
COUNT関数の場合、引数には*を指定することが多いですが、カラムを指定することもできます。
カラムを指定した場合、値がNULLの場合はカウント対象にならないので注意してください。
SUMとAVG関数は、引数は数値のカラムしか指定できません。
MINやMAX関数は、数値以外(文字列や日時)も指定可能です。
HAVING
GROUP BY句の後にHAVING句を指定することで、集約関数の結果で絞り込むことができます。
SELECT item_name , COUNT(*) count , SUM(quantity) sum_quantity , SUM(price * quantity) total , MAX(quantity) max_quantity , MIN(quantity) min_quantity , MAX(sales_date) new_sales_date FROM ex_group GROUP BY item_name HAVING COUNT(*) >= 5 -- レコード件数が5件以上のみ対象 ORDER BY item_name;
結果
| item_name | count | sum_quantity | total | max_quantity | min_quantity | new_sales_date |
|---|---|---|---|---|---|---|
| 商品C | 5 | 40 | 20000 | 10 | 5 | 2018/9/9 |
関数
関数とは、引数で値を受け取り、戻り値で値を返す処理のことです。
一般的なプログラミング言語における関数と同じです。
GROUP BY のところで、SUMやCOUNTなどの関数を使用しました。
それらの関数は、集約関数、集計関数などと呼ばれ、複数のレコードを1つのレコードにまとめる機能を持った関数です。
ここでの関数は、集約関数とは違い、一つの値を別の一つの値に処理するものです。
文字列に関する関数
SELECT trim(' abc ') f1 -- 空白を取り除く , substr('abcdef', 2, 3) f2 -- 部分文字列 , char_length('abcde') f3 -- 文字列の長さ , lower('HELLO') f4 -- 小文字に変換 , upper('world') f5; -- 大文字に変換
結果
| f1 | f2 | f3 | f4 | f5 |
|---|---|---|---|---|
| abc | bcd | 5 | hello | WORLD |
数値に関する関数
SELECT floor(10.8) f1 -- 引数よりも小さい、最大の整数 , ceil(10.8) f2 -- 引数よりも大きい、最大の整数 , random() f3 -- 0~1のランダムな値 , round(10.5) f4 -- 四捨五入 , trunc(10.9) f5; -- 切り捨て
結果
| f1 | f2 | f3 | f4 | f5 |
|---|---|---|---|---|
| 10 | 11 | 0.917... | 11 | 10 |
ここで使用した関数は、PostgreSQLの環境を前提にした関数です。
DBMSによっては使用できないものや、同じ機能でも名前が異なったりします。
また、ここで紹介した以外にも様々な関数があるので、詳しくはマニュアルなどを参照して調べてみてください。
JOIN(結合)
ここではJOIN(結合)について学習していきます。
結合では、複数のテーブルのデータを結合し、まとめて取得することができます。
結合にはいくつかの種類があります。
- クロス結合
- 内部結合
- 外部結合
- 左外部結合
- 右外部結合
- 全外部結合
- 自己結合
- 自然結合
色々ありますがよく使用されるのは内部結合と外部結合です。
一つずつ見ていきます。
クロス結合
クロス結合のサンプルを見るために、こちらのSQL文にてテストデータを作成しておいてください。
create table ex_pg_language as select 'PHP' pg_language union select 'Java' union select 'Ruby'; create table ex_dbms as select 'Oracle' dbms_name union select 'MySQL' union select 'PostgreSQL';
クロス結合の書き方は2つあります。
構文
-- 書き方1 SELECT * FROM table1 CROSS JOIN table2; -- 書き方2 SELECT * FROM table1, table2
そもそもクロス結合自体、使用する頻度は少ないと思うけれど、2の書き方は分かりにくいので、1の書き方がおすすめ。
サンプル
SELECT * FROM ex_pg_language CROSS JOIN ex_dbms; -- クロス結合
結果
| pg_language | dbms_name |
|---|---|
| PHP | Oracle |
| PHP | MySQL |
| PHP | PostgreSQL |
| Java | Oracle |
| Java | MySQL |
| Java | PostgreSQL |
| Ruby | Oracle |
| Ruby | MySQL |
| Ruby | PostgreSQL |
結果から分かる通り、クロス結合はそれぞれのレコードの組み合わせが全て取得される結合です。
レコード数の多いテーブル同士てクロス結合すると、大きな負荷がかかるため、注意が必要です。
内部結合
内部結合のサンプルを見るために、こちらのSQL文にてテストデータを作成しておいてください。
create table ex_items as select 1 id , 'りんご' item_name union select 2 ,'バナナ' union select 3 ,'オレンジ' union select 4 ,'ぶどう' union select 5 ,'いちご' union select 6 ,'メロン'; create table ex_item_price as select 1 id, 100 price union select 2 ,160 union select 3 ,80 union select 7 ,300 union select 8 ,200 union select 9 ,600;
内部結合も2つの書き方があります。
構文
-- 書き方1 SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.column = t2.column -- 書き方2 SELECT * FROM table1 t1, table2 t2 WHERE t1.column = t2.column
おすすめの書き方は1の書き方です。
2の書き方の場合、絞り込み条件と結合条件がごっちゃになって分かりにくくなるため、1の書き方がオススメです。
1の書き方の場合、INNERは省略することも可能です。
また、結合の条件が複数ある場合は、ANDで続けて書くこともできます。
サンプル
SELECT item.id, item_name, price FROM ex_items item -- テーブル名にエイリアスを付ける INNER JOIN ex_item_price ex_price ON item.id = ex_price.id ORDER BY item.id;
結果
| id | item_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
| 3 | オレンジ | 80 |
結果から分かる通り、内部結合では、結合対象となるテーブルの両方にデータが存在するレコードのみが取得対象になります。
外部結合
外部結合3つに分かれます。
- 左外部結合
- 右外部結合
- 全外部結合
の3つです。
左外部結合と右外部結合は実質ほぼ同じです。
サンプルデータは内部結合のものと同じものを使用します。
以下はそれぞれの書き方です。
構文
-- 書き方（左外部結合） SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.column = t2.column -- 書き方（右外部結合） SELECT * FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.column = t2.column -- 書き方（全外部結合） SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.column = t2.column
OUTERの部分は省略可能です。
全外部結合についてはDBMSによってはさぽーとされていないものもあるので注意が必要です。
左外部結合
サンプル
-- 左外部結合 SELECT i.id, item_name, price FROM ex_items i LEFT OUTER JOIN ex_item_price p ON i.id = p.id ORDER BY i.id;
結果
| id | item_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
| 3 | オレンジ | 80 |
| 4 | ぶどう | NULL |
| 5 | いちご | NULL |
| 6 | メロン | NULL |
左外部結合では、FROMで指定したテーブルのレコードは全て取得されます。
(WHERE句での絞り込みは行われる)
結合対象のテーブルで該当するレコードがない場合は、NULLとして表示されます。
条件指定の書く場所の違い
外部結合では、絞り込みを行うときに、結合条件の時点で絞り込むのか、結合後に条件を絞り込むのかで結果が変わってきます。
-- 結合条件での絞り込み SELECT i.id, item_name, price FROM ex_items i LEFT OUTER JOIN ex_item_price p ON i.id = p.id AND p.price >= 100 ORDER BY i.id;
結果
| id | item_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
| 4 | ぶどう | NULL |
| 5 | いちご | NULL |
| 6 | メロン | NULL |
-- 結合後の条件での絞り込み SELECT i.id, item_name, price FROM ex_items i LEFT OUTER JOIN ex_item_price p ON i.id = p.id WHERE p.price >= 100 ORDER BY i.id;
結果
| id | item_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
それぞれの違いを理解して適切に使いこなせるようにしておきましょう。
右外部結合
右外部結合は、左外部結合の逆です。
本質的には違いはありません。
SELECT p.id, item_name, price FROM ex_items i RIGHT OUTER JOIN ex_item_price p ON i.id = p.id ORDER BY p.id;
結果
| id | item_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
| 3 | オレンジ | 80 |
| 7 | NULL | 300 |
| 8 | NULL | 200 |
| 9 | NULL | 600 |
全外部結合
全外部結合は、左外部結合と右外部結合を合わせたものです。
DBMSによってはサポートされていない場合もあるので注意してください。
SELECT i.id id1, p.id id2, item_name, price FROM ex_items i FULL OUTER JOIN ex_item_price p ON i.id = p.id ORDER BY i.id, p.id;
結果
| id | item_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
| 3 | オレンジ | 80 |
| 4 | ぶどう | NULL |
| 5 | いちご | NULL |
| 6 | メロン | NULL |
| 7 | NULL | 300 |
| 8 | NULL | 200 |
| 9 | NULL | 600 |
自己結合
自己結合は、結合対象のテーブルが自分自身となっているもので、動作としては内部結合と変わりません。
-- 自己結合 SELECT * FROM table1 t1 INNER JOIN table1 t1 ON t1.column = t1.column
自然結合
自然結合は、結合条件を指定せずに、同じ名前のカラムで自動的に結合してくれる結合方法です。
こちらも、内部結合で表現することも可能なので、内部結合を理解していれば、重要度は下げても構いません。
-- 自然結合 SELECT * FROM table1 NATURAL JOIN table2 -- 内部結合での表現 SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id
集合演算
集合演算はSELECTの結果の表同士での演算のことです。
3つの種類があります。
- UNION
- EXCEPT
- INTERSECT
UNIONは足し算、EXCEPTは引き算、INTERSECTは掛け算を表します。
集合演算のサンプルを見るために、こちらのSQL文にてテストデータを作成しておいてください。
create table Astore_items as select 1 id, 'りんご' item_name, 100 price union select 2 , 'バナナ' , 160 union select 3 , 'オレンジ' , 80 union select 4 , 'ぶどう' , 300 union select 5 , 'いちご' , 200; create table Bstore_items as select 1 id, 'りんご' item_name, 100 price union select 2 , 'バナナ' , 160 union select 3 , 'オレンジ' , 90 union select 6 , 'メロン' , 600;
UNION
UNIONは、レコードの和集合を求める演算子です。
UNIONとUNION ALLが使用できます。
構文
SELECT句 UNION (ALL) SELECT句
まずはUNIONから
SELECT * FROM Astore_items UNION SELECT * FROM Bstore_items;
結果
| id | item_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
| 3 | オレンジ | 80 |
| 3 | オレンジ | 90 |
| 4 | ぶどう | 300 |
| 5 | いちご | 200 |
| 6 | メロン | 600 |
それぞれのSELECTの結果が合算されて、重複が削除されます。
続いてUNION ALLを見ていきます。
SELECT * FROM Astore_items UNION ALL SELECT * FROM Bstore_items;
結果
| id | item_name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
| 3 | オレンジ | 80 |
| 4 | ぶどう | 300 |
| 5 | いちご | 200 |
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
| 3 | オレンジ | 90 |
| 6 | メロン | 600 |
重複しているレコードもそのまま表示されます。
UNIONを私用した場合は、重複レコードは排除されますが、UNION ALLの場合は重複は排除されません。
ここで、並び順にも注意してください。
UNIONは、重複を排除するために、内部的にソートの処理が走ります。
そのため、それぞれの結果が混ざってソートされた形になっていますが、UNION ALLの場合は、重複を排除しないため、ソートがされません。
ソートの処理がない分、UNION ALL の方が処理は高速になります。
それぞれのSELECT文で重複がないことが明確になっており、かつ、並び順を気にしない場合は、UNION ALL を使用したほうが良いでしょう。
EXCEPT
EXCEPTは上の差分を求める演算子です。
書き方はUNIONの場合と同じで、UNIONの部分がEXCEPTに変わるだけです。
DBMSによっては、MINSになっている場合もあります。
SELECT * FROM Astore_items EXCEPT SELECT * FROM Bstore_items;
結果
| id | item_name | price |
|---|---|---|
| 3 | オレンジ | 80 |
| 4 | ぶどう | 300 |
| 5 | いちご | 200 |
UNIONと同じく、ALLを指定することもできます。
INTERSECT
INTERSECTは共通部分を求める演算子です。
SELECT * FROM Astore_items INTERSECT SELECT * FROM Bstore_items;
結果
| id | name | price |
|---|---|---|
| 1 | りんご | 100 |
| 2 | バナナ | 160 |
サブクエリ
サブクエリは日本語で副問い合わせとも呼ばれます。
SELECT文の結果を他のSQL文に使用するような書き方のことです。
SELECT文の中に別のSELECT文を書くようなイメージです。
サブクエリのサンプルを見るために、こちらのSQL文にてサンプルデータを作成しておいてください。
create table ex_item_sub as select '001' item_id, '商品A' item_name, 200 price union select '002' , '商品B' , 350 union select '003' , '商品C' , 170 union select '004' , '商品D' , 200 union select '005' , '商品E' , 300; create table ex_sales_sub as select '2018/9/1' sales_date,'001' item_id, 20 quantity union select '2018/9/2' ,'001' , 15 union select '2018/9/3' ,'002' , 40 union select '2018/9/4' ,'002' , 30 union select '2018/9/5' ,'003' , 50;
インラインビュー
SELECTの結果を一時的な表とみなして、結合やFROM句で使用するサブクエリです。
サブクエリの結果として使用される一時的な表をインラインビューと呼びます。
-- 例（販売数を商品で集約した結果を、結合に使用する） SELECT i.*, sum FROM ex_item_sub i LEFT OUTER JOIN ( -- サブクエリ(インラインビューとも呼ぶ) SELECT item_id, SUM(quantity) sum FROM ex_sales_sub GROUP BY item_id) s ON i.item_id = s.item_id ORDER BY i.item_id;
結果
| item_id | item_name | price | sum |
|---|---|---|---|
| 001 | 商品A | 200 | 35 |
| 002 | 商品B | 350 | 70 |
| 003 | 商品C | 170 | 50 |
| 004 | 商品D | 200 | NULL |
| 005 | 商品E | 300 | NULL |
相関サブクエリ
外側のクエリの値を内側のクエリ内で使用するようなサブクエリのことを相関サブクエリと呼びます。
主にEXISTSを使用する場合などのサブクエリです。
EXISTSは、中のSELECTの結果でレコードが存在する場合にTRUEになるような構文です。
-- 相関サブクエリ SELECT * FROM ex_item_sub i WHERE EXISTS (SELECT * FROM ex_sales_sub WHERE item_id = i.item_id AND sales_date BETWEEN '2018/9/1' AND '2018/9/3'); -- これも上と同じ結果になる -- 相関サブクエリではない SELECT * FROM ex_item_sub i WHERE EXISTS item_id IN (SELECT item_id FROM ex_sales_sub WHERE sales_date BETWEEN '2018/9/1' AND '2018/9/3')
結果
| item_id | item_name | price |
|---|---|---|
| 001 | 商品A | 200 |
| 002 | 商品B | 350 |
スカラサブクエリ
SELECT句の中でSELECT文を使用することもできます。
ただしこの場合、SELECTの実行結果は1レコードで、1つの値のみを取得することが可能なSELECT文に限ります。
-- スカラサブクエリ -- SELECT句の中でSELECT文を使用するサブクエリ -- 分かりやすいが速度は出にくいのでおすすめはしない -- 売上のレコードを取得して、商品名も表示する SELECT sales_date , item_id , (SELECT item_name FROM ex_item_sub i WHERE item_id = s.item_id) item_name , quantity FROM ex_sales_sub s;
結果
| sales_date | item_id | item_name | quantity |
|---|---|---|---|
| 2018/9/4 | 002 | 商品B | 30 |
| 2018/9/1 | 001 | 商品A | 20 |
| 2018/9/3 | 002 | 商品B | 40 |
| 2018/9/2 | 001 | 商品A | 15 |
| 2018/9/5 | 003 | 商品C | 50 |
CASE式(条件分岐)
CASEはSQL文の中で条件分岐を行うことができる構文です。
SELECT句、WHERE句、GROUP BY句のなど、どこでも使用可能です。
一般的にはWHERE句では使用せずにSELECT句の中で使用することが多いです。
maxや、sumなどの集約関数の中に入れ込むこともできます。
CASE式のサンプルを見るために、こちらのSQL文にてサンプルデータを作成しておいてください。
create table ex_case as select '001' id, '商品A' item_name, 1000 price union select '002' , '商品B' , 5000 union select '003' , '商品C' , 10000;
CASE式の書き方は、単純CASE式と検索CASE式の2つがあります。
-- 単純CASE式 -- 式の値がWHENで一致した値が評価される SELECT CASE 式 WHEN 値A THEN 値1 WHEN 値B THEN 値2 ELSE 値3 END エイリアス名 FROM table2 -- 検索CASE式 -- 条件式がtrueになった時の値が評価される SELECT CASE WHEN 条件式1 THEN 値1 WHEN 条件式2 THEN 値2 ELSE 値3 END エイリアス名 FROM table2
SELECT * , CASE WHEN price >= 10000 THEN '高級品' WHEN price BETWEEN 5000 AND 9999 THEN '普通' ELSE '安物' END rank FROM ex_case;
結果
| id | item_name | price | rank |
|---|---|---|---|
| 001 | 商品A | 1000 | 安物 |
| 002 | 商品B | 5000 | 普通 |
| 003 | 商品C | 10000 | 高級品 |
CASE式は、それ単体でも様々なことができて便利ですが、集約関数と組み合わせて使用することで更に使い道が広がります。
例えば、以下のようなテーブルがあったとします。
| 取引 | 金額 |
|---|---|
| 仕入 | 100 |
| 売上 | 200 |
| 仕入 | 300 |
| 売上 | 400 |
これを、取引ごとの合計を1レコードで取得して、以下のように表示したいとします。
| 仕入金額 | 売上金額 |
|---|---|
| 400 | 600 |
集計しているので、group by でできると一瞬思うかもしれませんが、group by ではそれぞれの集計結果が別レコードとして表示されるので、1レコードでまとめて取得することはできません。
この場合、以下のSQLで実現可能です。
SELECT sum(CASE WHEN 取引 = '仕入' THEN 金額 ELSE 0 END) 仕入金額 , sum(CASE WHEN 取引 = '売上' THEN 金額 ELSE 0 END) 売上金額 FROM 取引テーブル;
sum以外にも、maxなどの集約関数にも入れ込むことができます。
CASEと集約関数の組み合わせは協力なので、使いこなせるようになっておく良いでしょう。
ウィンドウ関数
GROUP BY を使わずとも集約関数の結果を取得できる特殊な関数です。
分析関数などとも呼ばれます。
row_number, count, sum, max, rank, dense_rank などがあります。
使いこなせると非常に便利ですが、DBMSやバージョンによっては実装されていないものもあるので注意が必要です。
関数名() over(…) のように使用します。overの中にはpartition by句とorder by句が記述できます。
ウィンドウ関数のサンプルを見るために、こちらのSQL文にてテストデータを作成しておいてください。
create table ex_sales_window as select '2018/9/1' sales_date, 'X商店' customer,'商品A' item_name, 200 price, 100 quantity union select '2018/9/1' ,'X商店' ,'商品B' , 300 , 50 union select '2018/9/1' ,'X商店' ,'商品C' , 500 , 10 union select '2018/9/2' ,'Y商店' ,'商品A' , 200 , 40 union select '2018/9/2' ,'Y商店' ,'商品B' , 300 , 150 union select '2018/9/3' ,'Z商店' ,'商品C' , 500 , 80;
-- 書き方 関数名() over(partition by カラムリスト order by カラムリスト)
SELECT * , row_number() over(ORDER BY sales_date, customer, item_name) 連番 -- 日付、顧客、商品の並びによる連番 , rank() over(ORDER BY price * quantity DESC) lank -- 売上金額のランク , sum(price * quantity) over(PARTITION BY sales_date ORDER BY item_name) 累計 , sum(price * quantity) over(PARTITION BY sales_date) 日付合計 FROM ex_sales_window;
結果
| sales_date | customer | item_name | price | quantity | 連番 | 売上ランク | 累計 | 日付合計 |
|---|---|---|---|---|---|---|---|---|
| 2018/9/1 | X商店 | 商品A | 200 | 100 | 1 | 3 | 20000 | 40000 |
| 2018/9/1 | X商店 | 商品B | 300 | 50 | 2 | 4 | 35000 | 40000 |
| 2018/9/1 | X商店 | 商品C | 500 | 10 | 3 | 6 | 40000 | 40000 |
| 2018/9/2 | Y商店 | 商品A | 200 | 40 | 4 | 5 | 8000 | 53000 |
| 2018/9/2 | Y商店 | 商品B | 300 | 150 | 5 | 1 | 53000 | 53000 |
| 2018/9/3 | Z商店 | 商品C | 500 | 80 | 6 | 2 | 40000 | 40000 |
WITH句
WITH句は、SELECT文の結果に名前を付けることができる構文です。
一つのSQL文が長くなってしまう場合に、SELECT文の途中経過に名前を付けることで、全体のSQL文の可読性を上げることができます。
-- 書き方 WITH 名前 AS (SELECT文)
-- 例 -- all_itemsをテーブルのように扱うことができる。 WITH all_items AS ( SELECT * FROM AStore_items UNION SELECT * FROM BStore_items ) SELECT * FROM all_items;
SELECTのまとめ
SELECT文で使用できる構文は色々あるので、慣れるまでは組み合わせて使用するのが難しいかもしれません。
様々な構文を組み合わせる場合は書く順番がある程度決まっています。
以下にSQLの書き方をまとめます。
-- 基本構文 SELECT カラム1, ... -- 取得したいカラムリスト FROM テーブル名 -- レコードの取得対象のテーブル JOIN句 結合対象テーブル -- 結合対象のテーブル ON 結合条件 -- 結合条件 WHERE 絞込条件1 ... -- レコードの絞り込み GROUP BY カラム1, ... -- 集約 HAVING 絞り込み条件 -- 集約後の絞り込み ORDER BY カラム1, ... -- 並び替え LIMIT 絞り込み件数 OFFSET 読み飛ばし件数 ;
この形さえ覚えれば基本的には大丈夫でしょう。
集合演算はサブクエリは、それぞれのSELECT句が上記のような形になっていると考えればよいでしょう。
SELECTの書く順番
SELECT文をすらすら書くにはコツがあります。
書くときの順番が重要です。
書く順番としては、
- FROM句（メインテーブル）
- JOIN句（結合対象のテーブル）
- WHERE句
- GROUP BY句
- HAVING句
- ORDER BY句
- SELECT句
とすると書きやすくなります。
SELECT句はSQLの構造的には最初にありますが、SQL文を組み立てる時には最後に書いた方が良いです。
まずはどのテーブルのどのレコードが必要かを書いていき、取得したいデータの加工は最後に書く。
これに慣れてくると、SQLがすらすら書けるようになってきます。
