-- ============================================

-- BASIC DERIVED TABLE SYNTAX

-- ============================================

-- Simple derived table with alias

SELECT *

FROM (

SELECT employee_id, name, salary

FROM employees

WHERE salary > 80000

) AS high_earners; -- Alias is MANDATORY

-- Execution:

-- 1. Inner query filters to high earners

-- 2. Creates temp table "high_earners"

-- 3. Outer query selects from temp table

-- ============================================

-- DERIVED TABLE WITH FILTERING

-- ============================================

-- Filter in derived table, then filter again in outer query

SELECT employee_id, name, salary

FROM (

SELECT *

FROM employees

WHERE hire_date > '2023-01-01' -- Filter 1: Recent hires

) AS recent_employees

WHERE salary > 70000; -- Filter 2: High salary

-- Why two filters?

-- - Inner filter reduces data early

-- - Outer filter applies additional criteria

-- - Can be more efficient than single WHERE with AND

-- ============================================

-- PRE-AGGREGATION IN DERIVED TABLE

-- ============================================

-- Aggregate first, then join

SELECT

c.customer_id,

c.name,

c.email,

o.order_count,

o.total_spent

FROM customers c