Subqueries and Common Table Expressions (CTEs) in SQL Server

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?

• ← Part 9: GROUP BY, HAVING, and Aggregations
• Part 11: Stored Procedures and User-Defined Functions →

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

← Previous Next →

GROUP BY, HAVING, and Aggregations in SQL Server Explained

Part 9: GROUP BY, HAVING, and Aggregations in SQL Server

Microsoft SQL Server Tutorial Series: Beginner to Expert

---

Welcome to Part 9 of our SQL Server tutorial series! In this lesson, we’ll explore how to summarize and group data using SQL Server’s powerful aggregation functions, GROUP BY and HAVING clauses.

---

🎯 What You'll Learn

• How to group data using GROUP BY
• How to use aggregate functions like COUNT, SUM, AVG, MIN, MAX
• How to filter grouped results with HAVING
• Practical examples with real-world use cases
• Best practices for performance and clarity

---

🧠 What is GROUP BY?

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It's typically used with aggregate functions.

SELECT Column1, AGG_FUNCTION(Column2)
FROM TableName
GROUP BY Column1;

Example: Count students per active status:

SELECT IsActive, COUNT(*) AS TotalStudents
FROM Students
GROUP BY IsActive;

---

📊 Common Aggregate Functions in SQL Server

Function	Description	Example
COUNT()	Counts number of rows	COUNT(*)
SUM()	Total of a numeric column	SUM(Marks)
AVG()	Average value	AVG(Fees)
MIN()	Lowest value	MIN(Age)
MAX()	Highest value	MAX(Score)

---

📌 Using HAVING to Filter Groups

HAVING is like WHERE, but for grouped results.

SELECT CourseID, COUNT(*) AS Enrolled
FROM Enrollments
GROUP BY CourseID
HAVING COUNT(*) > 10;

This returns only courses with more than 10 enrollments.

---

📚 Real-World Example: Students per Year

SELECT YEAR(BirthDate) AS BirthYear, COUNT(*) AS StudentCount
FROM Students
GROUP BY YEAR(BirthDate)
ORDER BY BirthYear;

This query groups students by their year of birth and counts how many students were born each year.

---

💡 Best Practices

Practice	Why It Matters
Use aliases for aggregated columns	Improves readability (e.g., COUNT(*) AS Total)
Use WHERE before GROUP BY	Filter raw data before grouping for performance
Use HAVING for filtering groups only	HAVING is evaluated after GROUP BY
Format numbers and dates in SELECT	Use FORMAT() if needed for presentation

---

🧾 Quick SQL Cheat Sheet

-- Count rows per group
SELECT Department, COUNT(*) AS Total
FROM Employees
GROUP BY Department;

-- Filter groups
SELECT Category, SUM(Price) AS TotalSales
FROM Products
GROUP BY Category
HAVING SUM(Price) > 10000;
  

---

✅ Summary

• Use GROUP BY to group data by columns
• Apply aggregate functions to summarize data
• Use HAVING to filter aggregated results
• Combine WHERE, GROUP BY, and HAVING for full power

---

🔗 What’s Next?

• Part 8: SQL Joins (INNER, LEFT, RIGHT, FULL)
• Next → Part 10: Subqueries and Common Table Expressions (CTEs)

Was this helpful? Drop a comment or share the post to help others! 🙌

← Previous Next →