WITH..AS clause in SQL

The WITH..AS clause in SQL, often referred to as the Common Table Expression (CTE), is a temporary result set which you can reference within a SELECT statement.

CTEs can make your queries more readable and modular, allowing for better readability, easier debugging or performance optimization.

Syntax

The WITH..AS clause allows you to define a CTE as follows:

WITH cte_name AS (
  SELECT column1, column2
  FROM existing_table
  WHERE
    filters
)
SELECT *
FROM cte_name

You can read it as:

• Step 1. We define a virtual cte_name table.
• Step 2. We’re querying the virtual cte_name table as if it was a real table in our database.

Simplifying Complex Queries

CTEs can be particularly useful in simplifying complex SQL queries by breaking them down into smaller, more manageable pieces.

For instance, if you’re working with nested SELECT statements, using a CTE can make your query more readable.

BAD

Look how heavy and unreadable a nested query looks like:

SELECT
  email, age
FROM (
  SELECT *
  FROM users
  WHERE
    status = 'customer'
) AS customers

GOOD

A CTE, on the other hand, allows us to read the same query top-to-bottom and follow the author’s idea of data analysis:

WITH customers AS (
  SELECT *
  FROM users
  WHERE
    status = 'customer'
)

SELECT
  email, age
FROM customers

Using WITH..AS for Data Preparation

The WITH..AS clause is a perfect tool for data preparation.

Such preparation steps might include:

• Data Cleaning. I bet you’ve already heard a phrase “ IN – OUT”. If we have duplicated, incomplete or broken records before our data analysis, chances are they might ruin the result.
• Transformations. For example, we might not be interested in specific user ages, but in age groups – 16-21, 22-30, 31-40, etc. We can use CTE to add a new age_group column to the existing users table.

Try adding a CTE to the following query to calculate age groups:

SELECT age
FROM users

Here’e a free SQL Habit lesson that might help: If/else logic via CASE statement.