今回はOracleのSELECT文の評価順序について調査、検証しました。
SELECT文は列別名を指定できる句がありますが、一部の句(WHERE句、GROUP BY句など)では列別名を指定できません。
理由はSELECT文で指定する句の評価順序があるためです。
SELECT文で指定する句は下記の順序で評価されます。
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
列別名を定義するのはSELECT句であるため、SELECT句より前に評価される句では列別名が使用できません。
評価順序のルールを守らないと解析エラーが発生する、というのを検証して実際の動きを見てみます。
■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)
■前提
下記2テーブルを検証に使用します
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------------- --------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
SQL> select * from dept;
DEPTNO DNAME LOC
---------- --------------- ---------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
■検証パターン
①WHERE句に列別名を指定
②ORDER BY句に列別名を指定
③GROUP BY句に列別名を指定
④HAVING句に列別名を指定
⑤(番外編1)ON句に列別名を指定
⑥(番外編2)WHERE句に集計関数を指定
■検証
①WHERE句に列別名を指定
WHERE句に列別名を指定した状態でSELECT文を実行した場合の動きを検証します
【検証手順】
1. WHERE句に列別名を指定した状態でSELECT文を実行
【作業ログ】
SQL> select empno as eno, ename, deptno from emp where eno = 7369;
select empno as eno, ename, deptno from emp where eno = 7369
*
行1でエラーが発生しました。:
ORA-00904: "ENO": 無効な識別子です。
SQL> --改善案
SQL> select empno as eno, ename, deptno from emp where empno = 7369;
ENO ENAME DEPTNO
---------- --------------- ----------
7369 SMITH 20
②ORDER BY句に列別名を指定
ORDER BY句に列別名を指定した状態でSELECT文を実行した場合の動きを検証します
【検証手順】
1. ORDER BY句に列別名を指定した状態でSELECT文を実行
【作業ログ】
SQL> select empno as eno, ename, deptno from emp order by eno;
ENO ENAME DEPTNO
---------- --------------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7839 KING 10
7844 TURNER 30
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
★列別名は指定できる
③GROUP BY句に列別名を指定
GROUP BY句に列別名を指定した状態でSELECT文を実行した場合の動きを検証します
【検証手順】
1. GROUP BY句に列別名を指定した状態でSELECT文を実行
【作業ログ】
SQL> select deptno as dno, count(*) from emp group by dno;
select deptno as dno, count(*) from emp group by dno
*
行1でエラーが発生しました。:
ORA-00904: "DNO": 無効な識別子です。
SQL> --改善案
SQL> select deptno as dno, count(*) from emp group by deptno;
DNO COUNT(*)
---------- ----------
30 6
20 3
10 3
④HAVING句に列別名を指定
HAVING句に列別名を指定した状態でSELECT文を実行した場合の動きを検証します
【検証手順】
1. HAVING句に列別名を指定した状態でSELECT文を実行
【作業ログ】
SQL> select deptno as dno, count(*) as dcnt from emp group by deptno having dcnt > 3;
select deptno as dno, count(*) as dcnt from emp group by deptno having dcnt > 3
*
行1でエラーが発生しました。:
ORA-00904: "DCNT": 無効な識別子です。
SQL> --改善案
SQL> select deptno as dno, count(*) as dcnt from emp group by deptno having count(*) > 3;
DNO DCNT
---------- ----------
30 6
⑤(番外編1)ON句に列別名を指定
検証の対象の句には上げていませんでしたが、表の結合でON句もよく使用するため、ON句に列別名を指定した状態でSELECT文を実行した場合の動きも検証しようと思います
【検証手順】
1. ON句に列別名を指定した状態でSELECT文を実行
【作業ログ】
SQL> select e.empno as eno, e.ename, d.deptno as dno, d.dname from emp e inner join dept d on e.eno = d.dno;
select e.empno as eno, e.ename, d.deptno as dno, d.dname from emp e inner join dept d on e.eno = d.dno
*
行1でエラーが発生しました。:
ORA-00904: "D"."DNO": 無効な識別子です。
★ON句も列別名は使用できない。SELECT句より前に評価されることが分かる(実際はFROM句とWHERE句の間で評価されるようです)
SQL> --改善案
SQL> select e.empno as eno, e.ename, d.deptno as dno, d.dname from emp e inner join dept d on e.deptno = d.deptno;
ENO ENAME DNO DNAME
---------- --------------- ---------- ---------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
⑥(番外編2)WHERE句に集計関数を指定
こちらは列別名を指定するパターンではありませんが、条件を指定する句であるWHERE句とHAVING句のうち、なぜWHERE句では集計関数が指定できないのかも検証しようと思います
【検証手順】
1. WHERE句に集計関数を指定した状態でSELECT文を実行
【作業ログ】
SQL> select empno as eno, ename, deptno from emp where count(*) > 3;
select empno as eno, ename, deptno from emp where count(*) > 3
*
行1でエラーが発生しました。:
ORA-00934: ここではグループ関数は使用できません。
★WHERE句が評価される順番はGROUP BY句より前のため、WHERE句では集計関数を使用できない
■おわりに
整理すると下記のようになります。
ORACLE MASTERのSQLの勉強などで「列別名を使用できる句、できない句が覚えられない」と言われることが結構ありますが、SELECT文の評価順序を意識すると覚えやすいかと思います。
