Why can I select multiple columns from dual?

Question

When I execute this query:

select 2+2, 2+2, 2+2 from dual;

I get multiple columns but, as per definition, dual only has one row and one column. So what is the reason behind it?

Answer 1

The number of columns in a table has nothing to do with the number of columns in the projection of a SELECT statement. You can always add additional computed columns to the projection (at least practically... theoretically, you might eventually hit some sort of logical limit).

The fact that you are querying dual here is irrelevant. You can do exactly the same thing with any table. The projection in this query

SELECT e.*, 2+2 computed_one, 2+3 computed_two, 2+4 computed_three
  FROM emp e

returns all the columns in the emp table along with three additional computed columns computed_one, computed_two, and computed_three. The fact that dual always has exactly one row makes it useful when you want the result to have exactly one row but other than that, there is no logical difference whether you use dual or a single-row, single-column table that you create yourself.

Answer 2

• DUAL is a table automatically created by Oracle Database along with the data dictionary.
• DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X.
• Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once.
• Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table.

• In Oracle you just can not write a SQL statement like,

  SELECT 2+2;

• Instead you have to write,

  SELECT 2+2 from dual;

• This is just the SQL convention standard for Oracle. This is different for all databases, like SQL Server uses,

  SELECT 2+2;

Hope you are cleared with this.