Updated February 27, 2023
Introduction to Oracle CTE
CTE ORACLE is a simple query to simplify the different classes of SQL queries as the derived table concept was just not suitable can be defined as a named temporary result set which can only exist within the scope of a single statement (In this case statement here means SELECT and also DML statements like INSERT and UPDATE) and it can be referenced later within that particular statement multiple times as desired by the developer.
Syntax
Let us now look at the syntax of the Oracle common Table Expression below.
With [CTE_NAME (column1, column2,…)]
AS
(
CTE QUERY
)
Parameters
- CTE_NAME: It refers to the name we want to give the common table expression
- column1: It refers to the column or column names of the table.
- CTE query: It actually refers to the query or the statement.
How CTE works in Oracle?
As we have already seen in the earlier section of this article about the definition of the CTE. In this section, we are going to discuss about how it works in database. So for the database to understand we define it using WITH clause before the starting of the CTE query statement. Here statement means SELECT, INSERT, UPDATE, DELETE. We can add more than one CTE with a single WITH clause by separating them with the help of commas. So, once we define the CTEs by the use of WITH clause. We can reference that defined CTE like we would reference a table in a database.
The most important point is that we can refer it in the scope of the execution of the WITH clause query. Once the execution is done of the query we cannot use the CTE in any other statements or query. So we can say that the scope of the CTE is still the execution of the statement.
A Recursive common table expression (RCTE) as the keyword recursive suggests references itself. It has a sub query that refers to its own name. It is mainly used when we are dealing with hierarchical data such as company hierarchy positions chart or table because the recursive CTE will continue executing until the entire hierarchy is returned in the result set.
One important point to remember while dealing with recursive CTE is that if it is not created properly it will create an infinite loop.
Examples to Implement Oracle CTE
Below are the examples mentioned:
Example #1 – SINGLE CTE USING WITH CLAUSE
We can create multiple CTEs in a single WITH clause. In this example, we will create a single CTE which will have the result set of the employee id and employee name of the employees present in the employee table. Let us look at the query for the same
Code:
WITH
CTE AS (SELECT name, employee_id FROM employee WHERE city = 'Delhi')
select * from CTE;
Explanation: Now we will go through the query and understand it. The first part of the query is the part where we have defined the common table expression. The CTE name is provided just after the WITH clause. The SELECT statement query inside the parenthesis of the AS clause is the CTE subquery. The CTE sub-query extracts the result set and stores it in the temporary Common Expression table named as CTE in this case. The second SELECT statement or the SELECT statement at the end of the query is to display the result set which is temporarily stored in the Common Table Expression.
One important point to remember is that the scope of the CTE is only till the execution of the statement.
Output: Let us now execute the query in SQL developer and look at the result.
As we can see in the screenshot that the result only displays the name and employee id of the employees who have the city as DELHI.
Example #2 – MULTIPLE CTE USING WITH CLAUSE
We can use a single WITH clause to define multiple CTEs. In this example, we are going to get the employees and their vehicle names. The Vehicle name is stored in the vehicle table based on the vehicle id as the primary key and the employee name is stored in the Employee table based on the employee id as the primary key. In this example, we are going to get only those employee names and vehicle whose employee id (foreign key) is present in the vehicle table. Let us look at the query for the same.
Code:
WITH
CTE AS (SELECT name, employee_id FROM employee),
CTE1 AS (SELECT employee_id, vehicle_name FROM vehicle)
SELECT name, vehicle_name FROM CTE INNER JOIN CTE1 ON CTE.employee_id = CTE1.employee_id;
Explanation: Now we will go through the query and understand it. The first part of the query is the part where we have defined two common table expressions. Both the CTE name is provided just after the WITH clause. The SELECT statement query inside the parenthesis of the AS clause is the CTE subquery. The CTE sub-query extracts the result set and stores it in the CTE In this case, since we have two CTEs so we have two subqueries, where each result set of the sub-query is stored in their respective CTE. The second part of the query consists of the INNER statement where we are using the result set of both the CTE to display the employee name and the corresponding vehicle name based on the employee id join condition.
One important point to remember is that the scope of the CTE is only till the execution of the statement.
Output: Let us now execute the query in SQL developer and look at the result.
As we can see in the above screenshot it displays the employee name along with their vehicle number.
Conclusion
In this article, we discussed Oracle CTE by staring with the definition of CTE and how to write the CTE (syntax) in Oracle. Later on in the article, we discussed the working of various CTEs in oracle with the help of appropriate examples.
Recommended Articles
This is a guide to Oracle CTE. Here we discuss an introduction to Oracle CTE, how does CTE work along with appropriate syntax, parameters and respective examples for better understanding. You can also go through our other related articles to learn more –
