Common Table Expressions (CTE) In SQL

Republished By Plato

Followers: 0

Introduction

CTE is the short form for Common Table Expressions. CTE is one of the most powerful tools of SQL (Structured Query Language), and it also helps to clean the data. It is the concept of SQL (Structured Query Language) used to simplify coding and help to get the result as quickly as possible. CTE is the temporary table used to reference the original table. If the original table contains too many columns and we require only a few of them, we can make CTE (a temporary table) containing the required columns only.

CTE is used to solve complex problems easily with good readability of code. A code should always be easier to read so that if you are referring it after some time, you will get that code immediately. Even if the third person is referring to your code, he/she can understand it very well if the readability of your code is easy and good. CTE is one of the important concepts of SQL. If you have knowledge of CTE, you are stepping into the world of advanced SQL. Common Table Expressions (commonly known as CTE) helps Data analysts, data scientists, or any other analysts who want to extract their data efficiently and effectively from a table containing big data.

This article was published as a part of the Data Science Blogathon.

Syntax of CTE

The following is the syntax of CTE:

WITH CTE_NAME AS
( SELECT column_name1, column_name2,..., column_nameN FROM table_name WHERE condition
)
SELECT column_name1, column_name2,..., column_nameN FROM CTE_NAME;

Example of CTE

Example 1:

Below code is executed in MySQL:

-- Creating databse
CREATE DATABASE employee_details; -- Use database
USE employee_details; -- Creating table
CREATE TABLE employee
(
emp_id INT PRIMARY KEY,
emp_name VARCHAR(20),
email_id VARCHAR(30),
city VARCHAR(25),
Verification_status CHAR(3)
); -- Inserting values into the table
INSERT INTO employee VALUES (1,'Suhas','[email protected]','Mumbai','Yes');
INSERT INTO employee VALUES (2,'Yohani','[email protected]','Mumbai','No');
INSERT INTO employee VALUES (3,'Reshama','[email protected]','Pune','Yes');
INSERT INTO employee VALUES (4,'Raj','[email protected]','Bangalore','No');
INSERT INTO employee VALUES (5,'Shivani','[email protected]','Bangalore','Yes');
SELECT * FROM employee;

The Output is given below:

In the example above, I have created the table named ’employee’ which contains the employee details.

The description of the columns of ’employee’ table is listed below:

emp_id: refers to the unique id of each employee
emp_name: refers to the name of each employee
email_id: refers to an email id of each employee
city: refers to the city where the employee is situated
Verification_status: If the verification of the background details of an employee is done, this value is stored as ‘Yes’ whereas If the verification of the background details of an employee is not done, this value is stored as ‘No’.

Suppose we want to extract specific information of employees, then we can achieve this by creating CTE.

If we want to check the verification status of employees whether it is verified or not and want to see the records of the employees whose verification is not done yet. So, we require only two columns in our CTE (temporary table) and the columns are emp_name and Verification_status containing the value as ‘No’ from the table ’employee’ .

The following code shows the example of creating CTE using the table ’employee’:

-- Creating CTE
WITH mycte AS ( SELECT emp_name , Verification_status FROM employee WHERE Verification_status = 'No'
)
SELECT * FROM mycte;

The Output is given below:

Here, ” mycte ” is the name of the CTE (temporary table). ” mycte ” contains the columns emp_name and Verification_status. It contains the details of the employees whose verification is not done yet. This is what we wanted to achieve.

We can make one or more than one CTE from the same table separated by comma.