Common table expressions (CTE) in SQL

This recipe explains common table expressions (CTE) in SQL

Common Table Expressions (CTE) in SQL

Common Table Expressions (CTE) In this tutorial let us understand what are Common table Expressions in SQL with a very clear example. Let us also understand where exactly to use it. Generally, Common Table Expressions are nothing but a named result set that can be referred to within another query. In is analogous to storing the result of one function in a variable and referring to it later as many times as we want. CTE's can be written using the WITH clause. The syntax of writing queries using a CTE is  

Let us understand the purpose of using a WITH clause through an example.

In this tutorial we are using a simple employee table which has 7 columns and 49 rows. The sample data is shown below.

Lets assume if we want to find the job type whose total salary is greater than the average across all job types. If we breakdown this problem, we have 3 steps to follow

  1. Find the total salary for each job id (lets call it total_sal)
  2. Find the average salary across the total salaries of each job id (lets call it avg_sal)
  3. Find the job ids whose total_sal is greater than the average sal

Now imagine solving this problem using sub queries. 

We can see that the above query is nested to two levels and it is not easy to understand. We can also see that the query to find the total salary for each job type is repeated. This scenario is a perfect to use the WITH clause. By using the with clause the query will now look like so.  

Here are the results

JOB_TYPE total_sal_per_job avg_sal_all_job
AD_VP 34000 17819
FI_ACCOUNT 39600 17819
IT_PROG 28800 17819
ST_CLERK 44000 17819
ST_MAN 36400 17819

Now the query looks much more cleaner and the repetition of queries is also prevented

What Users are saying..

profile image

Gautam Vermani

Data Consultant at Confidential
linkedin profile url

Having worked in the field of Data Science, I wanted to explore how I can implement projects in other domains, So I thought of connecting with ProjectPro. A project that helped me absorb this topic... Read More

Relevant Projects

Image Classification Model using Transfer Learning in PyTorch
In this PyTorch Project, you will build an image classification model in PyTorch using the ResNet pre-trained model.

Build an Image Classifier for Plant Species Identification
In this machine learning project, we will use binary leaf images and extracted features, including shape, margin, and texture to accurately identify plant species using different benchmark classification techniques.

Customer Churn Prediction Analysis using Ensemble Techniques
In this machine learning churn project, we implement a churn prediction model in python using ensemble techniques.

Classification Projects on Machine Learning for Beginners - 1
Classification ML Project for Beginners - A Hands-On Approach to Implementing Different Types of Classification Algorithms in Machine Learning for Predictive Modelling

Recommender System Machine Learning Project for Beginners-2
Recommender System Machine Learning Project for Beginners Part 2- Learn how to build a recommender system for market basket analysis using association rule mining.

Build a Music Recommendation Algorithm using KKBox's Dataset
Music Recommendation Project using Machine Learning - Use the KKBox dataset to predict the chances of a user listening to a song again after their very first noticeable listening event.

NLP Project for Multi Class Text Classification using BERT Model
In this NLP Project, you will learn how to build a multi-class text classification model using using the pre-trained BERT model.

Build a Collaborative Filtering Recommender System in Python
Use the Amazon Reviews/Ratings dataset of 2 Million records to build a recommender system using memory-based collaborative filtering in Python.

Build Customer Propensity to Purchase Model in Python
In this machine learning project, you will learn to build a machine learning model to estimate customer propensity to purchase.

Build a Logistic Regression Model in Python from Scratch
Regression project to implement logistic regression in python from scratch on streaming app data.