EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle CTE

Oracle CTE

Priya Pedamkar
Article byPriya Pedamkar

Updated February 27, 2023

Oracle CTE

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Oracle CTE - 1

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.

Oracle CTE - 2

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 –

  1. Oracle Alter Table
  2. Oracle Procedures
  3. Oracle Subquery
  4. Oracle Constraints
GOLANG Course Bundle - 6 Courses in 1
23+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
iOS DEVELOPER Course Bundle - 61 Courses in 1
147+ Hours of HD Videos
61 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JAVA SERVLET Course Bundle - 18 Courses in 1 | 6 Mock Tests
56+ Hours of HD Videos
18 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
RED HAT LINUX Course Bundle - 5 Courses in 1
28+ Hours of HD Videos
5 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
ORACLE DBA Database Management System Course Bundle - 2 Courses in 1
 20+ Hours of HD Videos
2 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more