Beginner

542. JOIN with COUNT

Use COUNT with JOINs to measure related records for each business entity.

📘 Lesson 542 of 728 ⏱ 12 min 🎯 Intermediate

COUNT can be used with joined tables to determine how many related records exist for each row or group.

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

Count how many orders each customer has placed.

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

Used for customer activity, order analysis, transaction monitoring, and operational reporting.

  • COUNT can summarize joined records.
  • LEFT JOIN can preserve entities with zero related records.
  • COUNT(column) ignores NULL values.
  • COUNT(*) behaves differently with outer JOINs.
  • GROUP BY defines the counting level.
Why use LEFT JOIN with COUNT?

LEFT JOIN allows entities with no related records to remain in the result, often showing a count of zero.

What does COUNT(column) do with NULL?

COUNT(column) does not count NULL values in that column.

Have you finished this lesson?

Introduction to SQL – All Lessons