Zenn
🔥

学習備忘録〜O'Reilly「初めてのSQL」5章　複数テーブルからデータを取得する〜

2024/01/01に公開
SQL
tech

はじめに

この備忘録は、新卒1年目の初学者バックエンドエンジニアの学習記録のためにつけているものです。
解釈違いや、誤情報がある可能性があります。見つけた際にはご指摘をお願いします。

概要

複数のテーブルに記載されている情報を1つのレポートにまとめたい場合、対象のテーブルを1つのテーブルにまとめるメカニズムが必要となる。このメカニズムを結合（join）と呼ぶ。ここでは、最も単純な内部結合（inner join）に焦点を当てていく。

結合とは何か

両方のテーブルの列をクエリの結果セットにつかできるような処理のこと

デカルト積

選択された全テーブルの全純烈であり、直積とも呼ばれる。
意図的に使われることは滅多にない。

内部結合

mysql> SELECT c.first_name, c.last_name, a.address
    -> FROM customer c JOIN address a
    -> ON c.address_id = a.address_id;

一方のテーブルの列に存在する値が、もう一方のテーブルには存在しない場合、その値を含んでいる行の結合は失敗し、それらの行は結果セットに含まれなくなる。このような結合を内部結合と呼ぶ。
一方で、一致する行が存在する稼働かに関係なく、どちらか一方のテーブルに存在する行がすべて結果セットに追加されるようにしたい場合は、外部結合を使う必要がある。

ANSIの結合構文

ANSIの結合構文を使う利点

  • 結合条件とフィルタ条件が別々の句（onキーワードとwhere句）に分かれているため、クエリが理解しやすくなる
  • 結合条件がテーブルのペアごとに別のonキーワードに追加されるため、その部分の結合をうっかり書き忘れるという可能性が低くなる
  • SQL92の結合構文を使うクエリにはデータベースサーバ間での移植性があるが、古い構文にはデータベースサーバによって若干の違いがある

3つ以上のテーブルを結合する

3つのテーブル結合は2つのテーブル結合とほとんど同じだが、ちょっとした違いが1つある。
2つのテーブルの結合では、from句で2つのテーブルと1種類の結合を指定して、テーブルの結合方法を定義するonキーワードを1つだけ追加する。
3つのテーブル結合では、from句で3つのテーブルと2種類の結合を指定して、onキーワードを2つ追加する。

サブクエリをテーブルとして使う

mysql> SELECT c.first_name, c.last_name, addr.address, addr.city
    -> FROM sudtomer c
    ->    INNER JOIN
    ->      ( DELECT a.address_id, a.address, ct.city
    ->        FROM address a
    ->          INNER JOIN city ct
    ->          ON a.city_id = ct.city_id
    ->        WHERE a.district = 'California'
    ->      ) addr
    ->     ON c.address_id = addr.address_id;

同じテーブルを２回使う

複数のテーブルを結合する際に、同じテーブルを２回以上結合しなければならないことがある。

mysql> SELECT f.title
    -> FROM film f
    ->   INNER JOIN film_actor fa1
    ->   ON f.film_id = fa1.film_id
    ->   INNER JOIN actor a1
    ->   ON fa1.actor_id = a1.actor_id
    ->   INNER JOIN film_actor fa2
    ->   ON f.film_id =fa2.film_id
    ->   INNER JOIN actor a2
    ->   ON fa2.actor_id = a2.actor_id
    -> WHERE (a1.first_name = 'CATE' AND a1.last_name = 'MCQUEEN' )
    ->   AND (a2.first_name = "CUBA" NAD a2.last_name = "BIRCH' );

自己結合

同じクエリで同じテーブルを複数回指定できることに加えて、実際にはテーブルを同じテーブルに結合することも可能である。
テーブルの中には、自己参照外部キーを含んでいるものがある。自己参照キーとは、同じテーブルないの主キーを参照している列のこと。

Discussion