仕事

業務で使うSQLのチートシート

2023年8月12日

inner join

SELECT * FROM employees e
INNER JOIN posistions p ON e.emp_no = p.emp_no;

上記のようにすると、結合できた行だけを抽出する。つまり両方のテーブルに一致する行を返す。内部結合といい、employeesテーブルとpositionsテーブルのデータを抽出する。

left join

SELECT * FROM employees e
LEFT JOIN posistions p ON e.emp_no = p.emp_no;

上記のようにすると、右のテーブル(posistions)に一致するものがない場合でも、左のテーブル(employees)のすべての行を抽出する。

right join

左のテーブルに一致するものがない場合でも、右のテーブルからすべての行を抽出する。

not exists

SELECT * FROM employees 
WHERE 
	not exists (
		select * from positions
		where 
			employees.emp_no = positions.emp_no 
			and positions.title = 'staff'
	);

上記のようにすると、役職がstaffの社員が除外されて抽出される。

SELECT * FROM employees 
WHERE 
	not EXISTS (
		select * from positions
		where employees.emp_no = positions.emp_no
	);

上記のようにするとemployeesテーブルにはデータが存在するが、positionsテーブルにはデータが存在しないデータを抽出できる。つまり2つテーブルのどちらか片方にしかないデータを抽出できるので、データ整合性を保っているか確認できる。

Reference制約エラー

お客様の操作
①お客様Aは商品Aをお客様Aの取り扱い商品リストに登録。
②商品Aを商品マスタから削除
「商品マスタ」⇒削除フラグが設定される　　　　　→問題なし　※仕様：削除フラグ設定
「顧客別商品マスタ」⇒削除フラグが設定される　　→問題なし　※仕様：削除フラグ設定
③日次処理実行
（削除フラグが設定されている商品マスタの商品を「顧客別商品マスタ」から物理削除）
「顧客別商品マスタ」⇒物理削除されない　→不具合★１　※仕様：物理削除
以下「REFERENCE制約エラー」発生

DELETE customer_goods FROM customer_goods AS cg WHERE exists(SELECT 1 FROM goods as g WHERE cg.good_id = g.good_id AND g.del = 1

④年次処理実行（ある一定の時間使用されなかった論理削除された商品マスタデータを削除）
「商品マスタ」⇒物理削除されない　→不具合★２　※仕様：物理削除
原因
SQLエラーでデータ削除が正しくされない。
上記★１では「顧客別商品マスタ」側は主キーとして、別のテーブルから参照されていた。
上記★２では「商品マスタ」側は主キーとして、「顧客別商品マスタ」側から参照されていた。
参照されている側のデータを削除すると「REFERENCE制約エラー」が発生する。
つまり、「顧客別商品マスタ」に存在している商品コードは、「商品マスタ」から削除できない。
逆も同じで「商品マスタ」に存在しない商品コードを「顧客別商品マスタ」に登録できない、存在するものしか登録できない。

不整合データを抽出する

例えば商品の価格をお客様毎に調整できる機能を持つシステムがあるとする。
テーブル構成としては以下
エンティティ一覧

論理エンティティ名 物理エンティティ名 備考
商品マスタ goods 商品を定義するマスタ
顧客マスタ customers 顧客を定義するマスタ
顧客別商品マスタ customer_goods 顧客毎に扱う商品を定義するマスタ
顧客別商品価格調整マスタ customer_goods_adjust 顧客毎に商品単価調整を行うマスタ

顧客別商品マスタで商品価格が調整されているか標準なのかの区分を持つ。
sale_type = 0 ：標準
sale_type = 1：調整

不整合のデータを抽出したい。
※不整合とは調整されている商品にもかかわらず、何らかの原因で「顧客別商品価格調整マスタ」に価格調整のデータが登録されていないデータのこと

select g.good_id, g.goods_name, c.customer_name, sale_type
from goods g
inner join customer_goods cg on g.goods_id = cg.goods_id
inner join customers c on cg.customer_id = c.customer_id
where g.delete_flg = '0'
and sale_type <> '0'
and not exists (select * from customer_goods_adjust cga where cg.goods_id = cga.goods_id and cg.customer_id = cga.customer_id)
order by good_id, customer_id

-仕事