目次
エンジニアにとってみれば、SQL は使えてあたりまえかもしれませんが、営業や管理部の人からすれば魅惑の魔法といわれることもあります。 しかし非エンジニアの方でも SQL が使えた方がいいですし、 そのほうがエンジニアの作業も楽になります。
ここでは、複数のテーブルからデータを取得する場合を考えます。
環境
- PostgreSQL
- pgAdmin 3
- Windows 10
PostgreSQL を使います。 データベースに Oracle Database, MySQL を使用している場合でも同じです。
テーブルをつなげる
次のような売上テーブル(sales)とメンバーテーブル(members)を考えます。
| id | amount | member_id | sale_date |
|---|---|---|---|
| 1 | 1000 | 4 | 2016-02-03 |
| 2 | 2000 | 4 | 2016-04-01 |
| 3 | 5000 | 2 | 2016-04-05 |
| 4 | 3000 | 3 | 2016-06-03 |
| id | name |
|---|---|
| 1 | Taro Yamada |
| 2 | Mao Yamada |
| 3 | Yoshio Yamada |
| 4 | Hiro Yoshida |
| 5 | Kazuki Yoshida |
ここから、売上と名前の表を作りたいとします、下のように。
| id | user_name | amount | sale_date |
|---|---|---|---|
| 1 | Hiro Yoshida | 1000 | 2016-02-03 |
| 2 | Hiro Yoshida | 2000 | 2016-04-01 |
| 3 | Mao Yamada | 5000 | 2016-04-05 |
| 4 | Yoshio Yamada | 3000 | 2016-06-03 |
売上をベースに、そのメンバーを一緒に表示します。
|
1 2 3 4 5 6 7 8 9 |
SELECT sales.id, members.name member_name, sales.amount, sales.sale_date FROM sales JOIN members ON members.id = sales.member_id ORDER BY sales.id |
JOIN で結合するテーブルを宣言し、 ON の中に結合する条件を書きます。 ここでは sales.member_id と members.id が等しいことが条件です。
そして、これまでと違って、 テーブル名を書いてからカラム名を書いています。 今回はそのようにしないと実行できません。 というのも、 1行目にある sales.id が id になっていると、 sales の id なのか、 members の id なのかわからないからです。 他の、片方のテーブルにしかないカラム(列)はテーブル名を除いてもかまいません。
また、2行目ではカラム名の後にカラムの別名をつけています。 こうすると、表示する際には指定した別名が使用されます。
長いテーブル名を毎回書くのが大変な場合は、頭文字をとるなどして短縮形にすることができます。 カラムと同じようにテーブルにも別名がつけられます。
|
1 2 3 4 5 6 7 8 9 |
SELECT s.id, m.name member_name, s.amount, s.sale_date FROM sales s JOIN members m ON m.id = s.member_id ORDER BY s.id |
FROM, JOIN の後に書くテーブル名の後ろに、 s, m といったテーブルの別名を書くことで、その別名が使えるようになります。
検索条件を記述する場合も、以前みたのと同じように書くことができます。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT s.id, m.name member_name, s.amount, s.sale_date FROM sales s JOIN members m ON m.id = s.member_id WHERE s.amount > 1000 AND m.id = 4 ORDER BY s.id |
たくさんのテーブルをつなげる
売上商品テーブル(sale_items)とメンバプロファイルテーブル(member_procfiles)をつなげることを考えます。
| id | sale_id | item_id | item_name |
|---|---|---|---|
| 1 | 1 | 204 | リモコン |
| 2 | 1 | 33 | サイコロ |
| 3 | 2 | 52 | 傘 |
| 4 | 2 | 63 | 靴 |
| 5 | 2 | 76 | 電卓 |
| 6 | 3 | 36 | スプレー |
| 7 | 3 | 17 | スピーカー |
| 8 | 3 | 55 | クリーム |
| 9 | 3 | 91 | パンフレット |
| 10 | 3 | 86 | マニュアル |
| id | member_id | tel |
|---|---|---|
| 1 | 1 | 111-456-7890 |
| 2 | 2 | 222-456-7890 |
| 3 | 3 | 333-456-7890 |
| 4 | 4 | 444-456-7890 |
| 5 | 5 | 555-456-7890 |
つなげたいテーブルがあれば、 JOIN でさらにつなげればデータを取得できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT s.id, m.name member_name, mp.tel, s.amount, s.sale_date, si.item_name FROM sales s JOIN members m ON m.id = s.member_id JOIN member_profiles mp ON mp.member_id = m.id JOIN sales_items si ON si.sale_id = s.id ORDER BY s.id |
この場合は、sale_items は sales.id が 1から3までのデータしかありませんから sales.id が 4, 5 のデータは出てきません。 そういったデータも出す場合は LEFT JOIN を使います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT s.id, m.name member_name, mp.tel, s.amount, s.sale_date, si.item_name FROM sales s JOIN members m ON m.id = s.member_id JOIN member_profiles mp ON mp.member_id = m.id LEFT JOIN sales_items si ON si.sale_id = s.id ORDER BY s.id |
LEFT JOIN はテーブルを結合しますが、結合されるテーブルにデータがあれば出力します。 ここではすべての JOIN を LEFT JOIN にしても出力されるデータは変わりません。 私はよほどのことがない限り、すべて LEFT JOIN にして SQL を書きます。
結合する条件が複数ある場合
テーブルを結合する場合は JOIN を使用します。 テーブルの結合条件に複数の条件がある場合は次のように書きます。
|
1 2 3 4 5 6 |
FROM first_table f LEFT JOIN second_table s ON s.first_id = f.id LEFT JOIN third_table t ON t.first_id = f.id AND t.second_id = s.id |
AND, OR を利用して、条件を書くことができます。 検索条件(WHERE)と同じように NOT も使えます。
結合条件と検索条件の使い分け
テーブルを結合するときは ON の後に条件を書きます。 ON の中でデータを絞り込むこともできますが、データの絞り込みは WHERE で行います。
ON はテーブルの関係を記述する場所だと考えましょう。
補足
テーブルを結合するときに使うカラムは xxxx_id となっていることが多いですが、開発現場によって変わってきますのでほかのパターンもあり得ます。
