=> SELECT ord_id, ord_date, pname FROM orders JOIN prod -> ON orders.prod_id = prod.prod_id;
ord_id | ord_date | pname ---+---+--- :
FROM
句にJOIN
句の左右に表を記載ON
句に結合条件を記載結合のSQL構文
=> SELECT ord_id, ord_date, pname FROM orders JOIN prod -> USING (prod_id);
結合に使用する列名が同じ場合は
USING
句で表記を簡単にできる=> SELECT ord_id, ord_date, pname FROM orders -> NATURAL JOIN prod;
同じ列名を持つ列を結合条件とする場合は
NATURAL JOIN
句で列名の指定を省略できる列名にテーブル名を指定する
db1=> ¥d tab1
Table "public.tab1"
Column | Type | Modifiers ---+---+--- id1 | numeric(2,0) |
col | character varying(14) |
db1=> ¥d tab2
Table "public.tab2"
Column | Type | Modifiers ---+---+--- id2 | numeric(2,0) |
col | character varying(14) |
db1=> SELECT id1, col, id2 FROM tab1, tab2 db1-> WHERE tab1.id1 = tab2.id2;
ERROR: column reference "col" is ambiguous LINE 1: SELECT id1, col, id2 FROM tab1, tab2 ^
結合するテーブル に同じ名前の列 があると列を 区別できない
列名にテーブル名を指定する
db1=> SELECT id1, tab1.col, id2 FROM tab1, tab2 db1-> WHERE tab1.id1 = tabl2.id2;
id1 | col | id2 ---+---+--- 1 | A | 1 2 | B | 2 (2 rows)
列名の前に
テーブル名を明示する
db1=> SELECT id1, a.col, id2 FROM tab1 a, tab2 b db1-> WHERE a.id1 = b.id2;
db1=> SELECT id1, a.col, id2 FROM tab1 AS a, tab2 AS b db1-> WHERE a.id1 = b.id2;
テーブル名に別名をつけると
テーブル名を別名で参照できるため、表記がシンプルに
結合条件に合致しない行の扱い
prod_id prod_name price
1
みかん50
2
りんご60
3
メロン100
order_id order_date prod_id qty
1 2013/9/1 1 10
2 2013/9/2 2 5
3 2013/9/2 1 8
4 2013/9/3 2 3
5 2013/9/3 2 4
order_id order_date prod_id prod_name qty
1 2013/9/1 1
みかん10
2 2013/9/2 2
りんご5
3 2013/9/2 1
みかん8
4 2013/9/3 2
りんご3
5 2013/9/3 2
りんご4
orders prod
orders.prod_id = prod.prod_id
→ prod_id=3 (
メロン)
に対応するデータが表示されない外部結合 (OUTER JOIN)
=> SELECT ord_id, ord_date, pname FROM orders JOIN prod -> ON orders.prod_id = prod.prod_id;
ord_id | ord_date | pname ---+---+--- 3 | 2013-09-02 |
みかん1 | 2013-09-01 |
みかん5 | 2013-09-03 |
りんご4 | 2013-09-03 |
りんご2 | 2013-09-02 |
りんご(5 rows)
db1=> SELECT ord_id, ord_date, pname
db1-> FROM orders RIGHT OUTER JOIN prod db1-> ON orders.prod_id = prod.prod_id;
ord_id | ord_date | pname ---+---+--- 1 | 2013-09-01 |
みかん3 | 2013-09-02 |
みかん2 | 2013-09-02 |
りんご4 | 2013-09-03 |
りんご5 | 2013-09-03 |
りんご| |
メロン通常の結合では表示されなかった
prod_id=3
（メロン）のデータが 表示された外部結合の種類
結合条件に合致しない行を表示するには OUTER JOIN （外部結合）を 使う
LEFT OUTER JOIN
：左側の表に対応行がなくてもOK
RIGHT OUTER JOIN
：右側の表に対応行がなくてもOK
FULL OUTER JOIN
：どちらの表に対応行がなくてもOK
「OUTER
」は省略可能
テーブルA LEFT JOIN
テーブルB ON
結合条件
対応行がなかったテーブル側の値はNULL [
注意] 外部結合の注意点
WHERE
句で結合条件を書けない
通常の結合でも外部結合でも、常にJOIN
・・・ON
結合条件 という構文を用 いればいちいち考えなくて済む Oracle Database
では独自の「(+)
」記法を使用して、外部結合でWHERE
句 に結合条件を書けるようになっているデータの集約
SELECT 文で、データを集約 ( 合計、平均、最大、最小などを計算 ) でき る
db1=> SELECT * FROM orders;
ord_id | ord_date | prod_id | qty ---+---+---+--- 1 | 2013-09-01 | 1 | 10 2 | 2013-09-02 | 2 | 5 3 | 2013-09-02 | 1 | 8 4 | 2013-09-03 | 2 | 3 5 | 2013-09-03 | 2 | 4 (5 rows)
db1=> SELECT max(qty), min(qty) FROM orders;
max | min ---+--- 10 | 3 (1 row)
列名ではなく、集約関数を指定
集約関数
複数のデータを受け取り、集約した値を返す
主な集約関数
集約関数 結果
count()
件数を返す。引数に*を指定した場合、NULLもカウントするmax()
最大値を返すmin()
最小値を返すsum()
合計値を返すavg()
平均値を返すmax --- 30 ord_id | ord_date | prod_id | qty
---+---+---+--- 1 | 2013-09-01 | 1 | 10 2 | 2013-09-02 | 2 | 5 3 | 2013-09-02 | 1 | 8 4 | 2013-09-03 | 2 | 3 5 | 2013-09-03 | 2 | 4
sum(qty)
GROUP BY句によるグループ単位の集約
指定した列の値に応じてグループ分けし、グループ単位で集約する
SELECT prod_id, sum(qty) FROM orders GROUP BY prod_id;
ord_id | ord_date | prod_id | qty ---+---+---+--- 1 | 2013-09-01 | 1 | 10 2 | 2013-09-02 | 2 | 5 3 | 2013-09-02 | 1 | 8 4 | 2013-09-03 | 2 | 3 5 | 2013-09-03 | 2 | 4
ord_id | ord_date | prod_id | qty ---+---+---+--- 1 | 2013-09-01 | 1 | 10 3 | 2013-09-02 | 1 | 8 ord_id | ord_date | prod_id | qty ---+---+---+--- 2 | 2013-09-02 | 2 | 5 4 | 2013-09-03 | 2 | 3
prod_id | sum ---+--- 1 | 18
prod_id, sum(qty)
prod_id | sum ---+--- 2 | 12
GROUP BY prod_id
db1=> SELECT prod_id, qty FROM orders
db1-> WHERE ord_id < 5 ORDER BY prod_id ; prod_id | qty
---+--- 1 | 10 1 | 8 2 | 5 2 | 3 (4 rows)
GROUP BY句とWHERE句の組み合わせ
db1=> SELECT prod_id, sum(qty) FROM orders db1-> WHERE ord_id < 5
db1-> GROUP BY prod_id;
prod_id | sum ---+--- 1 | 18 2 | 8 (2 rows)
WHERE 句による絞り込みの後、 GROUP BY によるグループ化＋集約
GROUP BYを指定しない実行結果と比較すると理解しやすいか
ドキュメント内
OSS-DB Exam Silver 技術解説無料セミナー
(ページ 133-144)