Relational Division in T-SQL: Customers Who Bought Every Product

Buying several products is different from buying every required product. Relational division expresses that complete-coverage question without mistaking duplicate purchases for missing requirements.

Define the Required Set

The requirement contains product identifiers rather than purchase quantities. A customer qualifies when every required identifier appears among that customer's purchases. Extra purchases remain allowed unless the business asks for an exact match.

I write the required set separately before choosing the query shape. I also decide what an empty requirement means. That small decision changes whether a customer with no purchases should qualify.

A staff skills question follows the same pattern. Replace products with required skills and purchases with recorded qualifications. The query still asks whether any required item is absent.

The database does not award extra coverage for buying the same product twice. Duplicate rows describe repeated activity, while coverage describes distinct membership. Keeping those concepts separate prevents a familiar counting mistake.

Use a disposable database for the fixture. Two tables hold requirements and purchase activity, while VALUES defines the demonstration's known customer population. A production query should use the real customer master as that population.

Build Deliberate Edge Cases

The required table has a primary key so its list cannot contain duplicate identifiers. The purchase table permits repeated products and a null product value. Those choices let the test expose counting and missing-value behavior.

CREATE TABLE dbo.RequiredProductDemo
(
    ProductID int NOT NULL CONSTRAINT PK_RequiredProductDemo PRIMARY KEY
);
CREATE TABLE dbo.PurchaseDivisionDemo
(
    PurchaseID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_PurchaseDivisionDemo PRIMARY KEY,
    CustomerID int NOT NULL,
    ProductID int NULL
);
INSERT dbo.RequiredProductDemo(ProductID) VALUES (10),(20);
INSERT dbo.PurchaseDivisionDemo(CustomerID, ProductID)
VALUES (501,10),(501,20),(501,20),
       (502,10),(502,10),
       (503,10),(503,20),(503,30),
       (504,NULL);
CREATE INDEX IX_PurchaseDivisionDemo_CustomerProduct
ON dbo.PurchaseDivisionDemo(CustomerID, ProductID);

Customer 501 illustrates complete coverage with a repeated purchase. Customer 502 illustrates duplicates without complete coverage. Customer 503 illustrates complete coverage plus an extra product, and customer 504 illustrates a missing product value.

The later query population also includes customer 505 with no recorded purchases. That customer tests the empty-list rule rather than disappearing before the logic begins. An activity-derived population cannot represent customers without activity.

This fixture describes intended inputs, not observed production results. Run the queries and inspect which identifiers they return. Compare those results against each declared case before changing the query for a larger table.

Relational Division by Counting Distinct Matches

The count solution compares distinct matched requirements with the required set's size. It counts the required identifier from the join, rather than every purchased identifier. Extra purchases therefore do not distort the coverage count.

WITH Customers AS
(
    SELECT CustomerID
    FROM (VALUES(501),(502),(503),(504),(505)) AS c(CustomerID)
)
SELECT c.CustomerID
FROM Customers AS c
LEFT JOIN dbo.PurchaseDivisionDemo AS p
    ON p.CustomerID = c.CustomerID
LEFT JOIN dbo.RequiredProductDemo AS r
    ON r.ProductID = p.ProductID
GROUP BY c.CustomerID
HAVING COUNT(DISTINCT r.ProductID) =
       (SELECT COUNT(*) FROM dbo.RequiredProductDemo)
ORDER BY c.CustomerID;

The left joins keep customers without matching activity in the grouped population. When the required list is empty, both sides of the comparison become zero. This implementation therefore qualifies every known customer for an empty requirement.

COUNT DISTINCT ignores null values. That behavior is useful here because unmatched purchase rows produce null required identifiers. It must still be intentional rather than an accidental side effect of the joins.

Relational division fails when a raw purchase count stands in for coverage. Repeated purchases can make that count large without adding another required item. Use distinct matched identifiers or the existence-based solution instead.

Relational Division With Double NOT EXISTS

Double NOT EXISTS reads as no required product exists for which no matching purchase exists. That matches the complete-coverage question directly. It also avoids generating a joined activity row for every matching purchase.

WITH Customers AS
(
    SELECT CustomerID
    FROM (VALUES(501),(502),(503),(504),(505)) AS c(CustomerID)
)
SELECT c.CustomerID
FROM Customers AS c
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.RequiredProductDemo AS r
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM dbo.PurchaseDivisionDemo AS p
        WHERE p.CustomerID = c.CustomerID
          AND p.ProductID = r.ProductID
    )
)
ORDER BY c.CustomerID;

Existence tests do not become more true when another duplicate purchase appears. The inner predicate looks for at least one matching row. This makes the set membership rule particularly clear during a review.

An empty required set contains no missing requirement, so this solution also returns every known customer. Add a separate nonempty-list condition if the business requires a different outcome. Keep that rule consistent across both implementations.

Exact Relational Division Rejects Extra Items

Exact division adds a second condition: no purchased item exists outside the required set. This is stricter than complete coverage. It deliberately excludes customers who bought additional products.

The query below also treats a null purchased product as an extra invalid item. It cannot match any non-null required product. Decide whether your business instead excludes unknown purchases before applying this rule.

WITH Customers AS
(
    SELECT CustomerID
    FROM (VALUES(501),(502),(503),(504),(505)) AS c(CustomerID)
)
SELECT c.CustomerID
FROM Customers AS c
WHERE NOT EXISTS
(
    SELECT 1 FROM dbo.RequiredProductDemo AS r
    WHERE NOT EXISTS
    (
        SELECT 1 FROM dbo.PurchaseDivisionDemo AS p
        WHERE p.CustomerID = c.CustomerID AND p.ProductID = r.ProductID
    )
)
AND NOT EXISTS
(
    SELECT 1 FROM dbo.PurchaseDivisionDemo AS p
    WHERE p.CustomerID = c.CustomerID
      AND NOT EXISTS
      (
          SELECT 1 FROM dbo.RequiredProductDemo AS r
          WHERE r.ProductID = p.ProductID
      )
)
ORDER BY c.CustomerID;

On my test server, both coverage queries returned customers 501 and 503. The exact query returned customer 501 alone, because 503 also bought product 30.

Duplicates of a required product still satisfy exact set division. They do not add another distinct item outside the required set. If quantities matter, define a different requirement and compare quantities explicitly.

With an empty required set, exact division qualifies only customers with no purchased rows. That differs from the ordinary complete-coverage rule. Test it rather than assuming both empty-list outcomes must match.

Check NULL and Index Policies

A null requirement is prohibited by the required table's key. An unknown required identifier cannot support a meaningful complete-coverage comparison. Validate external requirement lists before loading them into that table.

The customer-product index supports finding one customer's matching product efficiently. The required product key supports the opposite membership check. Inspect actual plans and reads when moving from the small fixture to a large activity table.

Without useful indexes on the matching keys, nested existence checks can perform substantial repeated work. Compare the count and existence solutions against your data distribution. The clearer expression does not guarantee the cheapest plan.

Data can change while a report checks requirements and purchases. Use the application's required consistency policy when a stable point-in-time answer matters. Correct set logic alone does not freeze concurrent changes.

Keep the requirement list stable for the duration of the report. If it comes from a request, load and validate it as one coherent set. Mixing successive versions of that list makes the coverage result difficult to explain.

Confirm the Business Question

Should customers with extra purchases qualify, and should an empty list qualify everyone? Answer both before approving the query. Those choices define the result more strongly than the preferred SQL style.

Relational division remains readable when the population, requirement set, and unknown-value policy stay separate. Keep duplicate activity from changing membership logic. Retain the edge cases as a small review fixture.

Finish by comparing complete coverage and exact coverage deliberately. Verify the intended identifiers, then inspect the larger workload's plans. A correct every-item question begins with a precise definition of every.

Related reading on this blog: SQL Server: Find Distinct Result Sets Using EXCEPT Operator and Gaps and Islands: Finding Missing Ranges in a Sequence.

Complete coverage is not a large purchase count, it is the absence of a missing requirement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.