Beginner

685. JOIN with TOP Subquery

Understand how a limited subquery can be JOINed with another table.

📘 Lesson 685 of 728 ⏱ 14 min 🎯 Advanced

A subquery can limit the number of rows returned before those rows are JOINed with another dataset.

This pattern can be useful when a business rule requires only a specific subset of records.

Select the highest-value orders and then display customer details.

SQL 
SELECT
    c.CustomerName,
    x.OrderID,
    x.OrderAmount
FROM Customers c
INNER JOIN (
    SELECT TOP 10
        OrderID,
        CustomerID,
        OrderAmount
    FROM Orders
    ORDER BY OrderAmount DESC
) x
    ON c.CustomerID = x.CustomerID;

Used in targeted reporting, exception analysis, top-value transaction reviews, and dashboards.

  • TOP limits rows in SQL Server.
  • ORDER BY should define which rows are selected.
  • TOP syntax differs across database systems.
  • The limited result is then JOINed.
  • For portability, database-specific alternatives may be required.
Why use ORDER BY with TOP?

ORDER BY defines which rows should be included when the result is limited.

Is TOP standard SQL?

TOP is commonly associated with SQL Server; other databases use syntax such as LIMIT or FETCH.

Have you finished this lesson?

Introduction to SQL – All Lessons