SQL DUAL

What is the SQL DUAL table?

A built-in one-row, one-column dummy table used to run SELECT statements that do not need data from real tables.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DUAL: Oracle (native), MySQL, MariaDB (accepted for compatibility). Not required in PostgreSQL, SQL Server, SQLite, Snowflake, BigQuery, Redshift.

SQL DUAL Full Explanation

DUAL is a special table automatically created by Oracle (and emulated by MySQL and MariaDB) that always contains exactly one row and one column (named DUMMY in Oracle, holding the value 'X'). Because every valid SELECT in Oracle requires a FROM clause, DUAL provides a convenient placeholder when you only need to evaluate an expression, call a scalar function, or fetch a system value. The optimizer treats DUAL efficiently, returning results without accessing disk. In MySQL and MariaDB, DUAL is optional because those systems let you omit the FROM clause, yet the keyword is still accepted for cross-compatibility. PostgreSQL, SQL Server, and SQLite have no built-in DUAL, but you can achieve the same effect by omitting FROM or by selecting from VALUES(…) or system catalogs. DUAL should never be modified or dropped, and it is owned by SYS in Oracle. Excessively joining to DUAL in large queries can add unnecessary scans, so limit its use to single-row expressions.

SQL DUAL Syntax

SELECT expression_list
FROM DUAL;

SQL DUAL Parameters

Example Queries Using SQL DUAL

-- Return current date
SELECT SYSDATE AS current_datetime
FROM DUAL;

-- Simple arithmetic
SELECT 2 * 3 AS result
FROM DUAL;

-- Constant string
SELECT 'Galaxy Rocks' AS message
FROM DUAL;

Expected Output Using SQL DUAL

Each query returns a single row with the evaluated value: the first shows the current datetime, the second shows 6, and the third shows the literal text "Galaxy Rocks"

Use Cases with SQL DUAL

Evaluate constants or expressions when no real table is needed.
Call scalar functions such as SYSDATE, USER, or SYSTIMESTAMP.
Generate a single row for cross joins or pivot operations.
Provide a cross-dialect compatible FROM clause for scripts that must run on Oracle and MySQL.

Common Mistakes with SQL DUAL

Assuming DUAL exists in all databases – it does not in PostgreSQL, SQL Server, or SQLite.
Forgetting that in MySQL the FROM clause can be omitted entirely.
Selecting multiple rows from DUAL via Cartesian joins, causing performance issues.
Attempting to insert, update, or delete rows in DUAL (not allowed in most systems).

First Introduced In

Oracle Database V2 (circa 1980)

Frequently Asked Questions

Is DUAL the same in Oracle and MySQL?

Oracle requires the DUAL table for SELECT statements that lack real tables. MySQL accepts DUAL but also allows you to drop the FROM clause entirely.

How can I mimic DUAL in PostgreSQL?

PostgreSQL users can omit the FROM clause (e.g., `SELECT 1;`) or use `SELECT 1 AS col` from the implicit one-row result of VALUES, such as `SELECT 1;` or `SELECT * FROM (VALUES (1)) AS t(col);`.

Why does DUAL contain the value 'X'?

Oracle's original DUAL table had the single column DUMMY with the constant 'X'. The content is irrelevant; only the single row matters.

Can DUAL be dropped or altered?

In Oracle, DUAL is owned by SYS and should remain untouched. Dropping or altering it can break internal queries and application code.