Advanced SQL Topics - CTEs, Optimization & Performance
Master advanced SQL concepts including Common Table Expressions, recursive queries, PIVOT operations, dynamic SQL, query optimization, execution plans, and table partitioning with comprehensive diagrams and examples
Advanced SQL Topics
Table of Contents
- Common Table Expressions (CTEs)
- Recursive CTEs
- PIVOT and UNPIVOT
- Temporary Tables
- Table Variables
- Dynamic SQL
- Query Optimization
- Execution Plans
- Table Partitioning
- Sequences
Common Table Expressions (CTEs)
CTE Architecture
┌─────────────────────────────────────────────────────────────┐
│ CTE ARCHITECTURE │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ WITH cte_name AS ( │ │
│ │ SELECT ... │ │
│ │ ) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Temporary Result Set (exists only during query) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SELECT * FROM cte_name │ │
│ │ (Main query uses CTE) │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
CTEs provide a way to write more readable and maintainable queries by defining temporary named result sets.
Basic CTE Syntax
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;
Example 1: Simple CTE
-- Calculate customer order statistics
WITH customer_orders AS (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent,
AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY customer_id
)
SELECT
c.customer_id,
c.first_name,
c.last_name,
co.order_count,
co.total_spent,
co.avg_order_value
FROM customers c
INNER JOIN customer_orders co ON c.customer_id = co.customer_id
WHERE co.order_count > 5
ORDER BY co.total_spent DESC;
Example 2: Multiple CTEs
-- Multiple CTEs for complex analysis
WITH
-- CTE 1: Calculate monthly sales
monthly_sales AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(total_amount) AS monthly_total
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
-- CTE 2: Calculate average monthly sales
avg_sales AS (
SELECT AVG(monthly_total) AS avg_monthly_sales
FROM monthly_sales
),
-- CTE 3: Identify high-performing months
high_months AS (
SELECT
ms.month,
ms.monthly_total,
┌─────────────────────────────────────────────────────────────┐ │ MULTIPLE CTEs FLOW │ ├─────────────────────────────────────────────────────────────┤ │ │ │ WITH │ │ ┌────────────────────┐ │ │ │ CTE 1: Raw Data │ │ │ └────────────────────┘ │ │ │ │ │ ▼ │ │ ┌────────────────────┐ │ │ │ CTE 2: Transform │ ◄─── Can reference CTE 1 │ │ └────────────────────┘ │ │ │ │ │ ▼ │ │ ┌────────────────────┐ │ │ │ CTE 3: Aggregate │ ◄─── Can reference CTE 1 & 2 │ │ └────────────────────┘ │ │ │ │ │ ▼ │ │ ┌────────────────────┐ │ │ │ Final SELECT │ ◄─── Uses any/all CTEs │ │ └────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────┘
ms.monthly_total - a.avg_monthly_sales AS variance
FROM monthly_sales ms
CROSS JOIN avg_sales a
WHERE ms.monthly_total > a.avg_monthly_sales
)
SELECT * FROM high_months
ORDER BY variance DESC;
Example 3: CTE with Joins
-- Product performance analysis
WITH product_stats AS (
SELECT
p.product_id,
p.product_name,
p.category_id,
COUNT(DISTINCT o.order_id) AS times_ordered,
SUM(oi.quantity) AS total_quantity_sold,
SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
GROUP BY p.product_id, p.product_name, p.category_id
),
category_totals AS (
SELECT
category_id,
SUM(total_revenue) AS category_revenue
FROM product_stats
GROUP BY category_id
)
SELECT
ps.product_name,
ps.times_ordered,
ps.total_quantity_sold,
ps.total_revenue,
ct.category_revenue,
ROUND((ps.total_revenue / ct.category_revenue * 100), 2) AS pct_of_category
FROM product_stats ps
INNER JOIN category_totals ct ON ps.category_id = ct.category_id
ORDER BY ps.total_revenue DESC;
Example 4: CTE for Data Transformation
-- Transform and clean data
WITH cleaned_data AS (
SELECT
customer_id,
TRIM(UPPER(first_name)) AS first_name,
TRIM(UPPER(last_name)) AS last_name,
LOWER(TRIM(email)) AS email,
REGEXP_REPLACE(phone, '[^0-9]', '') AS phone_digits
FROM customers
WHERE email IS NOT NULL
),
validated_data AS (
SELECT *
FROM cleaned_data
WHERE
LENGTH(phone_digits) = 10
AND email LIKE '%@%.%'
)
SELECT * FROM validated_data;
CTE Benefits
┌──────────────────────────────────────────────────────────┐
│ CTE BENEFITS │
├──────────────────────────────────────────────────────────┤
│ │
│ ✅ Readability │
│ - Break complex queries into logical steps │
│ - Self-documenting code │
│ - Named result sets │
│ │
│ ✅ Maintainability │
│ - Easier to modify and debug │
│ - Reusable within same query │
│ - Clear data flow │
│ │
│ ✅ Recursion Support │
│ - Handle hierarchical data │
│ - Tree traversal │
│ - Graph processing │
│ │
│ ✅ No Temp Table Overhead │
│ - No need to create/drop temp tables │
│ - Cleaner code │
│ - Better performance for simple queries │
│ │
│ ✅ Scope Management │
│ - Exists only during query execution │
│ - No cleanup required │
│ - No naming conflicts │
│ │
└──────────────────────────────────────────────────────────┘
Recursive CTE Structure
┌─────────────────────────────────────────────────────────────┐
│ RECURSIVE CTE STRUCTURE │
├─────────────────────────────────────────────────────────────┤
│ │
│ WITH RECURSIVE cte_name AS ( │
│ │
│ ┌──────────────────────────────────────────────┐ │
│ │ ANCHOR MEMBER (Base Case) │ │
│ │ - Initial result set │ │
│ │ - No recursion │ │
│ │ - Starting point │ │
│ └──────────────────────────────────────────────┘ │
│ │ │
│ UNION ALL │
│ │ │
│ ┌──────────────────────────────────────────────┐ │
│ │ RECURSIVE MEMBER │ │
│ │ - References cte_name │ │
│ │ - Joins with previous iteration │ │
│ │ - Continues until no rows returned │ │
│ └──────────────────────────────────────────────┘ │
│ ) │
│ SELECT * FROM cte_name; │
│ │
└─────────────────────────────────────────────────────────────┘
Recursive CTEs
Recursive CTEs reference themselves to process hierarchical or tree-structured data.
Recursive CTE Structure
WITH RECURSIVE cte_name AS (
-- Anchor member (base case)
SELECT columns
FROM table
WHERE base_condition
UNION ALL
-- Recursive member
SELECT columns
FROM table
INNER JOIN cte_name ON join_condition
WHERE recursive_condition
)
┌─────────────────────────────────────────────────────────────┐ │ EMPLOYEE HIERARCHY VISUALIZATION │ ├─────────────────────────────────────────────────────────────┤ │ │ │ ┌─────────────┐ │ │ │ CEO │ Level 1 (Anchor) │ │ └──────┬──────┘ │ │ │ │ │ ┌────────────────┼────────────────┐ │ │ │ │ │ │ │ ┌────▼────┐ ┌────▼────┐ ┌────▼────┐ │ │ │ VP Sales│ │ VP Tech │ │VP Finance│ Level 2 │ │ └────┬────┘ └────┬────┘ └────┬────┘ │ │ │ │ │ │ │ ┌────┼────┐ ┌────┼────┐ ┌───┼────┐ │ │ │ │ │ │ │ │ │ │ │ │ │ ┌──▼─┐┌─▼─┐┌─▼─┐ ┌──▼─┐┌─▼─┐┌─▼─┐ ┌──▼─┐┌─▼─┐┌─▼─┐ │ │ │Mgr1││Mgr2│Mgr3││Mgr4││Mgr5│Mgr6││Mgr7││Mgr8│Mgr9│ L3 │ │ └────┘└───┘└───┘ └────┘└───┘└───┘ └────┘└───┘└───┘ │ │ │ │ Recursive CTE traverses from CEO down through all levels │ │ │ └─────────────────────────────────────────────────────────────┘
SELECT * FROM cte_name;
Example 1: Employee Hierarchy
-- Build organization chart
WITH RECURSIVE employee_hierarchy AS (
-- Anchor: Top-level managers (no manager)
SELECT
employee_id,
first_name,
last_name,
manager_id,
job_title,
1 AS level,
CAST(first_name + ' ' + last_name AS VARCHAR(1000)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: Employees with managers
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.manager_id,
e.job_title,
eh.level + 1,
CAST(eh.path + ' > ' + e.first_name + ' ' + e.last_name AS VARCHAR(1000))
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
WHERE eh.level < 10 -- Prevent infinite recursion
)
SELECT
REPLICATE(' ', level - 1) + first_name + ' ' + last_name AS employee,
job_title,
level,
path
FROM employee_hierarchy
ORDER BY path;
Example 2: Category Tree
-- Navigate category hierarchy
WITH RECURSIVE category_tree AS (
-- Anchor: Root categories
SELECT
category_id,
category_name,
parent_category_id,
1 AS level,
CAST(category_name AS VARCHAR(1000)) AS path
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
-- Recursive: Child categories
SELECT
c.category_id,
c.category_name,
c.parent_category_id,
ct.level + 1,
CAST(ct.path + ' / ' + c.category_name AS VARCHAR(1000))
FROM categories c
INNER JOIN category_tree ct ON c.parent_category_id = ct.category_id
)
SELECT
category_id,
REPLICATE('--', level - 1) + category_name AS category_hierarchy,
level,
path
FROM category_tree
ORDER BY path;
Example 3: Number Sequence Generator
┌─────────────────────────────────────────────────────────────┐ │ BILL OF MATERIALS EXPLOSION │ ├─────────────────────────────────────────────────────────────┤ │ │ │ Product: Bicycle │ │ │ │ ┌──────────────┐ │ │ │ Bicycle │ Level 1 (1 unit) │ │ └──────┬───────┘ │ │ │ │ │ ┌────┼────┬────────┬────────┐ │ │ │ │ │ │ │ │ │ ┌─▼─┐┌─▼─┐┌─▼──┐ ┌─▼──┐ ┌─▼──┐ │ │ │Frame│Wheels│Seat│ │Pedals││Chain│ Level 2 │ │ │(1) │ (2) │(1) │ │ (2) │ (1) │ │ │ └────┘└─┬──┘└────┘ └──┬───┘└────┘ │ │ │ │ │ │ ┌────┼────┐ ┌────┼────┐ │ │ │ │ │ │ │ │ │ │ ┌──▼─┐┌─▼─┐┌─▼─┐┌──▼─┐┌─▼─┐┌─▼─┐ │ │ │Tire││Rim││Spoke││Pedal││Crank││Arm│ Level 3 │ │ │(2) ││(2)││(36)││ (2) ││ (2) ││(2)│ │ │ └────┘└───┘└────┘└─────┘└────┘└───┘ │ │ │ │ Total Quantity = Parent Qty × Component Qty │ │ Example: 2 Wheels × 36 Spokes = 72 Spokes total │ │ │ └─────────────────────────────────────────────────────────────┘
-- Generate sequence of numbers
WITH RECURSIVE number_sequence AS (
-- Anchor: Start with 1
SELECT 1 AS num
UNION ALL
-- Recursive: Add 1 until reaching 100
SELECT num + 1
FROM number_sequence
WHERE num < 100
)
SELECT num FROM number_sequence;
-- Use for date ranges
WITH RECURSIVE date_range AS (
SELECT DATE('2024-01-01') AS date
UNION ALL
SELECT DATE_ADD(date, INTERVAL 1 DAY)
FROM date_range
WHERE date < '2024-12-31'
)
SELECT
date,
DAYNAME(date) AS day_name,
WEEK(date) AS week_number
FROM date_range;
Example 4: Bill of Materials (BOM)
-- Explode product components
CREATE TABLE parts (
part_id INT PRIMARY KEY,
part_name VARCHAR(100),
parent_part_id INT,
quantity INT,
FOREIGN KEY (parent_part_id) REFERENCES parts(part_id)
);
WITH RECURSIVE bom AS (
-- Anchor: Top-level product
SELECT
part_id,
part_name,
parent_part_id,
quantity,
1 AS level,
quantity AS total_quantity
FROM parts
WHERE part_id = 1 -- Product ID
UNION ALL
-- Recursive: Component parts
SELECT
p.part_id,
p.part_name,
p.parent_part_id,
p.quantity,
b.level + 1,
b.total_quantity * p.quantity
FROM parts p
INNER JOIN bom b ON p.parent_part_id = b.part_id
)
SELECT
REPLICATE(' ', level - 1) + part_name AS component,
quantity AS unit_quantity,
total_quantity,
level
FROM bom
ORDER BY level, part_name;
Example 5: Find All Subordinates
-- Get all employees under a manager
WITH RECURSIVE subordinates AS (
-- Anchor: Specific manager
SELECT
employee_id,
first_name,
last_name,
manager_id,
salary
FROM employees
WHERE employee_id = 5 -- Manager ID
UNION ALL
-- Recursive: All subordinates
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.manager_id,
e.salary
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT
employee_id,
first_name + ' ' + last_name AS employee_name,
salary
FROM subordinates
WHERE employee_id != 5 -- Exclude the manager
ORDER BY salary DESC;
PIVOT Transformation
┌─────────────────────────────────────────────────────────────┐
│ PIVOT TRANSFORMATION │
├─────────────────────────────────────────────────────────────┤
│ │
│ BEFORE (Rows): │
│ ┌──────────────┬───────┬────────┐ │
│ │ Product │ Month │ Sales │ │
│ ├──────────────┼───────┼────────┤ │
│ │ Product A │ Jan │ 1000 │ │
│ │ Product A │ Feb │ 1200 │ │
│ │ Product A │ Mar │ 1500 │ │
│ │ Product B │ Jan │ 800 │ │
│ │ Product B │ Feb │ 900 │ │
│ │ Product B │ Mar │ 1100 │ │
│ └──────────────┴───────┴────────┘ │
│ │ │
│ │ PIVOT │
│ ▼ │
│ AFTER (Columns): │
│ ┌──────────────┬─────┬─────┬─────┐ │
│ │ Product │ Jan │ Feb │ Mar │ │
│ ├──────────────┼─────┼─────┼─────┤ │
│ │ Product A │1000 │1200 │1500 │ │
│ │ Product B │ 800 │ 900 │1100 │ │
│ └──────────────┴─────┴─────┴─────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
Recursive CTE Execution Flow
┌──────────────────────────────────────────────────────────┐
│ RECURSIVE CTE EXECUTION │
├──────────────────────────────────────────────────────────┤
│ │
│ Iteration 0 (Anchor): │
│ ┌─────────────┐ │
│ │ CEO │ Level 1 │
│ └─────────────┘ │
│ │ │
│ ▼ │
│ Iteration 1 (Recursive): │
│ ┌─────────────┬─────────────┬─────────────┐ │
│ │ VP Sales │ VP Tech │ VP Finance │ Level 2 │
│ └─────────────┴─────────────┴─────────────┘ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ Iteration 2 (Recursive): │
│ ┌──────┬──────┬──────┬──────┬──────┬──────┐ │
│ │ Mgr1 │ Mgr2 │ Mgr3 │ Mgr4 │ Mgr5 │ Mgr6 │ Level 3 │
│ └──────┴──────┴──────┴──────┴──────┴──────┘ │
│ │ │ │ │ │ │ │
│ ▼ ▼ ▼ ▼ ▼ ▼ │
│ Iteration 3 (Recursive): │
│ ┌────────────────────────────────────────┐ │
│ │ Individual Contributors (Level 4) │ │
│ └────────────────────────────────────────┘ │
│ │
│ ... continues until no more rows returned │
│ │
│ ⚠️ Always include termination condition! │
│ WHERE level < max_depth │
│ │
└──────────────────────────────────────────────────────────┘
PIVOT and UNPIVOT
Transform rows to columns (PIVOT) and columns to rows (UNPIVOT).
PIVOT - Rows to Columns
-- SQL Server: PIVOT example
-- Transform monthly sales data
SELECT *
FROM (
SELECT
product_name,
MONTH(order_date) AS month,
total_amount
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
) AS source_data
PIVOT (
SUM(total_amount)
FOR month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
### UNPIVOT Transformation
┌─────────────────────────────────────────────────────────────┐ │ UNPIVOT TRANSFORMATION │ ├─────────────────────────────────────────────────────────────┤ │ │ │ BEFORE (Columns): │ │ ┌──────────────┬─────┬─────┬─────┐ │ │ │ Product │ Jan │ Feb │ Mar │ │ │ ├──────────────┼─────┼─────┼─────┤ │ │ │ Product A │1000 │1200 │1500 │ │ │ │ Product B │ 800 │ 900 │1100 │ │ │ └──────────────┴─────┴─────┴─────┘ │ │ │ │ │ │ UNPIVOT │ │ ▼ │ │ AFTER (Rows): │ │ ┌──────────────┬───────┬────────┐ │ │ │ Product │ Month │ Sales │ │ │ ├──────────────┼───────┼────────┤ │ │ │ Product A │ Jan │ 1000 │ │ │ │ Product A │ Feb │ 1200 │ │ │ │ Product A │ Mar │ 1500 │ │ │ │ Product B │ Jan │ 800 │ │ │ │ Product B │ Feb │ 900 │ │ │ │ Product B │ Mar │ 1100 │ │ │ └──────────────┴───────┴────────┘ │ │ │ └─────────────────────────────────────────────────────────────┘
) AS pivot_table;
-- Result:
-- product_name | 1 | 2 | 3 | ... | 12
-- Product A | 1000 | 1200 | 1500 | ... | 2000
-- Product B | 800 | 900 | 1100 | ... | 1300
PIVOT Alternative (MySQL)
-- MySQL: Use CASE statements for PIVOT
SELECT
product_name,
SUM(CASE WHEN MONTH(order_date) = 1 THEN total_amount ELSE 0 END) AS Jan,
SUM(CASE WHEN MONTH(order_date) = 2 THEN total_amount ELSE 0 END) AS Feb,
SUM(CASE WHEN MONTH(order_date) = 3 THEN total_amount ELSE 0 END) AS Mar,
SUM(CASE WHEN MONTH(order_date) = 4 THEN total_amount ELSE 0 END) AS Apr,
SUM(CASE WHEN MONTH(order_date) = 5 THEN total_amount ELSE 0 END) AS May,
SUM(CASE WHEN MONTH(order_date) = 6 THEN total_amount ELSE 0 END) AS Jun,
SUM(CASE WHEN MONTH(order_date) = 7 THEN total_amount ELSE 0 END) AS Jul,
SUM(CASE WHEN MONTH(order_date) = 8 THEN total_amount ELSE 0 END) AS Aug,
SUM(CASE WHEN MONTH(order_date) = 9 THEN total_amount ELSE 0 END) AS Sep,
SUM(CASE WHEN MONTH(order_date) = 10 THEN total_amount ELSE 0 END) AS Oct,
SUM(CASE WHEN MONTH(order_date) = 11 THEN total_amount ELSE 0 END) AS Nov,
SUM(CASE WHEN MONTH(order_date) = 12 THEN total_amount ELSE 0 END) AS Dec
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY product_name;
UNPIVOT - Columns to Rows
-- SQL Server: UNPIVOT example
-- Transform columnar data to rows
SELECT
product_name,
month,
sales_amount
FROM monthly_sales
UNPIVOT (
sales_amount FOR month IN (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)
) AS unpivot_table;
-- Result:
-- product_name | month | sales_amount
-- Product A | Jan | 1000
-- Product A | Feb | 1200
-- Product A | Mar | 1500
UNPIVOT Alternative (MySQL)
-- MySQL: Use UNION ALL for UNPIVOT
SELECT product_name, 'Jan' AS month, Jan AS sales_amount FROM monthly_sales
UNION ALL
SELECT product_name, 'Feb', Feb FROM monthly_sales
UNION ALL
SELECT product_name, 'Mar', Mar FROM monthly_sales
UNION ALL
SELECT product_name, 'Apr', Apr FROM monthly_sales
UNION ALL
SELECT product_name, 'May', May FROM monthly_sales
UNION ALL
SELECT product_name, 'Jun', Jun FROM monthly_sales
UNION ALL
SELECT product_name, 'Jul', Jul FROM monthly_sales
UNION ALL
SELECT product_name, 'Aug', Aug FROM monthly_sales
UNION ALL
SELECT product_name, 'Sep', Sep FROM monthly_sales
UNION ALL
SELECT product_name, 'Oct', Oct FROM monthly_sales
UNION ALL
SELECT product_name, 'Nov', Nov FROM monthly_sales
UNION ALL
SELECT product_name, 'Dec', Dec FROM monthly_sales
ORDER BY product_name, month;
Dynamic PIVOT
-- SQL Server: Dynamic PIVOT with variable columns
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
-- Build column list dynamically
SELECT @columns = STRING_AGG(QUOTENAME(month), ',')
FROM (SELECT DISTINCT MONTH(order_date) AS month FROM orders) AS months;
-- Build dynamic SQL
SET @sql = '
SELECT *
FROM (
SELECT
product_name,
MONTH(order_date) AS month,
total_amount
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
) AS source_data
PIVOT (
SUM(total_amount)
FOR month IN (' + @columns + ')
) AS pivot_table';
### Temporary Table Lifecycle
┌─────────────────────────────────────────────────────────────┐ │ TEMPORARY TABLE LIFECYCLE │ ├─────────────────────────────────────────────────────────────┤ │ │ │ Session Start │ │ │ │ │ ▼ │ │ ┌──────────────────────────────────────┐ │ │ │ CREATE TEMPORARY TABLE │ │ │ │ - Stored in tempdb (SQL Server) │ │ │ │ - Visible only to current session │ │ │ └──────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌──────────────────────────────────────┐ │ │ │ INSERT/UPDATE/DELETE Operations │ │ │ │ - Use like regular table │ │ │ │ - Can create indexes │ │ │ └──────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌──────────────────────────────────────┐ │ │ │ Query Temporary Table │ │ │ │ - Multiple times in session │ │ │ │ - Join with other tables │ │ │ └──────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ ┌──────────────────────────────────────┐ │ │ │ DROP TABLE (Optional) │ │ │ │ OR │ │ │ │ Auto-dropped at session end │ │ │ └──────────────────────────────────────┘ │ │ │ │ │ ▼ │ │ Session End │ │ │ └─────────────────────────────────────────────────────────────┘
-- Execute dynamic SQL
EXEC sp_executesql @sql;
Temporary Tables
Temporary tables store intermediate results during a session.
Creating Temporary Tables
-- MySQL: Temporary table
CREATE TEMPORARY TABLE temp_customer_stats (
customer_id INT,
order_count INT,
total_spent DECIMAL(10,2),
avg_order_value DECIMAL(10,2)
);
-- Insert data
INSERT INTO temp_customer_stats
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent,
AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY customer_id;
-- Use temporary table
SELECT
c.first_name,
c.last_name,
t.order_count,
t.total_spent
FROM customers c
INNER JOIN temp_customer_stats t ON c.customer_id = t.customer_id
WHERE t.order_count > 5;
-- Temporary table automatically dropped at session end
SQL Server Temporary Tables
-- SQL Server: Local temporary table (session-specific)
CREATE TABLE #temp_sales (
product_id INT,
total_quantity INT,
total_revenue DECIMAL(10,2)
);
-- Insert data
INSERT INTO #temp_sales
SELECT
product_id,
SUM(quantity) AS total_quantity,
SUM(quantity * unit_price) AS total_revenue
FROM order_items
GROUP BY product_id;
-- Use temporary table
SELECT
p.product_name,
t.total_quantity,
t.total_revenue
FROM products p
INNER JOIN #temp_sales t ON p.product_id = t.product_id
ORDER BY t.total_revenue DESC;
-- Drop explicitly (optional)
DROP TABLE #temp_sales;
-- Global temporary table (visible to all sessions)
CREATE TABLE ##global_temp (
id INT,
value VARCHAR(100)
);
Temporary Table vs CTE
┌──────────────────────────────────────────────────────────┐
│ TEMPORARY TABLE vs CTE │
├──────────────────────────────────────────────────────────┤
│ │
│ Use TEMPORARY TABLE when: │
│ ✅ Need to reference multiple times │
│ ✅ Large intermediate result sets │
│ ✅ Need indexes for performance │
│ ✅ Complex transformations │
│ ✅ Multiple queries in same session │
│ │
│ Example: │
│ CREATE TEMPORARY TABLE temp_results AS │
│ SELECT customer_id, SUM(total) AS total │
│ FROM orders GROUP BY customer_id; │
│ │
│ SELECT * FROM temp_results WHERE total > 1000; │
│ SELECT * FROM temp_results WHERE total < 500; │
│ SELECT AVG(total) FROM temp_results; │
│ │
│ ───────────────────────────────────────────────────── │
│ │
│ Use CTE when: │
│ ✅ Single use in one query │
│ ✅ Improve readability │
│ ✅ Recursive queries │
│ ✅ Small to medium result sets │
│ ✅ No need for indexes │
│ │
│ Example: │
│ WITH results AS ( │
│ SELECT customer_id, SUM(total) AS total │
│ FROM orders GROUP BY customer_id │
│ ) │
│ SELECT * FROM results WHERE total > 1000; │
│ │
└──────────────────────────────────────────────────────────┘
-- ✅ Use Temporary Table when:
-- 1. Need to reference multiple times
CREATE TEMPORARY TABLE temp_results AS
SELECT customer_id, SUM(total_amount) AS total
FROM orders
GROUP BY customer_id;
SELECT * FROM temp_results WHERE total > 1000;
SELECT * FROM temp_results WHERE total < 500;
SELECT AVG(total) FROM temp_results;
-- ✅ Use CTE when:
-- 1. Single use in one query
WITH results AS (
SELECT customer_id, SUM(total_amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT * FROM results WHERE total > 1000;
Table Variables
Table variables are similar to temporary tables but with different scope and behavior.
SQL Server Table Variables
-- Declare table variable
DECLARE @customer_summary TABLE (
customer_id INT,
order_count INT,
total_spent DECIMAL(10,2),
last_order_date DATE
);
-- Insert data
INSERT INTO @customer_summary
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id;
-- Use table variable
SELECT
c.first_name,
c.last_name,
cs.order_count,
cs.total_spent,
cs.last_order_date
FROM customers c
INNER JOIN @customer_summary cs ON c.customer_id = cs.customer_id
WHERE cs.order_count > 10;
Table Variables vs Temporary Tables
┌──────────────────────────────────────────────────────────┐
│ TABLE VARIABLES vs TEMPORARY TABLES │
├──────────────────────────────────────────────────────────┤
│ │
│ Table Variables: │
│ ✅ Faster for small datasets │
│ ✅ No recompilation │
│ ✅ Scoped to batch/procedure │
│ ❌ No statistics │
│ ❌ No indexes (except primary key) │
│ ❌ Cannot use TRUNCATE │
│ │
│ Temporary Tables: │
│ ✅ Better for large datasets │
│ ✅ Statistics available │
│ ✅ Can create indexes │
│ ✅ Can use TRUNCATE │
│ ❌ Stored in tempdb │
│ ❌ Can cause recompilation │
│ │
└──────────────────────────────────────────────────────────┘
Dynamic SQL
Execute SQL statements built at runtime.
Example 1: Basic Dynamic SQL (SQL Server)
-- Build and execute dynamic query
DECLARE @table_name NVARCHAR(100) = 'customers';
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM ' + QUOTENAME(@table_name) + ' WHERE customer_id < 10';
EXEC sp_executesql @sql;
Example 2: Parameterized Dynamic SQL
-- SQL Server: Safe parameterized dynamic SQL
DECLARE @customer_id INT = 5;
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
SELECT
customer_id,
first_name,
last_name,
email
FROM customers
WHERE customer_id = @cust_id';
EXEC sp_executesql
@sql,
N'@cust_id INT',
@cust_id = @customer_id;
Example 3: Dynamic Column Selection
-- Build SELECT with dynamic columns
DECLARE @columns NVARCHAR(MAX) = 'customer_id, first_name, last_name, email';
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT ' + @columns + ' FROM customers WHERE is_active = 1';
EXEC sp_executesql @sql;
Example 4: Dynamic Table Creation
-- Create table dynamically
DECLARE @year INT = 2024;
DECLARE @table_name NVARCHAR(100);
DECLARE @sql NVARCHAR(MAX);
SET @table_name = 'orders_' + CAST(@year AS NVARCHAR(4));
SET @sql = '
CREATE TABLE ' + QUOTENAME(@table_name) + ' (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL
)';
EXEC sp_executesql @sql;
Example 5: MySQL Dynamic SQL
-- MySQL: Prepared statements
SET @table_name = 'customers';
SET @sql = CONCAT('SELECT * FROM ', @table_name, ' LIMIT 10');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- With parameters
SET @customer_id = 5;
SET @sql = 'SELECT * FROM customers WHERE customer_id = ?';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @customer_id;
DEALLOCATE PREPARE stmt;
Dynamic SQL Best Practices
-- ✅ Good: Use QUOTENAME to prevent SQL injection
DECLARE @table_name NVARCHAR(100) = 'customers';
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM ' + QUOTENAME(@table_name);
-- ❌ Bad: Vulnerable to SQL injection
SET @sql = 'SELECT * FROM ' + @table_name; -- Dangerous!
-- ✅ Good: Use parameters
EXEC sp_executesql
N'SELECT * FROM customers WHERE customer_id = @id',
N'@id INT',
@id = 5;
-- ❌ Bad: String concatenation with user input
SET @sql = 'SELECT * FROM customers WHERE customer_id = ' + @user_input; -- Dangerous!
Query Optimization
Techniques to improve query performance.
Optimization Techniques
┌──────────────────────────────────────────────────────────┐
│ QUERY OPTIMIZATION TECHNIQUES │
├──────────────────────────────────────────────────────────┤
│ │
│ 1. Indexing │
│ - Create appropriate indexes │
│ - Use covering indexes │
│ │
│ 2. Query Rewriting │
│ - Avoid SELECT * │
│ - Use EXISTS instead of IN │
│ - Avoid functions on indexed columns │
│ │
│ 3. Join Optimization │
│ - Join on indexed columns │
│ - Filter early │
│ - Use appropriate join types │
│ │
│ 4. Subquery Optimization │
│ - Convert to JOINs when possible │
│ - Use CTEs for readability │
│ │
│ 5. Statistics │
│ - Keep statistics updated │
│ - Analyze query patterns │
│ │
└──────────────────────────────────────────────────────────┘
Example 1: Avoid SELECT *
-- ❌ Bad: SELECT *
SELECT * FROM customers WHERE customer_id = 5;
-- ✅ Good: Select only needed columns
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE customer_id = 5;
Example 2: Use EXISTS Instead of IN
-- ❌ Slower: IN with subquery
SELECT *
FROM customers c
WHERE c.customer_id IN (
SELECT customer_id FROM orders WHERE total_amount > 1000
);
-- ✅ Faster: EXISTS
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.total_amount > 1000
);
Example 3: Avoid Functions on Indexed Columns
-- ❌ Bad: Function prevents index usage
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;
-- ✅ Good: Sargable predicate
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';
Example 4: Use Covering Indexes
-- Create covering index
CREATE INDEX idx_customer_orders
ON orders(customer_id, order_date, total_amount);
-- Query uses covering index (no table lookup needed)
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 5;
Example 5: Optimize JOINs
-- ❌ Bad: Cartesian product then filter
SELECT *
FROM customers c, orders o
WHERE c.customer_id = o.customer_id
AND c.is_active = 1;
-- ✅ Good: Filter early, explicit JOIN
SELECT *
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.is_active = 1;
Execution Plans
Understand how the database executes queries.
Viewing Execution Plans
-- SQL Server: Show execution plan
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM customers WHERE customer_id = 5;
GO
SET SHOWPLAN_TEXT OFF;
GO
-- Or use graphical plan
SET STATISTICS PROFILE ON;
SELECT * FROM customers WHERE customer_id = 5;
SET STATISTICS PROFILE OFF;
-- MySQL: EXPLAIN
EXPLAIN SELECT * FROM customers WHERE customer_id = 5;
-- Detailed format
EXPLAIN FORMAT=JSON
SELECT * FROM customers WHERE customer_id = 5;
-- PostgreSQL: EXPLAIN
EXPLAIN SELECT * FROM customers WHERE customer_id = 5;
-- With execution statistics
EXPLAIN ANALYZE
SELECT * FROM customers WHERE customer_id = 5;
Reading Execution Plans
┌──────────────────────────────────────────────────────────┐
│ EXECUTION PLAN OPERATORS │
├──────────────────────────────────────────────────────────┤
│ │
│ Table Scan: │
│ - Reads entire table │
│ - Slowest operation │
│ - No index used │
│ │
│ Index Scan: │
│ - Scans entire index │
│ - Better than table scan │
│ - Still reads all index entries │
│ │
│ Index Seek: │
│ - Uses index to find specific rows │
│ - Fastest operation │
│ - Optimal for selective queries │
│ │
│ Nested Loop Join: │
│ - Good for small datasets │
│ - One row at a time │
│ │
│ Hash Join: │
│ - Good for large datasets │
│ - Builds hash table │
│ │
│ Merge Join: │
│ - Requires sorted inputs │
│ - Efficient for sorted data │
│ │
└──────────────────────────────────────────────────────────┘
Example: Analyzing Query Performance
-- SQL Server: Detailed statistics
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(o.order_id) > 5;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
-- Analyze results:
-- - Logical reads
-- - Physical reads
-- - CPU time
-- - Elapsed time
Table Partitioning
Divide large tables into smaller, manageable pieces.
Partitioning Benefits
┌──────────────────────────────────────────────────────────┐
│ TABLE PARTITIONING BENEFITS │
├──────────────────────────────────────────────────────────┤
│ │
│ ✅ Improved Query Performance │
│ - Partition elimination │
│ - Parallel processing │
│ │
│ ✅ Easier Maintenance │
│ - Archive old partitions │
│ - Rebuild specific partitions │
│ │
│ ✅ Better Manageability │
│ - Smaller backup/restore units │
│ - Faster index operations │
│ │
│ ✅ Improved Availability │
│ - Partition-level operations │
│ - Reduced downtime │
│ │
└──────────────────────────────────────────────────────────┘
Example 1: Range Partitioning (SQL Server)
-- Create partition function
CREATE PARTITION FUNCTION pf_orders_by_year (DATE)
AS RANGE RIGHT FOR VALUES
('2021-01-01', '2022-01-01', '2023-01-01', '2024-01-01');
-- Create partition scheme
CREATE PARTITION SCHEME ps_orders_by_year
AS PARTITION pf_orders_by_year
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);
-- Create partitioned table
CREATE TABLE orders_partitioned (
order_id INT,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2)
) ON ps_orders_by_year(order_date);
Example 2: List Partitioning (MySQL)
-- MySQL: Partition by list
CREATE TABLE orders_by_region (
order_id INT,
customer_id INT,
region VARCHAR(20),
order_date DATE,
total_amount DECIMAL(10,2)
)
PARTITION BY LIST COLUMNS(region) (
PARTITION p_north VALUES IN ('North', 'Northeast', 'Northwest'),
PARTITION p_south VALUES IN ('South', 'Southeast', 'Southwest'),
PARTITION p_east VALUES IN ('East'),
PARTITION p_west VALUES IN ('West')
);
Example 3: Hash Partitioning
-- MySQL: Hash partitioning for even distribution
CREATE TABLE customers_partitioned (
customer_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
)
PARTITION BY HASH(customer_id)
PARTITIONS 4;
Querying Partitioned Tables
-- Query automatically uses partition elimination
SELECT * FROM orders_partitioned
WHERE order_date >= '2024-01-01'
AND order_date < '2024-12-31';
-- Only scans 2024 partition
-- View partition information
-- SQL Server
SELECT
p.partition_number,
p.rows,
rv.value AS partition_boundary
FROM sys.partitions p
INNER JOIN sys.partition_schemes ps ON p.partition_id = ps.data_space_id
INNER JOIN sys.partition_range_values rv ON ps.function_id = rv.function_id
WHERE OBJECT_NAME(p.object_id) = 'orders_partitioned';
Sequences
Generate unique sequential numbers.
Creating Sequences
-- SQL Server: Create sequence
CREATE SEQUENCE seq_order_number
START WITH 1000
INCREMENT BY 1
MINVALUE 1000
MAXVALUE 999999
CYCLE;
-- Use sequence
INSERT INTO orders (order_number, customer_id, total_amount)
VALUES (NEXT VALUE FOR seq_order_number, 1, 100.00);
-- Get next value
SELECT NEXT VALUE FOR seq_order_number;
-- PostgreSQL: Create sequence
CREATE SEQUENCE seq_customer_code
START WITH 1
INCREMENT BY 1
NO MAXVALUE
CACHE 20;
-- Use sequence
INSERT INTO customers (customer_code, first_name, last_name)
VALUES (nextval('seq_customer_code'), 'John', 'Doe');
-- Get current value
SELECT currval('seq_customer_code');
-- Oracle: Create sequence
CREATE SEQUENCE seq_transaction_id
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- Use sequence
INSERT INTO transactions (transaction_id, amount)
VALUES (seq_transaction_id.NEXTVAL, 500.00);
Sequence vs Auto-Increment
┌──────────────────────────────────────────────────────────┐
│ SEQUENCE vs AUTO-INCREMENT │
├──────────────────────────────────────────────────────────┤
│ │
│ Sequences: │
│ ✅ Can be used across multiple tables │
│ ✅ More control over numbering │
│ ✅ Can get next value before INSERT │
│ ✅ Can reset/alter easily │
│ ✅ Supports caching for performance │
│ ✅ Can define min/max values │
│ │
│ Auto-Increment: │
│ ✅ Simpler to use │
│ ✅ Automatically assigned │
│ ✅ No extra objects to manage │
│ ❌ Tied to specific column │
│ ❌ Less flexible │
│ ❌ Cannot share across tables │
│ │
└──────────────────────────────────────────────────────────┘
Summary
This section covered:
- ✅ Common Table Expressions (CTEs) for readable queries
- ✅ Recursive CTEs for hierarchical data
- ✅ PIVOT and UNPIVOT for data transformation
- ✅ Temporary tables and table variables
- ✅ Dynamic SQL for runtime query building
- ✅ Query optimization techniques
- ✅ Execution plan analysis
- ✅ Table partitioning strategies
- ✅ Sequences for unique number generation
- ✅ Best practices for advanced SQL features