MySQL: CTE vs Derived Table

In MySQL, CTE stands for Common Table Expressions.

CTEs are temporary named result sets that you can reference

within a SELECT, INSERT, UPDATE, or DELETE statement.

CTE allow you to break down the logic of the complex query

into more manageable parts.

MySQL supports two types of CTE (Common Table Expression):

non-recursive CTE and recursive CTE.

Non-recursive CTE:

A non-recursive CTE is a CTE that does not reference itself

in the SELECT statement.

Example:

WITH january_orders AS (

SELECT customer_id, SUM(order_amount) AS total_amount

FROM orders

WHERE MONTH(order_date) = 1

GROUP BY customer_id

)

SELECT customer_id, total_amount

FROM january_orders

It is possible to use a CTE (Common Table Expression) with multiple queries.

Example:

WITH monthly_sales AS (

-- Query 1: Calculate total sales for each month

SELECT MONTH(sale_date) AS month, SUM(sale_amount) AS total_sales

FROM sales

GROUP BY MONTH(sale_date)

),

top_sellers AS (

-- Query 2: Find the top 5 sellers for each month

SELECT month, seller_name, SUM(sale_amount) AS total_sales

FROM sales

GROUP BY month, seller_name

ORDER BY month, total_sales DESC

LIMIT 5

)

-- Query 3: Combine the results of the two queries

SELECT ms.month, ms.total_sales, ts.seller_name, ts.total_sales

FROM monthly_sales ms

INNER JOIN top_sellers ts ON ms.month = ts.month

ORDER BY ms.month, ts.total_sales DESC;

Recursive CTE:

A recursive CTE (Common Table Expression) is a CTE that references itself in the SELECT statement.

The recursive CTE has two parts:

The anchor member is the non-recursive part of the CTE, which is executed once at the beginning

of the query. It is a SELECT statement that returns the initial set of rows for the CTE.

The recursive member is the recursive part of the CTE, which is executed repeatedly until

the termination condition is met. It is a SELECT statement that references the CTE itself,

and returns a subset of rows that are related to the previous set of rows returned by the CTE.

Examples:

following query print odd numbers

WITH RECURSIVE odd_no (sr_no, n) AS

(

SELECT 1, 1

union all

SELECT sr_no+1, n+2 from odd_no where sr_no < 25

)

SELECT * FROM odd_no;

EXAMPLE:

following query will generate factorial of number:

WITH RECURSIVE factorial (n, fact) AS (

-- Anchor member

SELECT 0, 1

UNION ALL

-- Recursive member

SELECT n + 1, (n + 1) * fact

FROM factorial

WHERE n < 9

)

SELECT fact

FROM factorial

WHERE n = 9;

Derived Table

In MySQL, a derived table is a table that is created by using the result of a

subquery in a SELECT statement.

SELECT t1.id, t1.name, t2.total_sales

FROM (

SELECT id, name

FROM customers

WHERE country = 'USA'

) AS t1

INNER JOIN (

SELECT customer_id, SUM(sale_amount) AS total_sales

FROM sales

GROUP BY customer_id

) AS t2 ON t1.id = t2.customer_id

ORDER BY t2.total_sales DESC;

In this example, we are creating two derived tables: t1 and t2.

same query by CT:

WITH t1 AS (

SELECT id, name

FROM customers

WHERE country = 'USA'

), t2 AS (

SELECT customer_id, SUM(sale_amount) AS total_sales

FROM sales

GROUP BY customer_id

)

SELECT t1.id, t1.name, t2.total_sales

FROM t1

INNER JOIN t2 ON t1.id = t2.customer_id

ORDER BY t2.total_sales DESC;

Derived tables can be a useful tool for simplifying complex queries and breaking down a

large query into smaller, more manageable parts.

CTE vs Derived Table:

1) CTEs are typically more concise and easier to read than derived tables.

2) CTEs are often used for recursive queries , while derived tables are typically used for simple subqueries.

3) Derived tables cannot be recursive, and cannot reference themselves in the subquery.

3)CTE provides better performance in comparison with a derived table.