SQLのdualについてまとめています。
dualはfrom省略できないデータベース用のダミーテーブル
dualは、Oracleで使われるテーブル。SQLでfrom句を省略できないOracleで使われるダミー表です。
Oracleで、select文によるdualテーブルの全件取得を実行すると以下が出力されます。
select * from dual; DUMMY ----- X
例えば、以下のSQLは、Oracleで現在の日付を取得します。fromを省略すると、エラーが出力されます。
select SYSDATE from dual; SYSDATE ----- 23-APR-20 select SYSDATE; ORA-00923: FROM keyword not found where expected
なお、MySQLやSQL Server、PostgreSQLなどではdual指定は不要です。指定すると、dualテーブルが存在しないというエラーが出力されます。（※ただし、MySQLは互換性確保のため、from dualと記述してもエラーが出力されないようになってまいます。）
OracleのSQLコードをなるべく修正せずに他のデータベースに流用するため、ダミーのdualテーブルを作成するケースもあります。
以下のSQLは、postgreSQLでdualテーブルを作成し、現在時刻をselectで取得するサンプルです。
$ psql -U postgres
psql (9.6.17)
Type "help" for help.
postgres=#
postgres=#
postgres=# select now() from dual;
ERROR: relation "dual" does not exist
LINE 1: select now() from dual;
^
postgres=# select now();
now
------------------------------
2020-04-23 07:54:31.63905+00
(1 row)
postgres=# create table dual (dummy varchar(1));
CREATE TABLE
postgres=# insert into dual (dummy) values ('X');
INSERT 0 1
postgres=# select * from dual;
dummy
-------
X
(1 row)
postgres=# select now() from dual;
now
-------------------------------
2020-04-23 07:56:31.872746+00
(1 row)
dualテーブルを使ったSQLサンプル
dualテーブルは、ダミーデータが一件だけ格納されたテーブルです。createテーブルで作成しなくても存在しているテーブルで、基本的に他の一般テーブルと変わりありません。
dualテーブルに対して、様々なSQLを実行してみましょう。
SQL> desc dual Name Null? Type ----------------------------------------- -------- ---------------------------- DUMMY VARCHAR2(1) SQL> select * from dual; DUMMY ----- X SQL> select count(*) from dual; COUNT(*) ---------- 1 SQL> select 'abcabcabcabc' from dual; 'ABCABCABCABC' ------------------ abcabcabcabc SQL> select 3434334 from dual; 3434334 ---------- 3434334 SQL> truncate table dual; truncate table dual * ERROR at line 1: ORA-00942: table or view does not exist SQL> delete from dual; delete from dual * ERROR at line 1: ORA-01031: insufficient privileges
- dualは、カラム名DUMMYにvarchar2(1)のデータ型が設定されている。格納されているデータは「X」。
- データ件数は1件
- selectで文字列や数値を指定すると、指定した値がそのままカラム名として取得される。
- truncate tableを実行しようとすると、テーブルまたはビューが存在しないエラーが出力される。
- dualテーブルをdeleteしようとすると、権限がない旨のエラーが出力される
truncateやdelete実行時にはエラーが出力され、誤って消せないようになっているようです。
dualを使ってunion結合で複数行を生成するサンプルコード
dualはダミーデータが一件だけ格納されているテーブルですが、unionでselect結果を結合し、サブクエリを使うことで、擬似的にテーブルのように扱うことが可能です。
以下のSQLは、1を取得するselect文をUNIONで10件ぶん結合し、サブクエリのrownumを取得することで1～10までの数を取得しています。
select rownum from ( SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual); ROWNUM ----- 1 2 3 4 5 6 7 8 9 10
テスト用にテーブルを使いたいけど、createテーブルをしたくないという場合などに、使えそうです。
参考）DUALだけで多行を生成するSQLクエリをいろいろ挙げてみる(Oracle) – Qiita
まとめ
- dualは、Oracleで用意されているダミー表。from指定しないとエラーが出るため用意されている。
- 他の主要なデータベース（SQL Server、MySQL、PostgreSQLなど）では、from指定を省略できるため、dualは不要
- 他のデータベースでもdualテーブルを作成すれば、dualを使ったOracleのSQLを流用可能。
- dualは、ダミーデータが一件のみ格納されていて、truncateやdeleteはできない。
- unionやサブクエリを利用し、dualを使って複数行データを生成することが可能。
