SQL DUAL table
What is DUAL table?
The DUAL is special one row, one column table present by default in all Oracle databases. The owner of DUAL is SYS (SYS owns the data dictionary, therefore DUAL is part of the data dictionary.) but DUAL can be accessed by every user. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'. MySQL allows DUAL to be specified as a table in queries that do not need data from any tables. In SQL Server DUAL table does not exist, but you could create one.
The DUAL table was created by Charles Weiss of Oracle corporation to provide a table for joining in internal views.
See the following commands :
The following command displays the structure of DUAL table :
DESC DUAL;
Output:
Name Null? Type --------------------------- ------ DUMMY VARCHAR2(1)
The following command displays the content of the DUAL table :
-- Selecting all columns from the DUAL pseudo-table
SELECT *
-- This query is selecting all columns from a special table called DUAL
-- DUAL is a table in Oracle database that is typically used to perform calculations or as a placeholder
FROM DUAL;
Explanation:
- This SQL code selects all columns from the DUAL pseudo-table.
- The DUAL table is a special one-row, one-column table in Oracle database.
- It is often used in situations where a table reference is required by the syntax, but the actual data being queried does not matter.
- In this case, the query selects all columns from the DUAL table, but since DUAL only has one row and one column, it effectively returns a single row with that data.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
DUMMY ---------- X
The following command displays the number of rows of DUAL table :
-- Counting the number of rows in the DUAL pseudo-table
SELECT COUNT(*)
-- This query is counting the number of rows in the DUAL table
-- DUAL is a special table in Oracle database that typically has one row
FROM DUAL;
Explanation:
- This SQL code counts the number of rows in the DUAL pseudo-table.
- The SELECT statement uses the COUNT(*) function to count all rows in the DUAL table.
- Since DUAL is a special one-row table in Oracle database, this query effectively returns the count of rows, which is always 1.
- The output of this query will be a single row containing the count of rows in the DUAL table, which is always 1.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
COUNT(*)
----------
1
The following command displays the string value from the DUAL table :
-- Selecting the string 'ABCDEF12345' from the DUAL pseudo-table
SELECT 'ABCDEF12345'
-- This query selects the string 'ABCDEF12345' from the DUAL table
-- DUAL is a special table in Oracle database that typically has one row
FROM DUAL;
Explanation:
- This SQL code selects the string 'ABCDEF12345' from the DUAL pseudo-table.
- The SELECT statement retrieves the specified string literal from the DUAL table.
- Since DUAL is a special one-row table in Oracle database, this query effectively returns the specified string.
- The output of this query will be a single row containing the string 'ABCDEF12345'.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
'ABCDEF1234 ----------- ABCDEF12345
The following command displays the numeric value from the DUAL table :
-- Selecting the numeric value 123792.52 from the DUAL pseudo-table
SELECT 123792.52
-- This query selects the numeric value 123792.52 from the DUAL table
-- DUAL is a special table in Oracle database that typically has one row
FROM DUAL;
Explanation:
- This SQL code selects the numeric value 123792.52 from the DUAL pseudo-table.
- The SELECT statement retrieves the specified numeric literal from the DUAL table.
- Since DUAL is a special one-row table in Oracle database, this query effectively returns the specified numeric value.
- The output of this query will be a single row containing the numeric value 123792.52.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
123792.52 ---------- 123792.52
The following command tries to delete all rows from the DUAL table :
-- Deleting data from the DUAL pseudo-table (not supported in standard SQL)
DELETE FROM DUAL;
Explanation:
- This SQL code attempts to delete data from the DUAL pseudo-table.
- DUAL is a special one-row, one-column table in Oracle database.
- However, attempting to delete data from the DUAL table is not supported and will typically result in an error.
- DUAL is primarily used for syntax purposes, such as when a table reference is required by the syntax, but the actual data being queried does not matter.
- Since DUAL typically contains no meaningful data and is often used for calculations or as a placeholder, there is usually no need to delete data from it.
- Deleting from DUAL is not a common operation, and it is generally not recommended or necessary in most cases.
Output:
DELETE FROM DUAL
*
ERROR at line 1:
ORA-01031: insufficient privileges
The following command tries to remove all rows from the DUAL table :
-- Truncating (emptying) the DUAL pseudo-table (not supported in standard SQL)
TRUNCATE TABLE DUAL;
Explanation:
- This SQL code attempts to truncate (empty) the DUAL pseudo-table.
- DUAL is a special one-row, one-column table in Oracle database.
- However, attempting to truncate the DUAL table is not supported and will typically result in an error.
- Truncating a table removes all rows from it, but since DUAL typically contains only one row and is not intended for storage of meaningful data, truncating it is not a supported operation.
- DUAL is primarily used for syntax purposes, such as when a table reference is required by the syntax, but the actual data being queried does not matter.
- Truncating DUAL is not a common operation, and it is generally not recommended or necessary in most cases.
Note : The DELETE command is used to remove rows from a table. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. TRUNCATE removes all rows from a table. The operation cannot be rolled back.
Output:
TRUNCATE TABLE DUAL
*
ERROR at line 1:
ORA-00942: table or view does not exist
The following command select two rows from dual :
-- Selecting the 'dummy' column from the DUAL pseudo-table and combining the results with UNION ALL
SELECT dummy FROM DUAL
-- This query selects the 'dummy' column from the DUAL table and retrieves its value
-- DUAL is a special table in Oracle database that typically has one row and one column called 'dummy'
UNION ALL
-- Combining the results of the first SELECT query with the results of the second SELECT query using UNION ALL
SELECT dummy FROM DUAL;
-- This query selects the 'dummy' column from the DUAL table again to combine its results with the first SELECT query
Explanation:
- This SQL code selects the 'dummy' column from the DUAL pseudo-table twice and combines the results using UNION ALL.
- The first SELECT statement retrieves the value of the 'dummy' column from the DUAL table.
- The UNION ALL operator combines the results of the first SELECT query with the results of the second SELECT query.
- The second SELECT statement also retrieves the value of the 'dummy' column from the DUAL table.
- Since DUAL typically contains only one row and one column, this query effectively returns two rows with the same value in the 'dummy' column.
Relational Algebra Expression:
Relational Algebra Tree:
Output
DUMMY ---------- X X
Example - 1
You can also check the system date from the DUAL table using the following statement :
-- Selecting the current date and time using the SYSDATE function from the DUAL pseudo-table
SELECT sysdate FROM DUAL ;
-- This query selects the current date and time using the SYSDATE function
-- SYSDATE is a function in Oracle database that returns the current date and time
-- DUAL is a special table in Oracle database that typically has one row
-- The result of SYSDATE is returned as a single-column result set with one row, which is then selected from DUAL
Explanation:
- This SQL code retrieves the current date and time using the SYSDATE function from the DUAL pseudo-table.
- The SYSDATE function is used to obtain the current date and time in the Oracle database system.
- By selecting SYSDATE from DUAL, the result is returned as a single-column result set with one row.
- DUAL is a special table in Oracle database that typically contains one row and is often used for syntax purposes.
- The output of this query will be a single row with the current date and time.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
SYSDATE --------- 11-DEC-10
Example - 2
You can also check the arithmetic calculation from the DUAL table using the following statement :
-- Performing arithmetic operations on numeric literals and selecting the result from the DUAL pseudo-table
SELECT 15+10-5*5/5 FROM DUAL;
-- This query performs arithmetic operations on numeric literals and selects the result
-- DUAL is a special table in Oracle database that typically has one row
Explanation:
- This SQL code performs arithmetic operations on numeric literals and selects the result from the DUAL pseudo-table.
- The arithmetic operations include addition (+), subtraction (-), multiplication (*), and division (/).
- The order of operations (PEMDAS/BODMAS) is followed: multiplication and division are performed before addition and subtraction.
- The expression 15+10-5*5/5 is evaluated as follows:
- Multiplication: 5*5 equals 25
- Division: 25/5 equals 5
- Addition: 15+10 equals 25
- Subtraction: 25-5 equals 20
- Therefore, the result of the expression is 20.
- Since DUAL is a special one-row table in Oracle database, the result of the expression is selected and returned as a single-row result set from DUAL.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
15+10-5*5/5
-----------
20
Example - 3
Following code display the numbers 1..10 from DUAL :
-- Generating a sequence of numbers from 1 to 10 using hierarchical query and selecting the result from the DUAL pseudo-table
SELECT level
-- This query selects the pseudo-column LEVEL, which represents the level number in a hierarchical query
FROM DUAL
-- DUAL is a special table in Oracle database that typically has one row
CONNECT BY level <=10;
-- This clause generates a hierarchical query to produce a sequence of numbers from 1 to 10
Explanation:
- This SQL code uses a hierarchical query to generate a sequence of numbers from 1 to 10 and selects the result from the DUAL pseudo-table.
- The CONNECT BY clause is used to create a hierarchical query that generates the desired sequence.
- In this query, the pseudocolumn LEVEL is used to represent the level number in the hierarchical query.
- The condition level <= 10 specifies that the sequence should continue until the level reaches 10.
- As the hierarchical query generates each level from 1 to 10, the result set contains the numbers from 1 to 10.
- Since DUAL is a special one-row table in Oracle database, the result of the hierarchical query is selected and returned as a single-column result set from DUAL.
Output:
LEVEL
----------
1
2
3
4
5
6
7
8
9
10
Example - 4
In the following code, DUAL involves the use of decode with NULL.
-- Using the DECODE function to conditionally return a value based on comparison with NULL and selecting the result from the DUAL pseudo-table
SELECT decode(null,null,1,0)
-- This query uses the DECODE function to perform conditional logic
-- DECODE is an Oracle function that compares an expression with a series of values and returns a result based on the first match
FROM DUAL;
-- DUAL is a special table in Oracle database that typically has one row
Explanation:
- This SQL code uses the DECODE function to conditionally return a value based on comparison with NULL and selects the result from the DUAL pseudo-table.
- The DECODE function compares the first argument (null) with subsequent pairs of arguments.
- If the first argument matches the second argument in any pair, the corresponding third argument is returned.
- In this case, the first argument (null) matches the second argument (null), so the corresponding third argument (1) is returned.
- Therefore, the result of the query is 1.
- Since DUAL is a special one-row table in Oracle database, the result of the DECODE function is selected and returned as a single-column result set from DUAL.
Output:
DECODE(NULL,NULL,1,0)
---------------------
1
DUAL table : Oracle vs MySQL
We have already learned that DUAL is a special one row one column table. For Oracle, it is useful because Oracle doesn't allow statements like :
-- Performing arithmetic operations on numeric literals
SELECT 15+10-5*5/5;
-- This query performs arithmetic operations on numeric literals
Explanation:
- This SQL code performs arithmetic operations on numeric literals.
- The arithmetic operations include addition (+), subtraction (-), multiplication (*), and division (/).
- The order of operations (PEMDAS/BODMAS) is followed: multiplication and division are performed before addition and subtraction.
- The expression 15+10-5*5/5 is evaluated as follows:
- Multiplication: 5*5 equals 25
- Division: 25/5 equals 5
- Addition: 15+10 equals 25
- Subtraction: 25-5 equals 20
- Therefore, the result of the expression is 20.
Relational Algebra Expression:
Relational Algebra Tree:
Output:
SELECT 15+10-5*5/5
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
But the following command will execute (see the output of the previous example) :
-- Performing arithmetic operations on numeric literals and selecting the result from the DUAL pseudo-table
SELECT 15+10-5*5/5 FROM DUAL;
-- This query performs arithmetic operations on numeric literals and selects the result
-- DUAL is a special table in Oracle database that typically has one row
Explanation:
- This SQL code performs arithmetic operations on numeric literals and selects the result from the DUAL pseudo-table.
- The arithmetic operations include addition (+), subtraction (-), multiplication (*), and division (/).
- The order of operations (PEMDAS/BODMAS) is followed: multiplication and division are performed before addition and subtraction.
- The expression 15+10-5*5/5 is evaluated as follows:
- Multiplication: 5*5 equals 25
- Division: 25/5 equals 5
- Addition: 15+10 equals 25
- Subtraction: 25-5 equals 20
- Therefore, the result of the expression is 20.
- Since DUAL is a special one-row table in Oracle database, the result of the expression is selected and returned as a single-column result set from DUAL.
In case of MySQL the following command will execute :
-- Performing arithmetic operations on numeric literals
SELECT 15+10-5*5/5;
-- This query performs arithmetic operations on numeric literals
Explanation:
- This SQL code performs arithmetic operations on numeric literals.
- The arithmetic operations include addition (+), subtraction (-), multiplication (*), and division (/).
- The order of operations (PEMDAS/BODMAS) is followed: multiplication and division are performed before addition and subtraction.
- The expression 15+10-5*5/5 is evaluated as follows:
- Multiplication: 5*5 equals 25
- Division: 25/5 equals 5
- Addition: 15+10 equals 25
- Subtraction: 25-5 equals 20
- Therefore, the result of the expression is 20.
Output:
The following table shows the uses of dummy table in standard DBMS.
| DBMS | Dummy-table concept |
|---|---|
| MSSQL | No dummy-table concept. |
| MySQL | No dummy-table concept. |
| Oracle | Dummy-table : DUAL. |
| Informix | Since version 11.10, a dummy table has been included : sysmaster:sysdual |
| PostgreSQL | No dummy-table concept. |
| DB2 | Dummy-table : SYSIBM.SYSDUMMY1 |
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: SQL ordering output by column number with group by
Next: SQL Injection