The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Common Table Expressions (CTEs)

Next ►
►

Common Table Expressions (CTEs) are a powerful feature in SQL that allows you to define temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can make complex queries easier to read and maintain.

Key Concepts

  1. Definition: A CTE is defined using the WITH keyword followed by a query that generates a result set.
  2. Scope: The CTE is only valid for the duration of the statement in which it is defined.
  3. Recursive CTEs: CTEs can be recursive, allowing you to perform operations like hierarchical queries.

Syntax

The basic syntax for a CTE is as follows:

WITH cte_name AS (
    -- CTE query
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
-- Main query
SELECT column1, column2
FROM cte_name
WHERE condition;

Practical Examples

Example 1: Simple CTE

Let's create a simple CTE to select employees from a department:

WITH department_employees AS (
    SELECT employee_id, first_name, last_name, department_id
    FROM employees
    WHERE department_id = 10
)
SELECT employee_id, first_name, last_name
FROM department_employees;

Explanation:

  • The CTE department_employees selects employees from department 10.
  • The main query selects specific columns from the CTE.

Example 2: Recursive CTE

A recursive CTE can be used to generate a series of numbers or to traverse hierarchical data. Here’s an example to generate a series of numbers from 1 to 10:

WITH RECURSIVE number_series AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1
    FROM number_series
    WHERE number < 10
)
SELECT number
FROM number_series;

Explanation:

  • The CTE number_series starts with the number 1.
  • The recursive part of the CTE adds 1 to the number until it reaches 10.

Exercises

Exercise 1: Simple CTE

Create a CTE to find all products with a price greater than $100 and then select the product names and prices from the CTE.

-- Write your CTE and main query here
WITH expensive_products AS (
    SELECT product_name, price
    FROM products
    WHERE price > 100
)
SELECT product_name, price
FROM expensive_products;

Exercise 2: Recursive CTE

Create a recursive CTE to generate a series of even numbers from 2 to 20.

-- Write your CTE and main query here
WITH RECURSIVE even_numbers AS (
    SELECT 2 AS number
    UNION ALL
    SELECT number + 2
    FROM even_numbers
    WHERE number < 20
)
SELECT number
FROM even_numbers;

Common Mistakes and Tips

  • Scope Limitation: Remember that CTEs are only valid within the statement they are defined. If you need to use the result set in multiple statements, consider using a temporary table.
  • Recursive CTEs: Ensure that the recursive CTE has a termination condition to avoid infinite loops.
  • Performance: While CTEs can make queries more readable, they may not always be the most performant option. Always analyze and optimize your queries as needed.

Conclusion

Common Table Expressions (CTEs) are a versatile tool in SQL that can simplify complex queries and improve readability. By understanding both simple and recursive CTEs, you can leverage this feature to write more efficient and maintainable SQL code. In the next module, we will delve into database design principles, which will help you structure your databases effectively.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved