DUAL Table in SQL

Uses of DUAL in SQL

There are several uses of the DUAL table in SQL. Take a look at the following uses of the DUAL table. 

• The dual in sql can help perform mathematical calculations and return the result.
• The dual in sql can be used to SELECT a constant value and return that constant value.
• The dual in sql is used to SELECT a value returned by a function by querying the table.
• The dual in sql can concatenate two or more strings and return the result.
• The dual in sql can generate unique values when used with the ROWNUM keyword.
• The dual in sql is used to create a join of two tables with the help of Join.

Various Operations Performed in DUAL

Several operations can be performed with the help of the DUAL table in SQL. It provides an easy and convenient way to perform operations.

Arithmetic operations

With the help of dual in sql, we can perform basic arithmetic operations. The operations include addition, subtraction, multiplication, and division.

String concatenation

With dual in sql, we can combine two or more strings using the concatenation operator.

Date and time operations

We can perform operations on date and time data, like extracting the current date or time using the SYSDATE function.

Conditional statements

Case statements can also be used in the dual in sql to perform conditional operations, such as returning a value based on a specific condition.

Aggregate functions

With the help of dual in sql, aggregate functions can be performed. The aggregate functions include COUNT, SUM, AVG, MIN, and MAX on a data set.

DDL and DML operations

DDL and DML operations like insert and update on the table can be performed using the dual in sql.

Create Cartesian Product

The dual in sql can create a Cartesian product with other tables.

Examples of DUAL in SQL

If you are using Oracle Databases, you don’t need to create the table DUAL. It’s built-in.  If you are using any other database, you need to create the table as follows:

CREATE TABLE DUAL (dummy VARCHAR(1));
INSERT INTO DUAL VALUES('X')

 

In the following examples, we will show various operations that can be performed on the DUAL table in ORACLE. We will also show their Relational Algebraic expressions. 

From Relational Algebra, we will use the Projection operation represented by π. It displays a specific column of the table, as mentioned in the query.

Syntax of Projection Operation:   

π<attribute list>(R)

Where <attribute list> forms a list of all the columns we want to retrieve or specify, and R specifies the table name.

Example: If we have a table named students with various columns like RNo, Name, City, etc.,

Then the projection operation  π_{RNO, NAME}(STUDENT) yields the following result.

Let us take a look at the examples.

Displaying the structure of the DUAL table

DESC DUAL;

The DESC command describes the overall structure of the DUAL table.

Performing SELECT* query on DUAL

SELECT* FROM DUAL;

In this example, SELECT* means that all the content of the table DUAL will be displayed.

Relational Algebra Expression

Relational Algebra Tree Expression

Performing SELECT query on DUAL

SELECT 'Coding Ninjas' FROM DUAL;

In this example, we selected ‘Coding Ninjas’ using the query SELECT and stored the result in DUAL.

Relational Algebra Expression

Relational Algebra Tree Expression

Performing Date & Time Operations

SELECT SYSDATE FROM DUAL;

 

SYSDATE function returns the current date on your database.

Relational Algebra Expression

Relational  Algebra Tree Expression

Performing arithmetic operations

SELECT 2+3 FROM DUAL;

Arithmetic operation of addition is performed, and the result is stored in the DUAL table. 

Relational Algebra Expression

Relational Algebra Tree Expression

Concatenating two strings

SELECT 'Coding' || ' ' || 'Ninjas' FROM DUAL;

In this example, firstly, the two words, ‘Coding’ and ‘Ninjas’ are concatenated using the ‘||’ operator. ‘||’ is the string concatenation operator. The result is stored in the dual table as ‘Coding Ninjas’.

Relational Algebra Expression

Relational Algebra Tree Expression

Conditional Statements

SELECT CASE
    WHEN 'Coding' = 'Ninjas'  THEN 'True'
    ELSE 'False'
END
FROM DUAL;

In this example, firstly, the conditional statement is executed. It returns ‘True’ if ‘Coding’= ‘Ninjas’. Else, it returns ‘False’. Hence, the result is ‘False’ because ‘Coding’ is not equal to ‘Ninjas’. The result ‘False’ is stored in the DUAL table.

Relational Algebra Expression

Relational Algebra Tree Expression

Must see, nmap commands

Frequently Asked Questions

Can we rename the DUAL Table in Oracle?

No, we cannot rename the DUAL table in Oracle as it is built-in and system defined.

Is the DUAL table available in other databases apart from ORACLE?

The DUAL table is specific to Oracle databases only and is not available in other database management systems.

How to get date from dual in SQL?

You can use the SYSDATE function to get the current date and time from dual in SQL. You can also try the SYSTIMESTAMP function to get the system date and time in the TIMESTAMP datatype.

What is dual used for in SQL?

In SQL, "DUAL" is a special one-row, one-column table present in Oracle databases. It is commonly used for evaluating expressions or functions when a table reference is required but not relevant to the operation, providing a placeholder for computations without the need for an actual table. 

What is dummy table in SQL?

A "dummy table" in SQL refers to a placeholder or fictitious table used in queries to structure the syntax correctly, often for situations where a table reference is required but the specific table's data is irrelevant to the query's purpose.

Conclusions

In conclusion, "DUAL" in SQL serves as a versatile tool in Oracle databases, acting as a one-row, one-column table. Its primary purpose lies in providing a convenient placeholder for expressions or functions, allowing users to execute queries and computations without the need for a dedicated, real-world table.

If you found this blog interesting and insightful, refer to similar blogs:

SQL Introduction

SQL Interview Questions

Update Query in MySQL

Natural Join in SQL

Refer to the Basics of C++ with Data Structure, DBMS, and Operating System by Coding Ninjas, and keep practicing on our platform Coding Ninjas Studio. You can check out the mock test series on code studio.

You can also refer to our Guided Path on Coding Ninjas Studio to upskill yourself in domains like Data Structures and Algorithms, Competitive Programming, Aptitude, and many more! Refer to the interview bundle if you want to prepare for placement interviews. Check out interview experiences to understand various companies' interview questions.

You can also start practicing SQL problems to land your dream job.

Give your career an edge over others by considering our premium courses!

Happy Learning!