Beginner

425. JOIN and COUNT

Learn how COUNT can be used with JOIN to measure related records.

📘 Lesson 425 of 728 ⏱ 11 min 🎯 Intermediate

COUNT can be applied to joined data to determine how many related records exist for each entity.

Counting related records is one of the most common requirements in business analytics.

Count how many orders each customer has placed.

SQL 
SELECT
    c.CustomerName,
    COUNT(o.OrderID) AS OrderCount
FROM Customers c
LEFT JOIN Orders o
    ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

COUNT with JOIN is useful for customer activity, transaction monitoring, order analysis, employee activity, and operational reporting.

  • COUNT can count related records after a JOIN.
  • LEFT JOIN can preserve entities with zero related records.
  • COUNT(column) ignores NULL values.
  • GROUP BY creates one count per entity.
  • COUNT(*) behaves differently from COUNT(column) with outer joins.
Why use LEFT JOIN when counting customers with zero orders?

LEFT JOIN keeps customers even when they have no matching orders.

Why is COUNT(o.OrderID) useful in this query?

Because unmatched customers have NULL OrderID values, COUNT(OrderID) returns zero for those customers.

Have you finished this lesson?

Introduction to SQL – All Lessons