Part 10: Subqueries and Common Table Expressions (CTEs)

Microsoft SQL Server Tutorial Series: Beginner to Expert

Welcome to Part 10 of our SQL Server tutorial series! Today, we dive into Subqueries and CTEs (Common Table Expressions) — two powerful tools to write cleaner, more modular SQL queries.

📌 What You'll Learn

What subqueries are and how to use them
Types of subqueries: scalar, column, row, and correlated
What CTEs are and how they differ from subqueries
Real-world examples and syntax patterns
When to use subqueries vs. CTEs

🔍 What is a Subquery?

A subquery is a query nested inside another SQL query. It can return a value, a row, or a set of rows.

Example: Get students older than the average age:

SELECT FirstName, LastName, BirthDate
FROM Students
WHERE BirthDate < (
    SELECT AVG(BirthDate)
    FROM Students
);

This is a scalar subquery — it returns a single value.

🧱 Types of Subqueries

Type	Description	Example
Scalar Subquery	Returns a single value	Used in WHERE or SELECT
Column Subquery	Returns a single column	Used with IN or comparison
Row Subquery	Returns a row of values	Used in WHERE with row comparison
Correlated Subquery	Depends on outer query row	Runs for each row in outer query

Example of Correlated Subquery:

SELECT FirstName, LastName
FROM Students S
WHERE EXISTS (
  SELECT 1
  FROM Enrollments E
  WHERE E.StudentID = S.StudentID
    AND E.CourseID = 101
);

🧩 What is a CTE (Common Table Expression)?

A CTE is a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement.

WITH StudentAges AS (
    SELECT FirstName, LastName, DATEDIFF(YEAR, BirthDate, GETDATE()) AS Age
    FROM Students
)
SELECT * FROM StudentAges WHERE Age > 18;

CTEs improve readability, especially for complex queries, and are reusable within a single query scope.

🔁 Subquery vs CTE: When to Use?

Feature	Subquery	CTE
Readability	Less readable for large queries	More modular and cleaner
Reusability	Can’t reuse	Can reference multiple times in the same query
Recursion	Not possible	Supports recursive queries
Performance	Good for small tasks	Better for complex logic

📚 Real-World Use Case: Average Marks

WITH CourseAverages AS (
    SELECT CourseID, AVG(Marks) AS AvgMarks
    FROM Grades
    GROUP BY CourseID
)
SELECT G.StudentID, G.CourseID, G.Marks, C.AvgMarks
FROM Grades G
JOIN CourseAverages C ON G.CourseID = C.CourseID
WHERE G.Marks > C.AvgMarks;

This CTE calculates the average marks per course, then returns students who scored above the average.

✅ Best Practices

Use subqueries for simple lookups or conditions
Prefer CTEs for readability and multiple references
Use table aliases to avoid ambiguity
Always test subqueries and CTEs separately before integrating

🧾 SQL Cheat Sheet

-- Scalar subquery
SELECT Name FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

-- Simple CTE
WITH ActiveStudents AS (
  SELECT * FROM Students WHERE IsActive = 1
)
SELECT * FROM ActiveStudents;

✅ Summary

Subqueries are queries inside queries used for filtering and values
CTEs are temporary named result sets for improving readability
Use CTEs for better structure and recursive operations

🔗 What’s Next?

Have questions? Drop a comment and let us know how you’re applying these concepts! 💬

← Previous Next →

Learn Python

Menu

Subqueries and Common Table Expressions (CTEs) in SQL Server