Master SQL Subqueries: Scalar, Multi-Row, Correlated & CTEs Explained

Jan 27, 2025

In this blog post, we’ll explore the powerful world of SQL subqueries and how they can help you simplify complex queries. Whether you're a beginner or looking to refine your SQL skills, this guide will walk you through the key concepts, including scalar subqueries, multi-row subqueries, correlated subqueries, and Common Table Expressions (CTEs).


Watch the Video Tutorial

 If you prefer a visual walkthrough, check out the video below:


What Are SQL Subqueries?

A subquery is a query nested inside another query. It allows you to break down complex problems into smaller, more manageable pieces. Subqueries can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE, and JOIN clauses.


Types of Subqueries

1. Scalar Subqueries

A scalar subquery returns a single value (one row and one column). It’s often used in SELECT or WHERE clauses.

Example:

 
SELECT ProductName, ListPrice, 
       (SELECT AVG(ListPrice) FROM Products) AS AvgListPrice
FROM Products;

Here, the subquery calculates the average list price of all products and returns it as a single value.


2. Multi-Row Subqueries

A multi-row subquery returns multiple rows and can be used with operators like IN, ANY, or ALL.

Example:

 
SELECT ProductID, ProductName
FROM Products
WHERE ProductID IN (SELECT ProductID FROM OrderDetails WHERE Quantity > 10);

This query retrieves products that have been ordered more than 10 times.


3. Correlated Subqueries

A correlated subquery references a column from the outer query, making it dependent on the outer query’s results.

Example:

 
SELECT ProductName, ListPrice
FROM Products p1
WHERE ListPrice > (SELECT AVG(ListPrice) 
                   FROM Products p2 
                   WHERE p1.CategoryID = p2.CategoryID);

Here, the subquery calculates the average list price for each category and compares it to the outer query’s product list price.


4. Common Table Expressions (CTEs)

A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It’s a great way to simplify complex queries.

Example:

 
WITH MultipleOrders AS (
    SELECT ProductID
    FROM OrderDetails
    WHERE Quantity > 10
)
SELECT ProductName
FROM Products
WHERE ProductID IN (SELECT ProductID FROM MultipleOrders);

This CTE creates a temporary table (MultipleOrders) that stores products ordered more than 10 times, which is then used in the main query.


When to Use Subqueries

Subqueries are ideal for:

  • Breaking down complex logic into smaller, reusable parts.

  • Filtering data based on dynamic conditions.

  • Simplifying queries with nested logic.

However, avoid overusing subqueries for simple queries, as they can sometimes impact performance.


Practice Makes Perfect

The best way to master subqueries is to practice! Try rewriting your existing queries using subqueries and CTEs to see how they improve readability and maintainability.


Summary

  • Scalar subqueries return a single value.

  • Multi-row subqueries return multiple rows.

  • Correlated subqueries depend on the outer query.

  • CTEs help structure complex queries for better readability.

By mastering these concepts, you’ll be able to write more efficient and maintainable SQL queries.


If you found this guide helpful, don’t forget to check out the video tutorial for a step-by-step walkthrough. Happy querying!

#SQL #Subqueries #CTE #Database #SQLTutorial #LearnSQL

Are you struggling to remember the SQL syntax?

Get FREE SQL Query Cheat Sheet