SQL select statement confusion

Question

Possible Duplicate:
What is the dual table in Oracle?

I have always wondered how does the query like:

select sysdate
from dual;

or

select 8
from dual;

do work.

As I'm used to select statement selects column/s value from a given table. But in the examples mentioned above table dual DOESN'T have sysdate or just '8' columns....

Could someone explain me how it works?

In a `SELECT` list, the things listed are _expressions_, not necessarily columns. So while most commonly you select the row value of a specific column (which is an expression identified by the column name), you can also select the result of a function call, a boolean comparison, arithmetic like `99 + 1`, a string literal like `'string literal'`, and anything else that evaluates to a single value. Does't have to be a column. — Michael Berkowski, Dec 01 '12 at 19:50

GolezTrol · Accepted Answer · 2022-10-18T20:52:21.200

You don't have to select columns. You can select expressions, which can by columns, constant values, function calls, calculations.... So you could select this from an employee table

select 
  e.nr,  -- column
  upper(e.name) as name, -- Function call to change the name to uppercase.
  e.age + 5 as age, -- expression with calculation with column value. 
  1  -- Just a value. Contains 1  for each row that the query returns.
from 
  employees e;

Note as name and as age. This is because the column name is lost once an expression contains more that just a column name. In that case, you can specify the name that you want the column to have in the query results. A similar trick is done for the table name itself. The alias e is just a shorthand. This is especially useful when using multiple tables (in a join). In this simple query, the table alias can be omitted altogether.

dual is just a special table that has 1 row exactly, and one column named dummy. It has many different uses which have been discussed before. From Oracle 23c onwards, from dual is optional, as it has been in MySQL for a while longer. You can simply write:

select
  1;

Brian · Answer 2 · 2012-12-03T17:12:34.993

It's just a table with one row. It was created by Charles Weiss of Oracle to provide a table for joining in internal views of the Oracle Data Dictionary.

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.

There are lot of neat things you can leverage dual for. I use it to generate a list of numbers. For example

SELECT LEVEL just_a_column
FROM dual
CONNECT BY LEVEL <= 365

Also note that as of 10g Oracle recognizes dual for its unique usages and that the logical reads / full table scans in 9i and earlier have been eliminated for applications. Upgrading to 10g had signficant performance benefits for applications that used dual. (e.g. Select sysdate from dual).

Explain Plan example: In 10g and above, dual is a magic table - if you select sysdate from dual, the plan would be:

sql> select sysdate from dual;

SYSDATE
---------
12-DEC-02


Execution Plan
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SQL select statement confusion

2 Answers2