SQL入門 第７章補足
7-1 グループ関数
行をひとまとめにして計算し、結果を１件だけ返す関数。
★COUNT関数
①COUNT（＊）
指定された条件を満たすすべての行を数える。
（例）
EMP表の行数を表示
SELECT COUNT（＊） FROM EMP；
結果
COUNT(*)
----------
14
②COUNT（列名）
指定された条件を満たし、指定の列名の値がNULL以外の行を数える。
（例１）
COMM列がNULLでない行の数を表示
SELECT COUNT（COMM） FROM EMP；
結果
COUNT(COMM)
-----------
4
（例２）
EMP表の行数とCOMM列がNULLでない行の数を表示
SELECT COUNT（＊），COUNT（COMM） FROM EMP；
COUNT(*) COUNT(COMM)
---------- -----------
14 4
（例３）
EMP表のなかでDEPTNOが20のである行の数を表示
SELECT COUNT（＊） FROM EMP
WHERE DEPTNO ＝ ２０；
結果
COUNT(*)
----------
5
★MAX関数
★MIN関数
MAX（列名）
MIN（列名）
指定した列中の最大値（MAX）、最小値（MIN）を表示。
列名は数値型、文字型、日付型を使用可能。
（例１）
EMP表中でのSALの最大値と最小値を表示
SELECT MAX（SAL），MIN（SAL） FROM EMP；
結果
MAX(SAL) MIN(SAL)
---------- ----------
5000 800
（例２）
EMP表中で、SALが最大の社員のENAMEとSALを表示
SELECT ENAME，MAX（SAL） FROM EMP；
結果
エラー行: 1: エラーが発生しました。
ORA-00937: 単一グループのグループ関数ではありません。
※グループを指定していない列と、グループ関数を同時に指定するとエラーになります。対処方法は７－２グループ分けで紹介します。
★AVG関数
★SUM関数
AVG（列名）
列の値の平均を求める。数値型のデータ列のみ使用可能。
列中にNULL値を含むデータがある場合NULL値は無視され、NULLを含まないデータの件数を分母として平均が計算される。
SUM（列名）
列の値の合計を求める。数値型のデータ列のみ使用可能。
列中にNULL値を含むデータがある場合NULL値は無視され、NULLを含まないデータの合計が計算される。
（例）
SELECT AVG（COMM），SUM（COMM） FROM EMP；
結果
AVG(COMM) SUM(COMM)
---------- ----------
550 2200
7-2 グループ分け
すべての行を条件に基づいてグループ分けするGROUP BY句について。
グループ関数と列とをあわせて出力するとエラーになります。これを回避するためにGROUP BY句を使用します。
（例１）
EMP表中で、DEPTNOとSALの合計を表示
SELECT DEPTNO，SUM（SAL） FROM EMP；
結果
エラー行: 1: エラーが発生しました。
ORA-00937: 単一グループのグループ関数ではありません。
（例２）
SELECT DEPTNO，SUM（SAL） FROM EMP
GROUP BY DEPTNO；
（結果）
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
DEPTNOごとの合計が表示されます。
GROUP BY句は ○○ごとの○○という場合に使用します。
7-3 グループの選択（絞り込み）
教科書訂正
■同じ問い合わせの中に・・・
SELECT job, COUNT(*)
FROM emp
WHERE SUM(sal) > 8000 ←WHERE句にグループ関数は使用できません。
:
:
GROUP BY句によってグループ分けされたデータを対象に、さらに別の条件を付けて絞り込むHAVING句について。
■HAVING句
必ずGROUP BY句といっしょに使用する。
（例）
SELECT DEPTNO，SUM（SAL） FROM EMP
GROUP BY DEPTNO
HAVING SUM（SAL） ＞ １００００；
DEPTNOごとの給与の合計を表示する。ただし、給与の合計が１００００より大きいグループのみを出力する。
WHERE句で絞り込んだものをグループ化してさらにHAVING句で絞り込むことも可能。
（例）
SELECT JOB，COUNT（＊） FROM EMP
WHERE SAL ＞ ８００
GROUP BY JOB
HAVING COUNT（＊） ＞ ２；
EMP表から給与が８００より多い社員の中から、各職種別の該当社員が２人より多い職種の件数を表示する。
７－４に入る前に
Oracle9i対応オラクルマスター試験でのSQLの分類について
|
標準仕様
|
オラクル独自仕様
|
|
ＳＱＬ１９９９
|
Oracle8i
|
Oracle9i
|
|
内部結合
・クロス結合
・自然結合
・USING句結合
・ON句結合
外部結合
・左側外部結合
・右側外部結合
・完全外部結合
・ON句外部結合
|
直積結合
等価結合
非等価結合
外部結合
同一表結合（自己結合）
※直積結合はどの分類にも当てはまらないので１つの分類とした。
|
単純結合（直積結合を含む）
外部結合
内部結合
※厳密に言えば直積は単純結合には含まれまい。（単純結合には結合条件が必要であるため。）
|
Oracle独自仕様のSQL文とSQL1999仕様のSQL文では構文がかなり違うので注意が必要。
この補足資料では結合のSQLの例にOracle独自仕様なのかSQL1999仕様なのかを次のように明記する。
<ORA独自>・・・Oracle独自仕様
<SQL1999>・・・SQL1999仕様
・等価結合
「互いの表の値が等しいもの」という条件で結合することを等価結合という。
結合条件演算子には「＝」を用いる。
<例>
SELECT ENAME， DNAME FROM EMP，DEPT
WHERE EMP．DEPTNO = DEPT．DEPTNO
<参考>
SQL1999では自然結合、USING句結合およびON句結合で条件に「=」を使用した場合がこれに該当する。
Oracle9iでは単純結合という表現に変更された模様。
・非等価結合
等価結合でないものを非等価結合という。すなわち、「＝以外」の演算子による結合条件が指定された結合である。
直接対応する列がない表同士を結合する。
<例>
EMP表とは直接対応する列がないSALGRADE表を結合する。
SELECT E．ENAME，
E．SAL， S．GRADE
FROM EMP E， SALGRADE S
WHERE E．SAL BETWEEN S．LOSAL AND S．HISAL
<参考>
SQL1999では、条件に「=」を用いないON句結合がこれに該当する。
Oracle9iでは単純結合という表現に変更された模様。
★SQL1999について
Oracle９ｉから、SQLの標準仕様である「SQL1999」構文に関する問題が出題されるようになった。
詳細はOracle9i入門SQL編差分資料を参照のこと。
7-4 表の結合１（単純結合）
２つ以上の表を結合して表示する手順について。
SQL文の例は次の表をもとに記述してあります。
|
商品表
|
|
商品コード
|
商品名
|
分類コード
|
単価
|
|
10001
|
オラクルＴシャツ白
|
01
|
1200
|
|
10002
|
オラクルＴシャツ赤
|
01
|
1200
|
|
10003
|
オラクルパラソル
|
02
|
4000
|
|
10004
|
オラクルめざまし
|
|
3000
|
|
10005
|
オラクルクッキー
|
04
|
850
|
|
10006
|
ハイディぬいぐるみ
|
03
|
3500
|
|
分類表
|
|
分類コード
|
分類
|
|
01
|
衣類
|
|
02
|
レジャー用品
|
|
03
|
玩具
|
|
04
|
食品
|
|
05
|
文具
|
|
06
|
バッグ
|
★単純結合（クロス結合）
条件などを指定せず、単純に２つのテーブルにあるデータを結合する方法。
（例）<ORA独自>
SELECT 商品表．商品名，分類表．分類 FROM 商品表，分類表；
※複数の表を結合する場合、同じ項目名が複数存在する場合はどの表の項目かをはっきりさせるために
「表名．項目名」と記述する。
（例）<SQL1999>
SELECT 商品表．商品名，分類表．分類 FROM 商品表 CROSS JOIN 分類表；
（結果）
商品表６件×分類表６件＝３６件のデータが返されます。
これを直積といいます。
直積は時として膨大な量の結果を表示する場合があるのでできる限り条件を指定すること。
|
商品名
|
分類
|
|
オラクルＴシャツ白
|
衣類
|
|
オラクルＴシャツ赤
|
衣類
|
|
オラクルパラソル
|
衣類
|
|
オラクルめざまし
|
衣類
|
|
オラクルクッキー
|
衣類
|
|
ハイディぬいぐるみ
|
衣類
|
|
オラクルＴシャツ白
|
レジャー用品
|
|
オラクルＴシャツ赤
|
レジャー用品
|
|
オラクルパラソル
|
レジャー用品
|
|
オラクルめざまし
|
レジャー用品
|
|
オラクルクッキー
|
レジャー用品
|
|
ハイディぬいぐるみ
|
レジャー用品
|
|
オラクルＴシャツ白
|
玩具
|
|
オラクルＴシャツ赤
|
玩具
|
|
オラクルパラソル
|
玩具
|
|
：
|
：
|
|
オラクルクッキー
|
バッグ
|
|
ハイディぬいぐるみ
|
バッグ
|
★単純結合（SQL1999では内部結合）
指定した条件に合うデータのみ結合して返す結合。
（例）＜ORA独自＞
SELECT 商品表．商品名，分類表．分類 FROM 商品表，分類表
WHERE 商品表．分類コード ＝ 分類表．分類コード；
（例）＜SQL1999：自然結合＞
SELECT 商品表．商品名，分類表．分類 FROM 商品表 NATURAL JOIN 分類表；
（例）＜SQL1999：USING句結合＞
SELECT 商品表．商品名，分類表．分類 FROM 商品表 JOIN 分類表
USING（分類コード）；
（例）＜SQL1999：ON句結合＞
SELECT 商品表．商品名，分類表．分類 FROM 商品表 JOIN 分類表
ON 商品表．分類コード ＝ 分類表．分類コード；
（結果）
商品表の分類コードと分類表の商品コードが等しいデータが表示される。
|
商品名
|
分類
|
|
オラクルＴシャツ白
|
衣類
|
|
オラクルＴシャツ赤
|
衣類
|
|
オラクルパラソル
|
レジャー用品
|
|
オラクルクッキー
|
食品
|
|
ハイディぬいぐるみ
|
玩具
|
7-5 表の結合２（外部結合）
２つ以上の表を結合して表示する手順について。
SQL文の例は次の表をもとに記述してあります。
|
商品表
|
|
商品コード
|
商品名
|
分類コード
|
単価
|
|
10001
|
オラクルＴシャツ白
|
01
|
1200
|
|
10002
|
オラクルＴシャツ赤
|
01
|
1200
|
|
10003
|
オラクルパラソル
|
02
|
4000
|
|
10004
|
オラクルめざまし
|
|
3000
|
|
10005
|
オラクルクッキー
|
04
|
850
|
|
10006
|
ハイディぬいぐるみ
|
03
|
3500
|
|
分類表
|
|
分類コード
|
分類
|
|
01
|
衣類
|
|
02
|
レジャー用品
|
|
03
|
玩具
|
|
04
|
食品
|
|
05
|
文具
|
|
06
|
バッグ
|
★外部結合
指定した条件に合わないデータも返す結合。
（例１）＜ORA独自＞
SELECT 商品表．商品名，分類表．分類 FROM 商品表，分類表
WHERE 商品表．分類コード ＝ 分類表．分類コード（+）；
（例１’）＜SQL1999：左側外部結合＞
SELECT 商品表．商品名，分類表．分類
FROM 商品表 LEFT OUTER JOIN 分類表
ON 商品表．分類コード ＝ 分類表．分類コード；
（結果）
商品表の分類コードと分類表の商品コードが等しいデータと、条件に合わない商品表のデータが表示される。
|
商品名
|
分類
|
|
オラクルＴシャツ白
|
衣類
|
|
オラクルＴシャツ赤
|
衣類
|
|
オラクルパラソル
|
レジャー用品
|
|
オラクルクッキー
|
食品
|
|
ハイディぬいぐるみ
|
玩具
|
|
オラクルめざまし
|
|
（例２）＜ORA独自＞
SELECT 商品表．商品名，分類表．分類 FROM 商品表，分類表
WHERE 商品表．分類コード（+） ＝ 分類表．分類コード；
（例２’）＜SQL1999：右側外部結合＞
SELECT 商品表．商品名，分類表．分類
FROM 商品表 RIGHT OUTER JOIN 分類表
ON 商品表．分類コード ＝ 分類表．分類コード；
（結果）
商品表の分類コードと分類表の商品コードが等しいデータと、条件に合わない分類表のデータが表示される。
|
商品名
|
分類
|
|
オラクルＴシャツ白
|
衣類
|
|
オラクルＴシャツ赤
|
衣類
|
|
オラクルパラソル
|
レジャー用品
|
|
オラクルクッキー
|
食品
|
|
ハイディぬいぐるみ
|
玩具
|
|
|
バッグ
|
（例３）＜ORA独自＞
SELECT 商品表．商品名，分類表．分類 FROM 商品表，分類表
WHERE 商品表．分類コード（+） ＝ 分類表．分類コード
ＵＮＩＯＮ
SELECT 商品表．商品名，分類表．分類 FROM 商品表，分類表
WHERE 商品表．分類コード ＝ 分類表．分類コード（+）；
例１と例２を組合せて表示したい場合は「ＵＮＩＯＮ」を用いて２つのＳＱＬを組み合わせる。
これを「完全外部結合」という。
WHERE条件の両辺に（＋）をつけてもエラーとなる。（このような構文は存在しない）
（例３’）＜SQL1999：完全外部結合＞
SELECT 商品表．商品名，分類表．分類
FROM 商品表 FULL OUTER JOIN 分類表
ON 商品表．分類コード ＝ 分類表．分類コード；
（結果）
|
商品名
|
分類
|
|
オラクルＴシャツ白
|
衣類
|
|
オラクルＴシャツ赤
|
衣類
|
|
オラクルパラソル
|
レジャー用品
|
|
オラクルクッキー
|
食品
|
|
ハイディぬいぐるみ
|
玩具
|
|
オラクルめざまし
|
|
|
|
バッグ
|
7-6 表の結合３（同一表結合）
同じ表（その表自身）を結合して表示する手順について。
（例）
EMP表から社員名とその上司の名前を表示したい場合。
①EMP表から上司の社員番号を調べる。
②EMP表から社員名を調べる。
①②ともにEMP表を使う。このような場合、参照する表も参照される表も同じである。
表の実体（EMP表）は１つしか存在しないが、「参照する側」と「参照される側」の２つが存在すると考える。
参照する表と参照される表が同じ表であり区別がつかないため、ニックネームをつけて識別する。
＜ORA独自＞
SELECT WORKER．ENAME， MANAGER．ENAME
FROM EMP WORKER， EMP MANAGER
WHERE WORKER．MGR ＝ MANAGER．EMPNO
WORKER、MANAGERがEMP表のニックネーム。
＜SQL1999＞
SELECT WORKER．ENAME， MANAGER．ENAME
FROM EMP WORKER INNER JOIN EMP MANAGER
ON WORKER．MGR ＝ MANAGER．EMPNO
7-7 副問合せ（サブクエリー）
SQL文の中にSQL文を記述する手順について。
（例）EMP表から給与が全体の平均より多い社員のENAME、SALを表示する。
１．今までの場合
①全体の給与の平均を求める。
SELECT AVG（SAL） FROM EMP；
②①の結果を使い、給与が平均より多い社員のENAME、SALを表示する。
SELECT ENAME，SAL FROM EMP
WHERE SAL ＞ ○○○○；
○の部分に①の結果を入れる。
２．副問合せを使用した場合
SELECT ENAME，SAL FROM EMP
WHERE SAL ＞ （SELECT AVG（SAL） FROM EMP）；
（ ）内のSQL文が先に実行され、平均が求められる。その結果に基づいてもうひとつのSQL文が実行される。
副問い合わせの種類
①単一行を戻す副問合せ（グループ関数を使用したときなど）
②複数行を戻す副問合せ
比較演算子に注意
単一行を戻す副問合せに対しては＞，＜，＞＝，＜＝．＜＞，！＝を使用する。
（複数行戻る可能性がある場合は「ＩＮ」を使ってエラー回避可能）
複数行を戻す副問合せに対してはＩＮ，ＡＮＹ，ＡＬＬ，ＮＯＴ ＩＮを使用する。