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

Savvy Sahai

Data Science Intern, Capgemini
linkedin profile url

As a student looking to break into the field of data engineering and data science, one can get really confused as to which path to take. Very few ways to do it are Google, YouTube, etc. I was one of... 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.

Mastering A/B Testing: A Practical Guide for Production
In this A/B Testing for Machine Learning Project, you will gain hands-on experience in conducting A/B tests, analyzing statistical significance, and understanding the challenges of building a solution for A/B testing in a production environment.

Demand prediction of driver availability using multistep time series analysis
In this supervised learning machine learning project, you will predict the availability of a driver in a specific area by using multi step time series analysis.

AWS MLOps Project to Deploy a Classification Model [Banking]
In this AWS MLOps project, you will learn how to deploy a classification model using Flask on AWS.

Deploy Transformer-BART Model on Paperspace Cloud
In this MLOps Project you will learn how to deploy a Tranaformer BART Model for Abstractive Text Summarization on Paperspace Private Cloud

Ola Bike Rides Request Demand Forecast
Given big data at taxi service (ride-hailing) i.e. OLA, you will learn multi-step time series forecasting and clustering with Mini-Batch K-means Algorithm on geospatial data to predict future ride requests for a particular region at a given time.

Predict Churn for a Telecom company using Logistic Regression
Machine Learning Project in R- Predict the customer churn of telecom sector and find out the key drivers that lead to churn. Learn how the logistic regression model using R can be used to identify the customer churn in telecom dataset.

Forecasting Business KPI's with Tensorflow and Python
In this machine learning project, you will use the video clip of an IPL match played between CSK and RCB to forecast key performance indicators like the number of appearances of a brand logo, the frames, and the shortest and longest area percentage in the video.

Build an optimal End-to-End MLOps Pipeline and Deploy on GCP
Learn how to build and deploy an end-to-end optimal MLOps Pipeline for Loan Eligibility Prediction Model in Python on GCP

End-to-End Snowflake Healthcare Analytics Project on AWS-2
In this AWS Snowflake project, you will build an end to end retraining pipeline by checking Data and Model Drift and learn how to redeploy the model if needed