Specifies a temporary named result set, known as a common table expression (CTE).

Microsoft Documentation

Although there are some time around us the first time someone asked me about it I was confused: I haven’t know its proper definition until that momento. In fact, the common table expressions where added to the SQL standard in 1999

SQL:1999 added a WITH [RECURSIVE] construct allowing recursive queries, like transitive closure, to be specified in the query language itself; see common table expressions.

Wikipedia SQL:1999

Nowadays are used and well known for a lot of people around the world who works with data. There is a really powerful tool for organizing and simplifying complex queries. Because you can change a complex code to a one simpler.

So, for example. We want to simplifed a database for an online store with two tables:

• customers – Contains information about customers.
  • Columns: id (customer ID), name (customer name)
• orders – Contains information about orders made by customers.
  • Columns: order_id (order ID), customer_id (customer ID), order_amount (amount of the order)

-- Customers Table
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO customers (id, name)
VALUES 
    (1, 'John Doe'),
    (2, 'Jane Smith'),
    (3, 'Bob Johnson');

-- Orders Table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, customer_id, order_amount)
VALUES
    (101, 1, 50.00),
    (102, 1, 30.00),
    (103, 2, 75.00),
    (104, 2, 20.00),
    (105, 3, 100.00);

You can see he result with this query:

SELECT 
    customers.name,
    (
        SELECT SUM(order_amount) 
        FROM orders 
        WHERE orders.customer_id = customers.id
    ) AS total_sales
FROM 
    customers;

But this is much cleaner:

WITH total_sales_cte AS (
    SELECT 
        customer_id,
        SUM(order_amount) AS total_sales
    FROM 
        orders
    GROUP BY 
        customer_id
)

SELECT 
    customers.name,
    cte.total_sales
FROM 
    customers
JOIN 
    total_sales_cte cte ON customers.id = cte.customer_id;

The example below is much more clear than the previous one. We should avoid the use in the following escenarions:

• Simple Queries
• One-Time Use
• Nested CTEs