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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityThe 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.
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.