帰ってきた！ 標準はあるにはあるが癖の多いSQL #18 - t_alias と c_alias にも癖が出る Tweet
Previously on Mac De Oracle
前回は、ANY_VALUE()、癖ないじゃんという話でした。
今日は、
t_alias と c_alias
なんて言うと、バイオハザードがちょっと浮かぶのは私だけか。。（t-virusっぽい響きがあるだけだがw）
ということで、唐突ですがw、
SCOTT@localhost:1521/freepdb1> select * from dept order by deptno;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
経過: 00:00:00.00
SCOTT@localhost:1521/freepdb1> select * from emp order by deptno,empno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 81-06-09 2450 10
7839 KING PRESIDENT 81-11-17 5000 10
7934 MILLER CLERK 7782 82-01-23 1300 10
7369 SMITH CLERK 7902 80-12-17 800 20
7566 JONES MANAGER 7839 81-04-02 2975 20
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7876 ADAMS CLERK 7788 87-05-23 1100 20
7902 FORD ANALYST 7566 81-12-03 3000 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7900 JAMES CLERK 7698 81-12-03 950 30
14行が選択されました。
上記の表が、Oracle database/MySQL/PostgreSQLにあったとします。というか、あります。
この時、2表を結合（外部結合でも内部結合でも良いです）する際、DEPT表をインラインビュー化し、そのビューに表エイリアスと列エイリアスを記述する場合、どのような構文が浮かびますか？
t_alias = 表エイリアス
c_alias = 列エイリアス
と言うのが今日の癖のテーマです:)
よくあるのは以下のな感じ. 列、表 の直後に列エイリアス、表エイリアスを記述する。見落としもつけ忘れも発生しにくく、可読性も良いと思います。
SELECT
e.empno
,e.ename
,d.department_name
,d.department_loc
FROM
emp e
INNER JOIN
(
SELECT
deptno
,dname AS department_name
,loc AS department_loc
FROM
dept
) d
ON
e.deptno = d.deptno
;
ところが、以下のような構文も可能なrdbmsもあります。はい。これが今日の癖！ www
FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )
参考） PostgreSQL 17 / 7.2. テーブル式 / 7.2.1.2. テーブルと列の別名
https://www.postgresql.jp/document/17/html/queries-table-expressions.html#QUERIES-TABLE-ALIASES
SELECT
e.empno
,e.ename
,d.department_name
,d.department_loc
FROM
emp e
INNER JOIN
(
SELECT
deptno
,dname
,loc
FROM
dept
) d (
deptno
, department_name
, department_loc
)
ON
e.deptno = d.deptno
;
まず最初は、PostgreSQLから。
マニュアルに明記されているくらいですから当然、どちらも問題なく使えます。
perftestdb=> SELECT
perftestdb-> e.empno
perftestdb-> ,e.ename
perftestdb-> ,d.department_name
perftestdb-> ,d.department_loc
perftestdb-> FROM
perftestdb-> emp e
perftestdb-> INNER JOIN
perftestdb-> (
perftestdb(> SELECT
perftestdb(> deptno
perftestdb(> ,dname
perftestdb(> ,loc
perftestdb(> FROM
perftestdb(> dept
perftestdb(> ) d (
perftestdb(> deptno
perftestdb(> , department_name
perftestdb(> , department_loc
perftestdb(> )
perftestdb-> ON
perftestdb-> e.deptno = d.deptno
perftestdb-> ;
empno | ename | department_name | department_loc
-------+--------+-----------------+----------------
7369 | SMITH | RESEARCH | DALLAS
7499 | ALLEN | SALES | CHICAGO
7521 | WARD | SALES | CHICAGO
7566 | JONES | RESEARCH | DALLAS
7654 | MARTIN | SALES | CHICAGO
7698 | BLAKE | SALES | CHICAGO
7782 | CLARK | ACCOUNTING | NEW YORK
7788 | SCOTT | RESEARCH | DALLAS
7839 | KING | ACCOUNTING | NEW YORK
7844 | TURNER | SALES | CHICAGO
7876 | ADAMS | RESEARCH | DALLAS
7900 | JAMES | SALES | CHICAGO
7902 | FORD | RESEARCH | DALLAS
7934 | MILLER | ACCOUNTING | NEW YORK
(14 rows)
perftestdb=> SELECT
perftestdb-> e.empno
perftestdb-> ,e.ename
perftestdb-> ,d.department_name
perftestdb-> ,d.department_loc
perftestdb-> FROM
perftestdb-> emp e
perftestdb-> INNER JOIN
perftestdb-> (
perftestdb(> SELECT
perftestdb(> deptno
perftestdb(> ,dname AS department_name
perftestdb(> ,loc AS department_loc
perftestdb(> FROM
perftestdb(> dept
perftestdb(> ) d
perftestdb-> ON
perftestdb-> e.deptno = d.deptno
perftestdb-> ;
empno | ename | department_name | department_loc
-------+--------+-----------------+----------------
7369 | SMITH | RESEARCH | DALLAS
7499 | ALLEN | SALES | CHICAGO
7521 | WARD | SALES | CHICAGO
7566 | JONES | RESEARCH | DALLAS
7654 | MARTIN | SALES | CHICAGO
7698 | BLAKE | SALES | CHICAGO
7782 | CLARK | ACCOUNTING | NEW YORK
7788 | SCOTT | RESEARCH | DALLAS
7839 | KING | ACCOUNTING | NEW YORK
7844 | TURNER | SALES | CHICAGO
7876 | ADAMS | RESEARCH | DALLAS
7900 | JAMES | SALES | CHICAGO
7902 | FORD | RESEARCH | DALLAS
7934 | MILLER | ACCOUNTING | NEW YORK
(14 rows)
次に、MySQL。
明確に記載されているドキュメントは見つけられなかった（知ってたら教えてw）のですが、以下のようにどちらも実行できます。
（ありがとうございました。篠田さんに教えていただきました）
MySQL 8.0 リファレンスマニュアル / SQL ステートメント / データ操作ステートメント / サブクエリー / 導出テーブル
mysql> SELECT
-> e.empno
-> ,e.ename
-> ,d.department_name
-> ,d.department_loc
-> FROM
-> emp e
-> INNER JOIN
-> (
-> SELECT
-> deptno
-> ,dname
-> ,loc
-> FROM
-> dept
-> ) d (
-> deptno
-> , department_name
-> , department_loc
-> )
-> ON
-> e.deptno = d.deptno
-> ;
+-------+--------+-----------------+----------------+
| empno | ename | department_name | department_loc |
+-------+--------+-----------------+----------------+
| 7782 | CLARK | ACCOUNTING | NEW YORK |
| 7839 | KING | ACCOUNTING | NEW YORK |
| 7934 | MILLER | ACCOUNTING | NEW YORK |
| 7369 | SMITH | RESEARCH | DALLAS |
| 7566 | JONES | RESEARCH | DALLAS |
| 7788 | SCOTT | RESEARCH | DALLAS |
| 7876 | ADAMS | RESEARCH | DALLAS |
| 7902 | FORD | RESEARCH | DALLAS |
| 7499 | ALLEN | SALES | CHICAGO |
| 7521 | WARD | SALES | CHICAGO |
| 7654 | MARTIN | SALES | CHICAGO |
| 7698 | BLAKE | SALES | CHICAGO |
| 7844 | TURNER | SALES | CHICAGO |
| 7900 | JAMES | SALES | CHICAGO |
+-------+--------+-----------------+----------------+
14 rows in set (0.00 sec)
mysql> SELECT
-> e.empno
-> ,e.ename
-> ,d.department_name
-> ,d.department_loc
-> FROM
-> emp e
-> INNER JOIN
-> (
-> SELECT
-> deptno
-> ,dname AS department_name
-> ,loc AS department_loc
-> FROM
-> dept
-> ) d
-> ON
-> e.deptno = d.deptno
-> ;
+-------+--------+-----------------+----------------+
| empno | ename | department_name | department_loc |
+-------+--------+-----------------+----------------+
| 7782 | CLARK | ACCOUNTING | NEW YORK |
| 7839 | KING | ACCOUNTING | NEW YORK |
| 7934 | MILLER | ACCOUNTING | NEW YORK |
| 7369 | SMITH | RESEARCH | DALLAS |
| 7566 | JONES | RESEARCH | DALLAS |
| 7788 | SCOTT | RESEARCH | DALLAS |
| 7876 | ADAMS | RESEARCH | DALLAS |
| 7902 | FORD | RESEARCH | DALLAS |
| 7499 | ALLEN | SALES | CHICAGO |
| 7521 | WARD | SALES | CHICAGO |
| 7654 | MARTIN | SALES | CHICAGO |
| 7698 | BLAKE | SALES | CHICAGO |
| 7844 | TURNER | SALES | CHICAGO |
| 7900 | JAMES | SALES | CHICAGO |
+-------+--------+-----------------+----------------+
14 rows in set (0.01 sec)
最後の、Oracle Database.
最後に出てきたと言うことは、はい、本日のオチですねw
マニュアル上でも記載はないので、その通りの結果です。
参考） Oracle Database 23 / SQL言語リファレンス / SELECT / table_reference::=
https://docs.oracle.com/cd/G11854_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2126863
SCOTT@localhost:1521/freepdb1> @t_alias_c_alias_test 1 SELECT
2 e.empno
3 ,e.ename
4 ,d.department_name
5 ,d.department_loc
6 FROM
7 emp e
8 INNER JOIN
9 (
10 SELECT
11 deptno
12 ,dname
13 ,loc
14 FROM
15 dept
16 ) d (
17 deptno
18 , department_name
19 , department_loc
20 )
21 ON
22* e.deptno = d.deptno
) d (
*
行16でエラーが発生しました。:
ORA-02000: ON or USINGキーワードがありません。 ヘルプ:
https://docs.oracle.com/error-help/db/ora-02000/
経過: 00:00:00.01
1 SELECT
2 e.empno
3 ,e.ename
4 ,d.department_name
5 ,d.department_loc
6 FROM
7 emp e
8 INNER JOIN
9 (
10 SELECT
11 deptno
12 ,dname AS department_name
13 ,loc AS department_loc
14 FROM
15 dept
16 ) d
17 ON
18* e.deptno = d.deptno
EMPNO ENAME DEPARTMENT_NAME DEPARTMENT_LOC
---------- ---------- ---------------- ---------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7788 SCOTT RESEARCH DALLAS
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7876 ADAMS RESEARCH DALLAS
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
7934 MILLER ACCOUNTING NEW YORK
14行が選択されました。
と言うことで、
帰ってきた！ 標準はあるにはあるが癖の多いSQL #18 - t_alias と c_alias にも癖が出る
ここまで。
普段使わないような構文に出会うと、ちょっと固まったりしますよねw
あ、そうそう、固まるといえば、
最近、クマとの接近遭遇が市街地でも多くなり、
散歩に行くにも、"クルマ"に気をつけてー、のついでに、”クマ”にも気をつけて〜っ
と言った方がいいかもねと言う程度に頻度が高くて、マジで散歩しにくいので、
なんとかして欲しいと思う、
万が一であったら、固まってしまいそう。。。。。
涼しくなりつつある、北の地より。
ではまた。
Enjoy SQL and SQLの癖w
関連エントリー
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）の おまけ - SQL*PlusのautotraceでSQL Analysis Reportが出力される！ (23ai〜)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（前編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #12 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（後編）ー 列エイリアスの扱いにも癖がある！
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖がある
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #14 - コメントを書く位置にも癖がでる (SQL Clientにも癖がある)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #15 - 実行計画でスカラー副問合せの見せ方にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #16 - FROM句のインラインビューのエイリアスにもクセがある（必須だったり、任意だったり）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #17 - ANY_VALUE() ってなかなかいいじゃん、癖無さそう！
| 固定リンク | 0
コメント