／var／logmarcus chiu

SQL - Common Table Expression (CTE)

Created on

Common Table Expression (CTE)
  • is a named, temporary result set defined within a single SQL statement. It acts as a temporary view, simplifying complex queries by breaking them into smaller, more manageable parts
  • are created using the WITH keyword and can be used within SELECT, INSERT, UPDATE, or DELETE statements

CTE - Introduction

CTE - Examples

-- How many max prices for each food category is less than $15?
 
-- sub query method
SELECT COUNT(*) FROM (SELECT category, MAX(price) AS max_price
                      FROM menu_items
                      GROUP BY category) AS mp
WHERE max_price < 15;
 
-- CTE method
WITH mp AS (SELECT category, MAX(price) AS max_price
            FROM menu_items
            GROUP BY category)
SELECT COUNT(*) FROM mp WHERE max_price < 15;
-- How many max prices for each food category is less than AVG(of all max prices for each food category)?
 
-- CTE method (multiple references)
WITH mp AS (SELECT category, MAX(price) AS max_price
            FROM menu_items
            GROUP BY category)
SELECT COUNT(*)
FROM mp
WHERE max_price < (SELECT AVG(max_price) FROM mp);
-- CTE method (multiple table references)
WITH mp AS (SELECT category, MAX(price) AS max_price
            FROM menu_items
            GROUP BY category),
     ci AS (SELECT *
            FROM menu_items
            WHERE item_name LIKE '%Chicken%')
SELECT COUNT(*)
FROM ci JOIN mp ON ci.category = mp.category;