Subqueries and Cte's

Introduction

SQL is a powerful language used for managing and analyzing data. As queries become more complex, tools like subqueries and Common Table Expressions (CTEs) help simplify logic and improve readability.

What is a Subquery?

A subquery is a query nested inside another query. It is used to perform operations that depend on the result of another query.

Example:

SELECT name
FROM customers
WHERE customer_id IN (
SELECT customer_id FROM sales
);

Types of Subqueries

1. Scalar Subquery
   Returns a single value.

2. Multi-row Subquery
   Returns multiple rows.

3. Correlated Subquery
   Depends on the outer query.

When to Use Subqueries

• Filtering based on aggregated data
• Comparing values across tables
• Handling nested logic

What is a CTE?

A Common Table Expression (CTE) is a temporary result set defined using the WITH clause.

Example:

WITH sales_data AS (
SELECT customer_id, SUM(amount) AS total
FROM sales
GROUP BY customer_id
)
SELECT * FROM sales_data;

Types of CTEs

• Simple CTE
• Recursive CTE (used for hierarchical data)

When to Use CTEs

• When queries become complex
• When logic needs to be reused
• When readability is important

Subqueries vs CTEs

Feature	Subqueries	CTEs
Readability	Moderate	High
Reusability	Low	High
Complexity Handling	Limited	Excellent

Conclusion

Both subqueries and CTEs are essential tools in SQL. Subqueries are useful for simple logic, while CTEs are ideal for complex and readable queries.

Mastering both improves your ability to solve real-world data problems efficiently.