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

Create Your First Chatbot with RASA NLU Model and Python
Learn the basic aspects of chatbot development and open source conversational AI RASA to create a simple AI powered chatbot on your own.

Deploy Transformer BART Model for Text summarization on GCP
Learn to Deploy a Machine Learning Model for the Abstractive Text Summarization on Google Cloud Platform (GCP)

Build CI/CD Pipeline for Machine Learning Projects using Jenkins
In this project, you will learn how to create a CI/CD pipeline for a search engine application using Jenkins.

Abstractive Text Summarization using Transformers-BART Model
Deep Learning Project to implement an Abstractive Text Summarizer using Google's Transformers-BART Model to generate news article headlines.

Recommender System Machine Learning Project for Beginners-4
Collaborative Filtering Recommender System Project - Comparison of different model based and memory based methods to build recommendation system using collaborative filtering.

Build a Text Generator Model using Amazon SageMaker
In this Deep Learning Project, you will train a Text Generator Model on Amazon Reviews Dataset using LSTM Algorithm in PyTorch and deploy it on Amazon SageMaker.

Natural language processing Chatbot application using NLTK for text classification
In this NLP AI application, we build the core conversational engine for a chatbot. We use the popular NLTK text classification library to achieve this.

Skip Gram Model Python Implementation for Word Embeddings
Skip-Gram Model word2vec Example -Learn how to implement the skip gram algorithm in NLP for word embeddings on a set of documents.

Time Series Analysis with Facebook Prophet Python and Cesium
Time Series Analysis Project - Use the Facebook Prophet and Cesium Open Source Library for Time Series Forecasting in Python

MLOps using Azure Devops to Deploy a Classification Model
In this MLOps Azure project, you will learn how to deploy a classification machine learning model to predict the customer's license status on Azure through scalable CI/CD ML pipelines.