Specifies a temporary named result set, known as a common table expression (CTE).
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](https://en.wikipedia.org/wiki/SQL:1999#:~:text=SQL%3A1999%20(also%20called%20SQL,meanwhile%20SQL%3A1999%20is%20deprecated.)
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 TableCREATE 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 TableCREATE 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_salesFROM 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_salesFROM customersJOIN 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