SQL Server LEFT JOIN With COUNT: Get 0 for Rows With No Match

Isaiah Langer has never placed an order, yet my first sql server left join with count query gave him one.

Count a column from the right table, not the row: SELECT c.Customer, COUNT(o.OrderID) AS Orders FROM dbo.Customers AS c LEFT JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID GROUP BY c.Customer;

An unmatched customer still gets one joined row, padded with NULLs. COUNT(*) counts it; COUNT(o.OrderID) skips the NULL and returns 0. Here’s the report we’re building:

Before You Start: Sample Customers, Orders and Tickets

Test setup: SQL Server 17.0.1000.7 (2025) Developer edition, sqlcmd in VS Code. Isaiah and Lee have no orders; order 1008 has no customer:

CREATE TABLE dbo.Customers (
    CustomerID int PRIMARY KEY,
    Customer   nvarchar(16) NOT NULL,
    State      char(2) NOT NULL);
CREATE TABLE dbo.Orders (
    OrderID    int PRIMARY KEY,
    CustomerID int NULL,
    OrderDate  date NOT NULL,
    Total      decimal(10,2) NOT NULL,
    Status     varchar(7) NOT NULL);
CREATE TABLE dbo.Tickets (
    TicketID int PRIMARY KEY, CustomerID int NOT NULL);

INSERT INTO dbo.Customers VALUES
(1, N'Megan Bowen', 'WA'), (2, N'Alex Wilber', 'TX'),
(3, N'Adele Vance', 'CA'), (4, N'Diego Siciliani', 'NY'),
(5, N'Lee Gu', 'IL'),      (6, N'Isaiah Langer', 'FL');

INSERT INTO dbo.Orders VALUES
(1001, 1, '2025-11-14',  39.99, 'Shipped'),
(1002, 1, '2026-03-02', 149.00, 'Shipped'),
(1003, 1, '2026-09-17',  54.25, 'Pending'),
(1004, 2, '2026-01-08', 229.50, 'Shipped'),
(1005, 2, '2026-10-12',  54.25, 'Pending'),
(1006, 3, '2025-12-21', 149.00, 'Shipped'),
(1007, 4, '2026-10-02',  54.25, 'Pending'),
(1008, NULL, '2026-10-20', 19.99, 'Shipped');

INSERT INTO dbo.Tickets VALUES (1, 1), (2, 1), (3, 2), (4, 5);

Step 1: Count Orders per Customer With LEFT JOIN and COUNT(column)

SET NOCOUNT ON;
SELECT c.Customer,
       COUNT(*)         AS CountStar,
       COUNT(o.OrderID) AS Orders
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.Customer
ORDER BY Orders DESC, c.Customer;

Output:

Customer         CountStar   Orders     
---------------- ----------- -----------
Megan Bowen                3           3
Alex Wilber                2           2
Adele Vance                1           1
Diego Siciliani            1           1
Isaiah Langer              1           0
Lee Gu                     1           0

What this tells me: the LEFT JOIN in SQL Server kept all six customers, but COUNT(*) can’t tell an order from a NULL-padded row.

Checkpoint: you should see six rows totaling 7 orders; guest order 1008 isn’t counted.

Step 2: Count Only Shipped Orders (Filter in ON, Not WHERE)

SET NOCOUNT ON;
SELECT c.Customer, COUNT(o.OrderID) AS Shipped
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o
    ON o.CustomerID = c.CustomerID AND o.Status = 'Shipped'
GROUP BY c.Customer ORDER BY c.Customer;

SELECT c.Customer, COUNT(o.OrderID) AS Shipped
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
WHERE o.Status = 'Shipped'
GROUP BY c.Customer ORDER BY c.Customer;

Output:

Customer         Shipped    
---------------- -----------
Adele Vance                1
Alex Wilber                1
Diego Siciliani            0
Isaiah Langer              0
Lee Gu                     0
Megan Bowen                2
Customer         Shipped    
---------------- -----------
Adele Vance                1
Alex Wilber                1
Megan Bowen                2

Important: the WHERE version became an inner join: o.Status is NULL on unmatched rows, so Diego, Isaiah and Lee drop out. In ON it only picks which orders match (LEFT OUTER JOIN with a WHERE clause).

Checkpoint: you see six rows with three zeros, then three.

Step 3: Add a Second LEFT JOIN Without Inflating the Count

Tickets doubled Megan’s orders:

SET NOCOUNT ON;
SELECT c.Customer,
       COUNT(o.OrderID)          AS OrdersWrong,
       COUNT(DISTINCT o.OrderID) AS Orders,
       COUNT(DISTINCT t.TicketID) AS Tickets
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders  AS o ON o.CustomerID = c.CustomerID
LEFT JOIN dbo.Tickets AS t ON t.CustomerID = c.CustomerID
GROUP BY c.CustomerID, c.Customer
ORDER BY c.Customer;

Output:

Customer         OrdersWrong Orders      Tickets    
---------------- ----------- ----------- -----------
Adele Vance                1           1           0
Alex Wilber                2           2           1
Diego Siciliani            1           1           0
Isaiah Langer              0           0           0
Lee Gu                     0           0           1
Megan Bowen                6           3           2

You get six rows: three orders times two tickets. COUNT(DISTINCT ...) repairs counts, like DISTINCT in a JOIN, but not a SUM. So I pre-aggregated.

Step 4: Save the LEFT JOIN Count Report as a View

Each derived table returns one row per customer, so nothing multiplies. ISNULL turns gaps into 0, and a view in SQL Server queries like a table:

CREATE VIEW dbo.vCustomerReport AS
SELECT c.Customer,
       ISNULL(o.Orders, 0) AS Orders,
       CAST(ISNULL(o.Spent, 0) AS decimal(10,2)) AS Spent,
       ISNULL(t.Tickets, 0) AS Tickets
FROM dbo.Customers AS c
LEFT JOIN (SELECT CustomerID, COUNT(*) AS Orders,
                  SUM(Total) AS Spent
           FROM dbo.Orders GROUP BY CustomerID) AS o
    ON o.CustomerID = c.CustomerID
LEFT JOIN (SELECT CustomerID, COUNT(*) AS Tickets
           FROM dbo.Tickets GROUP BY CustomerID) AS t
    ON t.CustomerID = c.CustomerID;
GO
SELECT * FROM dbo.vCustomerReport ORDER BY Orders DESC, Customer;

Output:

Customer         Orders      Spent        Tickets    
---------------- ----------- ------------ -----------
Megan Bowen                3       243.24           2
Alex Wilber                2       283.75           1
Adele Vance                1       149.00           0
Diego Siciliani            1        54.25           0
Isaiah Langer              0          .00           0
Lee Gu                     0          .00           1

(6 rows affected)

Test the LEFT JOIN Count Report: HAVING COUNT = 0 and TOP 1

SET NOCOUNT ON;
SELECT c.Customer AS NoOrders
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
GROUP BY c.Customer
HAVING COUNT(o.OrderID) = 0;

SELECT TOP (1) WITH TIES c.Customer, COUNT(o.OrderID) AS Orders
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
GROUP BY c.Customer
ORDER BY Orders DESC;

Output:

NoOrders        
----------------
Isaiah Langer   
Lee Gu          
Customer         Orders     
---------------- -----------
Megan Bowen                3

HAVING COUNT(o.OrderID) = 0 gives you parents with no children. WITH TIES keeps every customer tied for first; plain TOP (1) picks one arbitrarily (see INNER JOIN with TOP 1).

How Do You Count LEFT JOIN Rows by Year?

A YEAR() filter in WHERE drops your zeros again. Count conditionally: COUNT(CASE WHEN YEAR(o.OrderDate) = 2026 THEN o.OrderID END). My run gave Megan 2, Alex 2, Diego 1 and 0 for the other three.

Variation: RIGHT JOIN and FULL OUTER JOIN With COUNT

RIGHT JOIN is LEFT JOIN with the tables swapped. FULL OUTER JOIN keeps both sides, so the guest order appears:

SET NOCOUNT ON;
SELECT ISNULL(c.Customer, N'(guest)') AS Customer,
       COUNT(o.OrderID) AS Orders
FROM dbo.Customers AS c
FULL OUTER JOIN dbo.Orders AS o
    ON o.CustomerID = c.CustomerID
GROUP BY c.Customer
ORDER BY Orders DESC, Customer;

Output:

Customer         Orders     
---------------- -----------
Megan Bowen                3
Alex Wilber                2
(guest)                    1
Adele Vance                1
Diego Siciliani            1
Isaiah Langer              0
Lee Gu                     0

The FULL OUTER JOIN with a WHERE clause has the same ON-vs-WHERE trap.

My Takeaways and Fixes for LEFT JOIN Counts

1. Count the right table’s key, never *, when zeros matter.
2. Put right-table filters in ON.
3. Two child tables? Pre-aggregate each one before joining.

Tested on six customers; fan-out gets expensive at scale.

Where to Take It Next

For matched rows only, see COUNT with a join and GROUP BY. PostgreSQL runs these queries unchanged (joins in PostgreSQL).

More join reading:

• INNER JOIN vs LEFT JOIN in SQL Server
• SQL Server OUTER JOIN types

Frequently Asked Questions