kin29.info

 

SQLの話です。

使ったもの → Postgres

 

ここにこんなテーブルがあります。

# SELECT * FROM duplication_table;
id | sub_id | name | price | description
—-+——–+——————+——-+———————-
1 | 1 | 普通の牛乳 | 190 | 生乳100%
1 | 2 | 濃厚な牛乳 | 240 | 一番好きなやつ
1 | 3 | 生乳30%の牛乳 | 180 |
2 | 1 | スーパーなカップ | 120 | 結局これ買っちゃう
2 | 2 | ハーゲンなダッツ | 250 | たまに買っちゃう
3 | 1 | ハッピーなターン | 150 | 粉が多いのもあるよね

 

Q.重複idは排除してidを知りたい時ってどうしてますか？

A.DISTINCTですよね！

# SELECT DISTINCT(id) FROM duplication_table;
id
—-
1
3
2

 

 

まあ、これはよく見る。

 

 

Q.「他のカラム情報も欲しいんや！」って時ありません？

A.朗報です!!DISTINCT ONってのがあります！

# SELECT DISTINCT ON(id)* FROM duplication_table;
id | sub_id | name | price | description
—-+——–+——————+——-+———————-
1 | 1 | 普通の牛乳 | 190 | 生乳100%
2 | 1 | スーパーなカップ | 120 | 結局これ買っちゃう
3 | 1 | ハッピーなターン | 150 | 粉が多いのもあるよね

ほらね？けど、これどの条件で絞ってるんだっけ？

謎ですね。sub_idっぽいけど。。。。。

 

id中の最小priceを残して、重複排除

# SELECT DISTINCT ON(id)* FROM duplication_table ORDER BY id,price;
id | sub_id | name | price | description
—-+——–+——————+——-+———————-
1 | 3 | 生乳30%の牛乳 | 180 |
2 | 1 | スーパーなカップ | 120 | 結局これ買っちゃう
3 | 1 | ハッピーなターン | 150 | 粉が多いのもあるよね

こうすれば全カラム取得できました！！！

 

テーブルって結合すると便利ですよね。

結合しないと得たいものを得ることができないことがよくあります。

 

 

しかし、右にJOINする？左？いや、INNER？

ってなる

結果、副問い合わせをする。

パフォーマンス的にどうなの？ってなる。

 

 

ということで、今日の目標は

JOIN使いこなせるようになろう！

 

＜データ＞

# SELECT * FROM parent_table ORDER BY id;
id | name
—-+——–
1 | サザエ
2 | フネ
3 | タイ子

 

# SELECT * FROM child_table ORDER BY id;
id | name | parent_id
—-+——–+———–
1 | サザエ | 2
2 | カツオ | 2
3 | ワカメ | 2
4 | タラ     | 1
5 | イクラ | 3
6 | タマ     |

 

 

まずは、INNER JOIN

・child_tableにINNNER JOINするパターン

# SELECT * FROM child_table INNER JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+——–
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ　 | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子

 

タマはparent_idがないため、結合されませんでした。

 

・parent_tableにINNNER JOINするパターン

# SELECT * FROM parent_table INNER JOIN child_table ON parent_table.id =child_table. parent_id ORDER BY child_table.id;
id | name | id | name | parent_id
—-+———-+—-+———–+———–
2 | フネ　 | 1 | サザエ | 2
2 | フネ 　| 2 | カツオ | 2
2 | フネ 　| 3 | ワカメ | 2
1 | サザエ | 4 | タラ　 | 1
3 | タイ子 | 5 | イクラ | 3

 

ここでも、タマはparent_idがないため、結合されませんでした。

よって、左右テーブルどちらにINNER JOINしてもデータの内容としては変わらない。

また、フネ(parent_id=2)に関しては、３行あります。

 

 

▶︎INNER JOINは、

左右それぞれのテーブル.カラムの値が一致するレコードだけを取得する。

よって、一致しないレコードは削除される。

 

 

 

 

次はOUTER JOIN。

まずは、parent_tableにOUTER JOIN

parent_tableが右に来るのでRIGHT JOINしてます。

# SELECT * FROM child_table RIGHT JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+——–
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ 　| 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子

右のparent_tableを基本として結合するため、タマのように結合条件のparetn_table.idを持たないchild_table.parent_idは結合されませんでした。

 

 

次に、child_tableにOUTER JOIN

child_tableが左に来るのでLEFT JOINしてます。

# SELECT * FROM child_table LEFT JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+——–
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ 　| 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子
6 | タマ　 |　|　|

左のchild_tableを基本として結合するため、タマのように結合条件のchild_table.parent_idがparent_table.idに存在しなくてもNULLとして結合してくれる。

 

▶︎OUTER JOINは、

基本となったテーブル(左右どちらか)のレコードは全て取得し、左右それぞれのテーブル.カラムの値が一致しないレコードもNULLとして結合する。

 

 

※FULL JOINについて

 

ここで、データを追加する。

# INSERT INTO parent_table VALUES (4, ‘花澤さん’);

# SELECT * FROM parent_table ORDER BY id;
id | name
—-+———-
1 | サザエ
2 | フネ
3 | タイ子
4 | 花澤さん

 

# SELECT * FROM child_table ORDER BY id;
id | name | parent_id
—-+——–+———–
1 | サザエ | 2
2 | カツオ | 2
3 | ワカメ | 2
4 | タラ 　| 1
5 | イクラ | 3
6 | タマ 　|

 

おわかりの通り、花澤さんにひもづくchild_table.parent_idはない。

また、先ほどに続いてタマにひもづくparent_table.idもない。

 

＞parent_table(右)に結合！

# SELECT * FROM child_table RIGHT JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+———-
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ　 | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子
|　　　 |　 | 4 | 花澤さん

child_tableのタマのレコードはないが、

parent_tableは全て取得されている。

 

 

＞child_table(左)に結合！

# SELECT * FROM child_table LEFT JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+——–
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ　 | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子
6 | タマ 　|　 |   |

parent_tableの花澤さんのレコードはないが、

child_tableは全て取得されている。

 

 

＞＞本題のFULL JOIN

# SELECT * FROM child_table FULL JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+———-
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ　 | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子
6 | タマ　 | 　|　 |
|　　　 |　 | 4 | 花澤さん

# SELECT * FROM parent_table FULL JOIN child_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | id | name | parent_id
—-+———-+—-+——–+———–
2 | フネ 　　| 1 | サザエ | 2
2 | フネ 　　| 2 | カツオ | 2
2 | フネ　 　| 3 | ワカメ | 2
1 | サザエ 　| 4 | タラ　 | 1
3 | タイ子 　| 5 | イクラ | 3
|　 　　　| 6 | タマ　 |
4 | 花澤さん | 　|　　　 |

左右どちらにJOINするにしても、左右全てのレコードを取得する。

 

 

 

UPDATE文にて結合テーブルに対してWHERE句で条件しぼってるのに全行アップデートされるという悪夢におそわれました。

原因不明です。解決策は見つけました！

 

＜実行環境＞

・PostgreSQL 8.4.20

・PhpPgAdmin 5.0.4

 

＜データ＞
fruits_table

id	name	description
1	日向夏	宮崎のくだもの
2	バナナ	私の嫌いなくだもの
3	ぶどう	私の好きなくだもの

 

price_table

id	price
1	300
2	100
3	200

 

くっつけると、、、

id	name	description	price
1	日向夏	宮崎のくだもの	300
2	バナナ	私の嫌いなくだもの	100
3	ぶどう	私の好きなくだもの	200

 

 

＜失敗SQL＞

UPDATE 
 price_table
SET
 price = 150
FROM
 price_table T1
 JOIN fruits_table T2
 ON T1.id = T2.id
WHERE
 T2.name = 'バナナ'

id	name	description	id	price
1	日向夏	宮崎のくだもの	1	150
2	バナナ	私の嫌いなくだもの	2	150
3	ぶどう	私の好きなくだもの	3	150

 

(ﾟ⊿ﾟ)!!!

「バナナ」だけpriceを150にしたいのに

すべてのpriceが150になっちゃいました><

 

 

落ち着いて見る

SELECT 
 *
FROM
 fruits_table T1
 INNER JOIN price_table T2
 ON T1.id = T2.id
WHERE 
 T1.name = 'バナナ'

これ（上のSQL）実行したらちゃんとバナナだけの行を選択できるのにな・・・。

id	name	description	id	price
2	バナナ	私の嫌いなくだもの	2	100

 

 

 

うーん、ASをつかわず以下を実行すると

UPDATE 
 price_table
SET
 price = 100
FROM
 price_table
 JOIN fruits_table
 ON fruits_table.id = price_table.id
WHERE
 fruits_table.name = 'バナナ'

SQL エラー:

ERROR:  table name "price_table" specified more than once

と言われる。

 

 

(教えて、Googleせんせーい！)

検索ワード「update テーブル結合 postgres」 ポチっ

発見したーーー！！！→リンク

JOINではなくWHEREで結合させるんだってさ。

UPDATE 
 price_table T1
SET
 price = 150
FROM
 fruits_table T2
WHERE
 T2.id = T2.id
 AND T2.name = 'バナナ'

id	name	description	id	price
1	日向夏	宮崎のくだもの	1	300
2	バナナ	私の嫌いなくだもの	2	150
3	ぶどう	私の好きなくだもの	3	200

 

これで思い通りのアップデートが無事できました^^

今、実は「基本情報技術者試験」に向け、勉強してます。

んで、ちょうどデータベース分野について調べてました。

 

 

前回と内容がかぶるんですが、

今回は一意性制約(UNIQUEキー制約)について学んだことを書きますー！

 

 

一意性制約(UNIQUEキー制約)とは、、、

• NULL以外の各行を一意に制約する。
• ただし、NOT NULLではないためNULLを許します。
  →NULLが複数行存在することもあります。

 

私「NULL以外を一意にするだけで、NULL許すんだー、結構ゆる〜い。」

そこで気になる記事を見つけました。

 

複合一意キーにおいてすべてのキー列に対して NULL を持つ行も同様に複数持つことが可能。

ただし、1つ以上のキー列に対して NULL を持ち、その他のキー列に対して同じ組合せの値を持つ 2つの行は制約違反となる。(※)

一意キーは 1 つの表に 複数定義してもよい。

つまり

• 単一列における一意キー制約において NULL が存在して NULL を INSERT してもは制約に違反しない。
• 複合列における一意キー制約において
  (NULL,NULL) ⇔ (NULL,NULL) は制約に違反しないが
  (NULL,1000) ⇔ (NULL,1000) は一意キー制約違反となる。
  (1000,NULL,NULL) ⇔ (1000,NULL,NULL) も同様に違反となる。 (※)

https://www.shift-the-oracle.com/constraint/

 

 

ん？

よくわかんないのでやってみる。(バカです、はい)

※大好きなphpPgAdminでやってます。

 

CREATE TABLE id_number_unique_key_table(
  id integer UNIQUE,
  number integer UNIQUE
);

INSERT INTO id_number_unique_key_table
VALUES(NULL, NULL);

INSERT INTO id_number_unique_key_table
VALUES(NULL, NULL);

 

うんうん、いける！

 

カラムid,number両方において、同じNULLです。

一見、ユニークじゃないのにいいの？ってなるけど、

一意性制約はNULL以外に関しての制約なのでいいのです。

 

前回の復習をすると、ユニークだけどNULL(プライマリキーではない)だから、

レコードの識別ができないため、削除/編集ができない。

 

 

 

しかし、以下の文は一意性制約違反です。

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

 

( ｰ`дｰ´)/ダメｯ　てされる。

カラムnumberにおいて、1000,1000でかぶってますもんね。

一意ではないです。

NULLでもないです。

だから怒られちゃうんです。

 

 

 

もちろんこれはできます！

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

INSERT INTO id_number_unique_key_table
VALUES(NULL, 2000);

カラムid.number両方とも、NULLかユニークです。

ユニークキー(一意性制約)はNULLかユニークであれば違反ではない！

 

PhpPgAdminって便利ですよね。

でも、私はたまに以下のことを感じていました。

各レコードの編集/削除が出来るときと出来ないときがある。

この違いなんだ？

 

 

そこでタイミングよく、敏腕上司が教えてくれました。

 

上司「主キーがあればレコード識別ができるから、GUIで編集/削除できる」

 

私「すんごい！本当だー！しらなかったー！さすが●●さん！」

ちょっと考えると、当たり前のことだなと理解しましたwww

 

上司「ブログのネタにできるね」

 

以上、こんな会話がありました。早速ネタにします。

ありがとう●●さん。

 

 

そこで自分でいろいろ試してみました。

参考：https://www.postgresql.jp/document/9.4/html/ddl-constraints.html

 

その前に、、、

データベースは整合性を保つための整合性制約があります。

<整合性制約>

・一意性制約(ユニーク制約)

・参照制約(外部キー制約)

・非NULL制約(NOT NULL制約)

の3つがあります。

 

 

私「主キーはユニークだから一意性制約か！じゃあ、uniquであればいいのか！」

いつも通り、先走る私。

CREATE TABLE id_unique_table(
 id integer UNIQUE,
 name text
);

INSERT INTO id_unique_table
VALUES(1, test1);
INSERT INTO id_unique_table
VALUES(2, test2);
INSERT INTO id_unique_table
VALUES(3, test3);
INSERT INTO id_unique_table
VALUES(NULL, 'null');

 

一意性制約のマーク(「１」)が付いています

 

ん？

id=NULLのところは編集/削除できんやーん＞＜

 

 

ってことは….NOT NULLも必要なのか！

さっきのCREATE TABLE文に非NULL制約を追加

CREATE TABLE id_not_null_unique_table(
 id integer UNIQUE NOT NULL,
 name text
);

INSERT INTO id_not_null_unique_table
VALUES(1, 'test1');
INSERT INTO id_not_null_unique_table
VALUES(2, 'test2');
INSERT INTO id_not_null_unique_table
VALUES(3, 'test3');

※INSERT INTO id_not_null_unique_table
VALUES(null, ‘null’); は非NULL制約に反するためINSERTできませんでした。

 

想定通り、非NULL制約が追加されました。

 

 

私「なるほど。id=NULLのレコードが複数あったらレコードを識別できない。

ってことは、主キーは一意性制約と非NULL制約が必要なんだ！」

 

 

つまり、

CREATE TABLE テーブル(
 id integer UNIQUE NOT NULL,
 name text
);

と

CREATE TABLE テーブル(
 id integer PRIMARY KEY,
 name text
);

は同じ！

 

試しに、後者を実行。

PRIMARY KEY宣言したら、勝手にNOT NULLが入ってた！

 

 

 

ユニークでNOT NULLじゃないと、

そりゃー誰でも識別できんですわ。