EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle CTE

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Oracle CTE

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.

Syntax

Let us now look at the syntax of the Oracle common Table Expression below.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,217 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

Let’s Get Started

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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

EDUCBA

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

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

Special Offer - Oracle Course Training Learn More