Dual table is used to select pseudo columns. it has one row and one column DUMMY which has a value X.
I have two questions
How is the dual able to give the value for example:
select sysdate from dual
will result in current datetime. How is this possible?
cant answer the fist item, but as for dual as far as i know its a useful "table" in oracle that allows for selecting all sorts of constants/functions while maintaining "proper" sql syntax (SELECT ... FROM ...) and the optimizer knows its a one line table. useful for things like sequences : "select seq.nextval from dual"
A pseudo-column is a function which returns a system generated value. sysdate is a function which returns the current datetime; rownum is a pseudo-column that returns the row number in a result set.
The nomenclature dates from the earlier days of Oracle, before we had PL/SQL. It just means that we can use these functions in the projection of a SELECT statement, just like the columns of a table. Nowadays we can write our own functions and use them in SQL statements without blinking, and so the phrase "pseudo-column" is a tad confusing.
The feature which distinguishes a function from a pseudo-column is that the pseudo-column returns a different value for each row in the resultset whereas a function returns the same value (unless some column in the table is passed as a parameter to derive the value).
Dual is another venerable slice of Oracle history. It is a table which contains one row, and which the database knows contains one row. So the select statement you quote is just saying "give me the current datetime". It is functionally equivalent to
select sysdate
from emp
where rownum = 1
/
In PL/SQL the select from dual is nugatory. We can just code this:
l_date := sysdate;
One common use for DUAL used to be getting the next value of a sequence in a trigger. Since 11g we can do ...
:new.id := my_seq.nextval;
Under the covers this still executes select my_seq.nextval into :new.id from dual;
SQL has a number of built-in functions which don't need parentheses after them to invoke them. One such function in Oracle is SYSDATE.
Remember, if you have a table, a SELECT statement with no restriction condition (WHERE clause) normally returns one row of data for each row in the table. So, given a table:
CREATE TABLE Ex1(Dummy CHAR(10) NOT NULL);
INSERT INTO Ex1 VALUES('Abacus');
INSERT INTO Ex1 VALUES('Sedentary');
INSERT INTO Ex1 VALUES('Caucasus');
Running the SELECT statement:
SELECT Dummy FROM Ex1;
will return 3 rows. Now, suppose I write the statement as:
SELECT 'ABC', Dummy, SYSDATE FROM Ex1;
This will also return 3 rows:
If I omit the Dummy column:
SELECT 'ABC', SYSDATE FROM Ex1;
I get:
And if I omit the string literal:
SELECT SYSDATE FROM Ex1;
I get:
And I delete two rows and rerun the query, I get:
DELETE FROM Ex1 WHERE Dummy > 'B';
SELECT SYSDATE FROM Ex1;
I get:
Because there's just the one row of data in the table Ex1.
Nominally, I could do:
UPDATE Ex1 SET Dummy = 'X';
RENAME TABLE Ex1 AS Dual;
Of course, you can't do that - I'm not sure whether Oracle supports a RENAME TABLE statement and it probably wouldn't let you rename your table so it could be confused with the built-in DUAL table. But conceptually, the table Ex1 with a single row in it is isomorphic with DUAL.
Unless Oracle has a specific special meaning for the term, then a pseudo-column is a column that appears to be part of the table but that is not actually stored as data in the table. A classic example is a row number:
SELECT ROWNUM, * FROM SomeTable
The output appears to have a column ROWNUM (ROWID in Informix, with which I'm most familiar) but that is not directly stored in the DBMS. Different DBMS have other different pseudo-columns, for different purposes.
It is sometimes difficult to distinguish between a pseudo-column and a function.