Beginner

686. JOIN with LIMIT Subquery

Use a LIMIT-based subquery before JOINing selected records.

📘 Lesson 686 of 728 ⏱ 14 min 🎯 Advanced

LIMIT restricts the number of rows returned by a query and can be used inside a derived table before a JOIN.

It is useful when only a limited subset of records should participate in a JOIN.

Select the five most recent orders and display their customer names.

SQL 
SELECT
    c.CustomerName,
    x.OrderID,
    x.OrderDate
FROM Customers c
INNER JOIN (
    SELECT
        OrderID,
        CustomerID,
        OrderDate
    FROM Orders
    ORDER BY OrderDate DESC
    LIMIT 5
) x
    ON c.CustomerID = x.CustomerID;

Used in dashboards, recent-activity reports, testing, and targeted analysis.

  • LIMIT is common in MySQL, PostgreSQL, and some other systems.
  • ORDER BY should define the desired subset.
  • Syntax varies between databases.
  • Limiting rows before a JOIN changes the business result.
  • Use carefully when global versus per-group limits are involved.
What does LIMIT do?

LIMIT restricts the number of rows returned by a query in SQL dialects that support it.

Can LIMIT be used inside a JOIN subquery?

Yes. A derived table can use LIMIT before its result is JOINed.

Have you finished this lesson?

Introduction to SQL – All Lessons