Johnny's Knowledge

SQL - CTE And Subqueries

Properties1
tagsdatabase, sql

CTE And Subqueries

Common table expressions and subqueries both let a query depend on intermediate results. They are useful when a single flat query becomes hard to read, and they pair naturally with SQL Window Functions — putting a RANK() or ROW_NUMBER() inside a CTE is the standard way to filter on a window-function result, since WHERE cannot reference a window expression directly.

Common Table Expressions

A CTE creates a temporary result set that can be referenced by the main query.

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

Benefits:

  • improves query readability
  • can be referenced multiple times
  • helps structure complex queries

Subqueries

A subquery is a query nested inside another query.

Single-row subquery:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Multiple-row subquery:

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE location = 'NY'
);

CASE WHEN

CASE WHEN adds conditional logic to SQL.

Simple CASE:

SELECT
    selling_date,
    CASE SUBSTRING(selling_date, 5, 3)
        WHEN 'Jan' THEN 1
        WHEN 'Feb' THEN 2
        ELSE NULL
    END AS selling_month
FROM car_prices;

Searched CASE:

SELECT
    name,
    age,
    CASE
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 64 THEN 'Adult'
        WHEN age >= 65 THEN 'Senior'
        ELSE 'Unknown'
    END AS age_group
FROM people;

SUBSTRING

SUBSTRING extracts part of a string.

SUBSTRING(variable, start, length)

SQL string indexing starts at 1, not 0.

Backlinks