大ちゃんの駆け出し技術ブログ

RUNTEQ受講生のかわいいといわれるアウトプットブログ

トップ > SQL > 【無職に転生 ~ 就職するまで毎日ブログ出す⑧】【SQL】JOIN

【無職に転生 ~ 就職するまで毎日ブログ出す⑧】【SQL】JOIN

SQL JOIN

はじめに

こんにちは、大ちゃんの駆け出し技術ブログです。

タイトルにあるとおり【無職に転生 ~ 就職するまで毎日ブログ出す】というチャレンジをしています！！！！昨日までは就活するまで本気出すでしたが、これだとまるで就活後は頑張らないのかと思われてしまいそうで、、、大人気アニメのタイトルのまるパクリチャレンジです。

RailsやらRubyやらSQLやらその他Webの知識やらが色々と抜け落ちているのを感じており、知識の定着のためにもアウトプットする機会を増やすためです。加えて、退職して文字通り無職に転生しましてプロニートになり、毎日時間に余裕ができたので引き締めるためにも毎日投稿を思い至りました！

【投稿内容】

  • SQLの難しい処理 (副問合せ、JOINとか複雑な処理が書けない)
  • Rails全般 (純粋に必要な知識が多すぎる、網羅的な理解が足りない)
  • Rubyのあまり使わないメソッドや記述方法 (あまり重要ではないけど特に)
  • Web知識全般 (クッキーやら、セッションやらなんとなくで理解しているものの自分の言葉で説明できない)
  • 書籍 (スタートアップ企業に勤めるので、自分が会社に与える影響やパフォーマンスを高めるためビジネス書を読んでいきます。)

本記事でやること

本日は初学者の多くがつまづくであろうJOINに関して書いていこうと思います。自分も正直理解が浅いこともあり今でも「あれ、この書き方でちゃんと意図した通りにテーブル結合されているかな？」なんてことは頻発しています。そのため、本日は「JOINに関するいろいろ」というタイトル通り、JOINに関連しているSQLを紹介していきます。

JOINが存在する理由

まずJOINはなぜあるのかについてです。

「え、テーブルとテーブルを結合するからじゃない？」

そうです。テーブルとテーブルを結合するからです。ここで言いたいのは、テーブルが複数存在することが重要です。つまり、テーブルが正規化されていることがテーブルが複数存在することにつながっています。

Database normalization is useful because it minimizes duplicate data in any single table, and allows for data in the database to grow independently of each other (ie. Types of car engines can grow independent of each type of car). データベースの正規化は、1つのテーブル内の重複データを最小限に抑え、データベース内のデータが互いに独立して増加することを可能にするため、有用です（例えば、自動車のエンジンの種類は、それぞれの自動車の種類とは独立して増加することができます）

データベースの正規化によりテーブルが細かく分けられそれぞれのテーブルが独立した役割を持っています。それにより各テーブルがシンプルな状態に保たれます。

https://i.gyazo.com/df6beb6c8f6eb63100eb963f580ac513.png

https://proengineer.internous.co.jp/content/columnfeature/6480

正規化されることでテーブルは複数存在しますが、これらのデータを組み合わせようとすると「一つのデータを取り出す」「別のデータを取り出す」「取り出した二つのデータを組み合わせる」という三つの工程が必要になります。テーブルを独立させてしまったがために処理の工数が増えてしまいました。

それを解決するためにJOINが存在します。必要なデータを結合させて取得するデータをセットで揃えることで工数を削減することができます。

JOINの種類

JOINにはテーブルの結合方法として大まかに二つあります。

  • 内部結合 (INNER JOIN)
  • 外部結合 (LEFT JOIN)

内部結合

内部結合であるINNER JOINはテーブル結合の中で頻繁に出てきます。この結合は二つのテーブルで共通の列をマッチングさせて、マッチしたものをテーブルとして抽出します。

書き方としては下記のとおり。共通列とは外部キーに相当します。

SELECT テーブル.列(別テーブル.列)
FROM テーブル
JOIN (INNER JOINと同義) 別テーブル
ON テーブル.共通列 = 別テーブル.共通列

見るは言うより易し。まずは内部結合の例文を書きます。

例えば、下記のようなテーブルがあるとします。

https://i.gyazo.com/0b80189a76fd39965728226b81814211.png

https://sqlbolt.com/lesson/select_queries_with_joins

そしid、title、domestic_sales、international_salesを取得します。

SELECT movies.id,  movies.title, boxoffice.domestic_sales, boxoffice.international_sales 
FROM movies
JOIN boxoffice
ON movies.id = boxoffice.movie_id
ORDER BY movies.id;

結果

https://i.gyazo.com/380ce1b2b7d6d7582050c165ada6dbaf.png

今回のテーブル群の共通列はmoviesテーブルのid列とboxofficeテーブルのmovie_id列になります。それらを指定して結合させることができます。

ON movies.id = boxoffice.movie_id

各列に対してテーブル名.列としている理由は、二つのテーブルに同名の列がある場合、テーブル名をつけないとどちらのテーブルの列になるのかわからないためです。例えば、もしboxofficeの列にidがあるのであれば下記の場合エラーになります。

SELECT id, id 
FROM movies
JOIN boxoffice
ON movies.id = boxoffice.movie_id
ORDER BY movies.id;

正しく動かすためにはテーブル名をつけてどのテーブルのidかを指定します。

SELECT movies.id, boxoffice.id 
FROM movies
JOIN boxoffice
ON movies.id = boxoffice.movie_id
ORDER BY movies.id;

今回の場合同名のカラムはないのでテーブル名をつけなくても問題はないのですが、一応テーブル名をつけておく方法も理解しておきます。

INNER JOINの性質として条件にマッチしないベースとなるテーブルは削除されるというものがあります。

https://i.gyazo.com/773ea246eaf27e11de0f85d78a2d2b76.png

ここでテーブルをINNER JOINさせます。

SELECT * FROM dogs
JOIN owners 
ON dogs.owner_id = owners.id;

結果

https://i.gyazo.com/c4d48027560cb6d46dd90f4cba0f4ba7.png

dogsテーブルのkuroがいなくなっていることに注目してください。kuroのowner_idは4です。ですが、ownersテーブルでidが4番は存在していません。内部結合の場合、ベースとなるテーブルから、条件にマッチするレコードがないものは削除されるのです。

SQL素人でも分かるテーブル結合(inner joinとouter join)

ほとんど引用ですみません。ですが説明を見てわかるとおりベースとなるテーブルの列が外部テーブルの列にマッチしないレコードは削除されてしまいます。

外部結合

LEFT JOINは上記のようにベースとなるテーブルからレコードが削除されて欲しくない場合に使用します。

SELECT * FROM dogs 
LEFT JOIN owners 
ON dogs.owner_id = owners.id;

出力結果

https://i.gyazo.com/5998d8f81c396d5ea1b3751455637523.png

外部結合をすると、外部の列にないレコードは削除されず残ってしまうため、列にないレコードはnullとして出力されてしまいます。nullで出力されたくない場合はCOALESCE関数を使ってnullを置き換えます。

SELECT dogs.id, dogs.name, COALESCE(owners.name, '外部テーブルは空値') FROM dogs 
LEFT JOIN owners 
ON dogs.owner_id = owners.id;

参考記事

SQL素人でも分かるテーブル結合(inner joinとouter join)

Multi-table queries with JOINs