SQL勉強記録3

https://qiita.com/Ryo_Kitamoto/items/abdd73a76aebf3f2b3e7#%E7%95%AA%E5%A4%96%E7%B7%A8%E3%82%A8%E3%83%A9%E3%83%BC%E8%A8%98%E9%8C%B2

https://qiita.com/Ryo_Kitamoto/items/9786e8af7ba79ebe56bf


以上の続き

## 結合とは
テーブルの結合の仕方として
１対１
１対多
多対多
の結合の仕方がある
JOINとは列方向に結合することで
以前やったUNIONは行方向の結合

### 内部結合：INNER JOINについて
接続する条件が正しいレコード**のみ**連結して取り出すSQL
紐付けができたものだけとりだす
```sql
INNER JOIN 接続するテーブル名　ON 接続する条件
```
```sql
SELECT * FROM employees;
SELECT * FROM departments;

-- 通常のINNER JOIN
SELECT * FROM employees AS em
INNER JOIN departments AS dt ON em.department_id=dt.id; -- ※１

-- 全て取り出すのではなく見たいカラムだけで取り出すこともできる
SELECT em.id,em.first_name,em.last_name,em.department_id,dt.name FROM employees AS em
INNER JOIN departments AS dt ON em.department_id=dt.id;

-- 複数のレコード(カラム条件)で紐づける
SELECT * FROM students;
SELECT * FROM users;

SELECT * FROM students AS std
INNER JOIN users AS usr ON std.first_name=usr.first_name AND std.last_name=usr.last_name; -- ONの条件をANDで複数持たせる

-- =以外で紐づける
SELECT * FROM employees AS em
INNER JOIN students AS std
ON em.id<std.id;
```
※1 このコードでdepartment_idが1〜４までのテーブル通しを連結させることができたが、実はemployeesテーブルにはdepartment_idが5の人も存在する。しかしdepartmentテーブルには1~4しかないため、ここでは表示されなくなってしまう。そこで次のLEFT JOINやRIGHT JOINにつながる

### 外部結合：LEFT JOIN, RIGHT JOIN
LEFT JOIN：左のテーブルはすべてのレコードを取得して、
右のテーブルからは**紐付けできたレコードのみ**取り出しそれ以外は**NULL**

左のテーブルとはLEFT JOINの前に書いたテーブルのこと
```sql
FROM 左のテーブル　LEFT JOIN 右のテーブル ON 接続する条件
```
RIGHT JOINはその逆で右のテーブルを全て取り出す。左のテーブルでないものはNULL。
だが実際RIGHT JOINが使われることはあんまりない。
```sql
-- LEFT JOIN (department_id=5の人はdt.nameはNULLで接続する)
SELECT em.id,em.first_name,em.last_name,em.department_id,dt.name FROM employees AS em
LEFT JOIN departments AS dt ON em.department_id=dt.id ORDER BY id;

-- もしNULLで入れるのが嫌ならCOALESCEを使うと値を入れられる
-- 思い出してみよう。COALESCEははじめに出てくるNULL以外の値を取り出す関数
SELECT em.id,em.first_name,em.last_name,em.department_id,COALESCE(dt.name,'該当なし') FROM employees AS em
LEFT JOIN departments AS dt ON em.department_id=dt.id ORDER BY id;


-- 多対多のテーブルを繋げる（中間テーブルを使う）
SELECT * FROM enrollments;
-- enrollmentsテーブルにはstudent_idとclass_idを持っている中間テーブルである
SELECT * FROM students AS std
LEFT JOIN enrollments AS enr ON std.id=enr.student_id 
LEFT JOIN classes AS cls ON enr.class_id=cls.id ORDER BY std.id;
-- enrollmentsテーブルを介すことによってstudentsテーブルとclassesテーブルを結合できた！
-- LEFT JOINなので結びつけられなかったカラムはstudentsテーブル以外はNULLになっていることもわかる

-- RIGHT JOIN
SELECT * FROM classes; -- id=10に監査論という授業がある
SELECT * FROM enrollments WHERE class_id =10; -- しかしそれをとっている生徒は誰もいないため
-- こちらで取り出すとclass_idで何も取れなくなるためstudentsとclassesテーブルはNULLのデータが取り出される
SELECT * FROM students AS std
RIGHT JOIN enrollments AS enr ON std.id=enr.student_id 
RIGHT JOIN classes AS cls ON enr.class_id=cls.id ORDER BY std.id;
```

### 外部結合：FULL OUTER JOIN
FULL OUTER JOINは**MySQL(ver8)ではできない**がUNIONを使って実現可能。
LEFT　JOINとRIGHT JOINの２つの要素を持った結合で**結合できなかった行はNULL**と表示する結合方法
```sql
-- FULL JOIN(両方から取り出して、取得できないものはNULL)
SELECT * FROM students AS std
LEFT JOIN enrollments AS enr ON std.id=enr.student_id 
LEFT JOIN classes AS cls ON enr.class_id=cls.id
UNION -- UNIONを使う
SELECT * FROM students AS std
RIGHT JOIN enrollments AS enr ON std.id=enr.student_id 
RIGHT JOIN classes AS cls ON enr.class_id=cls.id ORDER BY std.id;
```

### 複雑な結合の演習(外部キーが多い複数テーブルの結合、結合してから絞り込む、GROUP BYしてまとめたものを既存のテーブルに結合)
難しいけど副問い合わせの効果的な使い方もなんとなくわかってきた
```sql
-- 複雑な結合について復習

SELECT * FROM customers;
SELECT * FROM orders; -- customersとitemsの中間テーブル
SELECT * FROM items; -- storesと多対1の関係
SELECT * FROM stores;
-- customers.idで並び替える

SELECT * FROM customers AS cs
LEFT JOIN orders AS od ON cs.id=od.customer_id
LEFT JOIN items AS i ON od.item_id=i.id
LEFT JOIN stores AS st ON i.store_id=st.id ORDER BY cs.id;

-- だれがいつどこでなにをに並び替えてみた
SELECT cs.id,cs.first_name,od.order_date,st.name,i.name FROM customers AS cs
LEFT JOIN orders AS od ON cs.id=od.customer_id
LEFT JOIN items AS i ON od.item_id=i.id
LEFT JOIN stores AS st ON i.store_id=st.id ORDER BY cs.id;

-- さらにcutomers.idが10でorders.order_dateが2020-08-01より後に絞り込む
SELECT cs.id,cs.first_name,od.order_date,st.name,i.name FROM customers AS cs
LEFT JOIN orders AS od ON cs.id=od.customer_id
LEFT JOIN items AS i ON od.item_id=i.id
LEFT JOIN stores AS st ON i.store_id=st.id
WHERE cs.id =10 AND od.order_date>'2020-08-01' ORDER BY od.order_date;

-- 上記の絞り込みを副問い合わせで書く方法もある（結果は同じだからどちらでもいいがこういう書き方もあるよというもの）
-- どういうことかというとテーブルを取り出してくる時点でWHEREを使って絞り込んでいる(わかりづらいSQLなのでおすすめはしないらしい)
SELECT cs.id,cs.first_name,od.order_date,st.name,i.name FROM (SELECT * FROM customers WHERE id=10) AS cs
LEFT JOIN (SELECT * FROM orders WHERE order_date>'2020-08-01') AS od ON cs.id=od.customer_id
LEFT JOIN items AS i ON od.item_id=i.id
LEFT JOIN stores AS st ON i.store_id=st.id
ORDER BY od.order_date;

-- GROUP BY の紐付け
SELECT customer_id,SUM(order_amount*order_price) AS '総金額'
FROM orders GROUP BY customer_id ;
-- 総金額を出した上記のテーブルをcustomersテーブルにくっつけるイメージ
SELECT * FROM customers AS cs
LEFT JOIN( -- ()内に上記のSQLを入れ込む
	SELECT customer_id,SUM(order_amount*order_price) AS '総金額'
	FROM orders GROUP BY customer_id
) AS order_summary
ON cs.id=order_summary.customer_id;
```

### SELF JOIN　自己結合
自分に結合する結合方法
例：上司が同じテーブルの中に入っているテーブル
maneger_idが自身のテーブルの主キーを示しているテーブルなど。
同じテーブルに**別名をつけて結合する**

```sql
-- SELF JOIN(自己結合)
SELECT * FROM employees AS emp1
INNER JOIN employees AS emp2
ON emp1.manager_id=emp2.id;

-- CONCATを使ってみやすくする
SELECT 
	CONCAT(emp1.last_name,emp1.first_name) AS '部下の名前',
	CONCAT(emp2.last_name,emp2.first_name) AS '上司の名前'
FROM employees AS emp1
LEFT JOIN employees AS emp2
ON emp1.manager_id=emp2.id; -- LEFT JOINを使うと上司がいない人もNULLで取り出される
-- おまけ：COALESCEを使ってNULLを回避することもできる
```

### CROSS JOIN 交差結合
例えばジュースS,M,LとポテトS,M,Lのテーブルがあったとすると**２つのテーブルのデータのすべての組み合わせを取得するSQL**

```sql
-- 交差結合
SELECT * FROM テーブル1　AS 別名１ CROSS JOIN テーブル２　AS 別名２　（ON 結合条件）
-- 古い書き方
SELECT * FROM テーブル1　AS 別名１, テーブル２　AS 別名２ (WHERE 条件)
```

最後の複雑なSQLができる人はあまりいないとおもうがこういった処理ができることも覚えておく
```sql
-- CROSS JOIN
-- こちらはCROSS JOINの古い書き方。まだ使うので読み解けるようにはしておく
SELECT * FROM employees AS emp1,employees AS emp2
WHERE emp1.id=1;

-- 新しい書き方
SELECT * FROM employees AS emp1
CROSS JOIN employees AS emp2
ON emp1.id =1;

-- 計算結果とCASEで紐付け
SELECT AVG(age) AS avg_age FROM customers; -- これをCROSS JOINで紐づける
-- テーブルに平均年齢をくっつけるSQL
SELECT * FROM customers AS cs
CROSS JOIN(
	SELECT AVG(age) AS avg_age FROM customers
) AS summary_customers; -- CROSS JOINなので別名をつけないとエラーになる

-- さらに平均年齢より高いと⚪というカラムをCASEを使って追加する
SELECT
	*,
	CASE 
		WHEN cs.age>summary_customers.avg_age THEN '⚪︎'
		ELSE '×'
	END AS '平均年齢よりも高いか'
FROM customers AS cs
CROSS JOIN(
	SELECT AVG(age) AS avg_age FROM customers
) AS summary_customers;

-- もう一つ複雑なSQLを練習
SELECT * FROM salaries;
SELECT * FROM employees;
SELECT AVG(payment) AS avg_payment FROM salaries AS summary; -- 平均7,805,904

/* やりたいこと：salariesテーブルのpaymentの平均を求めて、それをemployeesテーブルに全て組み合わせる。
 * 平均月収以上かを調べたいがemployeesテーブルには月収の情報がないので事前にINNER JOINで紐付けを行なっておく。
 * GROUP BYでemp.idごとにsalariesテーブルの平均をまとめる
 * メインクエリのSELECTにてCASEを使ってemployees+salaries(INNER JOINでまとめたテーブルをGROUP BY)のAVG(payment)が
 * CROSS　JOINでくっつけたAVG(payment)別名avg_paymentより高いかを判定する
 */
SELECT
	emp.id,
	AVG(payment), -- GROUP BYした平均
	summary.avg_payment, -- salariesの平均をCROSS JOINでくっつけたデータ
	CASE 
		WHEN AVG(payment)>= summary.avg_payment THEN '⚪︎'
		ELSE '×'
	END AS '平均月収以上か'
FROM employees AS emp
INNER JOIN salaries AS sa
ON emp.id=sa.employee_id
CROSS JOIN
	(SELECT AVG(payment) AS avg_payment FROM salaries) AS summary
GROUP BY emp.id,summary.avg_payment; -- GROUP BYはSELECTする対象で,AVGなどの集計関数を使用しないカラムはGROUP BYに含める
	
```

### with構文
WITHの後に記述したSQLの実行結果を、一時的なテーブルに格納する可読性の高い構文のこと
**MySQLではver8.0以降で利用できる**

:::note
　WITH文のメリットとしてはわかりやすいのと
上から実行されるので処理の順番を決めたい時に便利。
なぜ順番を決めるのかというと結合やGROUP　BYでまとめたりするのは
総データ量が多いと負荷がかかってしまう。
なので絞り込むWHEREなどは最初に実行してしまう方がいい。なので上にWHEREを書いている

補足：SQLはあくまで結合や絞り込みなどの処理内容を記述した文。
DBのオプティマイザーという機能が実際に処理する順序や方法を決めるらしい
:::
```sql
WITH 中間テーブル名1 AS(SELECT文),
WITH 中間テーブル名1 AS(SELECT文)
-- そのあとに書くSQLでその中間テーブルが使える。このテーブルは何を持ってきてるか、というのがわかりやすいのが特徴
SELECT * FROM テーブル
INNER JOIN 中間テーブル1 ON テーブル.id=中間テーブル１.id
INNER JOIN 中間テーブル2 ON 中間テーブル1.id=中間テーブル2.id

```
```sql
-- WITH
-- departmentsから営業部の人を取り出して、employeesと結合する

WITH tmp_departments AS(
	SELECT * FROM departments WHERE name="営業部"
) -- 一時的なテーブルの定義が終わったらSELECTする
SELECT * FROM employees AS emp
INNER JOIN tmp_departments ON emp.department_id =tmp_departments.id;
-- 先に絞り込んだものをINNER　JOINするというやり方


-- 複雑な結合と絞り込みほどWITH構文は輝く
/*
 * storesテーブルからid 1,2,3のものを取り出す(WHERE)
 * itemsテーブルと紐付け、itemsテーブルとordersテーブルを紐づける(INNER JOIN)
 * ordersテーブルのorder_amount*order_priceの合計値をstoresテーブルのstore_name毎に集計する(GROUP BY SUM)
 * といったSQLを書いてみよう
 */
WITH tmp_stores AS( -- １つ目の一時的テーブル作成
	SELECT * FROM stores WHERE id IN(1,2,3)
),tmp_items_orders AS( -- ２つ目の一時的テーブル作成
	SELECT 
		items.id AS item_id,
		tmp_stores.id AS store_id,
		orders.id AS order_id, -- idという名前のカラムが何個もあるので別名にしてあげるとエラー回避できる
		orders.order_amount AS order_amount,
		orders.order_price AS order_price,
		tmp_stores.name AS store_name
	FROM tmp_stores
	INNER JOIN items -- storesをitemsと紐付け
	ON tmp_stores.id=items.store_id
	INNER JOIN orders -- itemsとordersを紐付け
	ON items.id=orders.item_id
)
-- WITHを作れたらSELECT
SELECT store_name,SUM(order_amount*order_price) FROM tmp_items_orders GROUP BY store_name;

/*
要はstoresテーブルからidが1,2,3のレコードを先に取り出して（with１つ目）
それにitemsとordersをinner joinさせる。それもwithをつかってjoinさせたテーブルを新たにエイリアスをつける（with２つ目）
その新たに作ったテーブルをgroup byでまとめているという作業
*/
```