A CTE is a common table expression in SQL. It is essentially a temporary result set or output that can be referenced later within other queries. It is extremely useful for managing complex queries and making them more optimized and readable.
1. Basic Syntax
With cte_name AS (
select col1, col2
from table a
where condition
)
SELECT * FROM cte_name
2. Why Use CTEs
- Easy to understand: Long queries can be broken down into simpler, smaller logical pieces and more manageable problems.
- Can be reused: Once a CTE is created with an alias, it can be referenced multiple times within the SQL query. This makes the code more optimized, reusable, and avoids rewriting logic.
- Multiple use cases: CTEs can be used to create multiple definitions together, including advanced tasks like recursive CTEs.
3. Real-World Use Cases:
CREATE TABLE employees(
id INT,
name VARCHAR(40),
salary INT
);
INSERT INTO employees (id, name, salary) VALUES
(1, 'ALICE', 5000),
(2, 'Ben', 5000),
(3, 'Raj', 2000);
WITH salary_calculations AS (
SELECT AVG(salary) AS avg_salary
FROM employees
)
SELECT e.name, e.salary
FROM employees e, salary_calculations s
WHERE e.salary > s.avg_salary;
Output is below
JDoodle is an amazing online tool for trying out SQL code online and makes it easy to test with different SQL versions. Try it below:
jdoodle
Explanation:
We start by creating the table and inserting values into it.
Next, we define the CTE with the average salary.
Finally, we select the employees whose salary is above that average.
Conclusion:
Common Table Expressions are powerful and flexible tools in SQL and should be used carefully and wisely. It's important to break down long, complex logic into smaller parts and use CTEs effectively to make the code easier to read and debug
Top comments (1)
I’ve read about this before, but your explanation made it stick.