SQLのdualは、from省略ができないOracle用のダミー表

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を使って複数行データを生成することが可能。