Day 45: MySQL CTEs (Common Table Expressions) - Writing Cleaner and More Readable Queries!

Today, let's explore Common Table Expressions (CTEs) in MySQL, a powerful feature that makes complex queries more readable and reusable!

What is a CTE in MySQL?

A CTE(Common Table Expression) is a temporary result set that exists only within the execution of a query. It helps simplify nested queries, making them easier to understand and debug.

CTE Syntax:

WITH  cte_name AS(
    SELECT column1, column2 FROM table_name WHERE condition 
)
SELECT * FROM cte_name;        

( A CTE is like a temporary table, but it exists only during query execution!)

1. Using CTE to Find High-Salary Employees

Finding employees earning above 50,000 rupees

WITH HighSalary AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT * FROM HighSalary;        

(This makes the query much cleaner compared to using subqueries!)

2. CTEs with Aggregations

Finding customers who spent more than 5000

WITH OrderSummary AS(
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders 
    GROUP BY customer_id
)
SELECT customer_id, total_spent 
FROM OrderSummary
WHERE total_spent > 5000;        

(Using CTEs, we calculate total spending per customer, then filter the results!)

3. Recursive CTEs - Working with Hierarchical Data

Recursive CTEs allow self-referencing, making them useful for hierarchical data(e.g., organizational structures, category trees)

Example: Employee Hierarchy

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

   UNION ALL

  SELECT e.employee_id, e.name, e.manager_id, eh.level+1
  FROM employees e
  JOIN EmployeeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELEECT * FROM EmployeeHierarchy        

(This helps retrieve nested relationships, like managers and employees!)

When to Use CTEs?

?? Simplifying complex queries

?? Avoiding subquery repetition

?? Working with hierarchical data (e.g., employees, categories)

?? Improving readability and maintainability

#100DaysOfCode #MySQL #SQL #Database #CTE #CommonTableExpressions #Learning #BackendDevelopment