"Left Join Where Is Null" gets complicated to read/understand and is subject to cardinalities issues that will impact performance if the right set has many records for 1 record in the left hand side.
There is also this way, probably the closest to natural language.
SelectDistinct
CustomerID
From#Purchase
WhereProductCode = 'A'
AndCustomerID In
(
SelectCustomerID
From#Purchase
WhereProductCode = 'B'
)
AndCustomerID Not In
(
SelectCustomerID
From#Purchase
WhereProductCode = 'C'
)
I would be interested to have this table filled by 100,000+ rows of sample data and then compare execution plans and statistics of the proposed approaches.
Comparing syntax without an idea of the performance signature is a bit useless IMHO.
So here is another version of the initialization script from which the difference will be more blatant:
--===== Conditionally drop the test table to make
-- reruns in SSMS easier.
IF OBJECT_ID('tempdb..#Purchase','U') IS NOT NULL
DROP TABLE #Purchase
;
--===== Create the test table
CREATE TABLE #Purchase
(
PurchaseID INT IDENTITY(1,1),
CustomerID INT,
ProductCode VARCHAR(1000)
PRIMARY KEY CLUSTERED (PurchaseID)
)
;
--===== Populate the test table with known data.
INSERT INTO #Purchase
(CustomerID, ProductCode)
------- Customer #1 precisely meets the criteria.
-- Bought 'A' and 'B' but not 'C'.
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
------- Customer #2 also meets the criteria.
-- Bought 'A' and 'B' and somthing else,
-- but not 'C'.
SELECT 2, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 2, 'D' UNION ALL
------- Customer #3 also meets the criteria.
-- Bought 'A' and 'B' and something else,
-- but not 'C'.
SELECT 3, 'A' UNION ALL
SELECT 3, 'B' UNION ALL
SELECT 3, 'D' UNION ALL
SELECT 3, 'A' UNION ALL
SELECT 3, 'D' UNION ALL
------- Customer #4 doesn't meet the criteria.
-- Bought 'A' and 'B' but also bought 'C'.
SELECT 4, 'A' UNION ALL
SELECT 4, 'B' UNION ALL
SELECT 4, 'C' UNION ALL
------- Customer #5 doesn't meet the criteria.
-- Bought 'A' and 'B' and something else,
-- but also bought 'C'.
SELECT 5, 'A' UNION ALL
SELECT 5, 'B' UNION ALL
SELECT 5, 'A' UNION ALL
SELECT 5, 'B' UNION ALL
SELECT 5, 'C' UNION ALL
SELECT 5, 'D' UNION ALL
------- Customer #6 doesn't meet the criteria.
-- Bought more than 1 of 'A' and something else
-- but not 'B'.
SELECT 6, 'A' UNION ALL
SELECT 6, 'A' UNION ALL
SELECT 6, 'D' UNION ALL
SELECT 6, 'E' UNION ALL
------- Customer #7 doesn't meet the criteria.
-- Bought more than 1 of 'B' and something else
-- but not 'A'.
SELECT 7, 'B' UNION ALL
SELECT 7, 'B' UNION ALL
SELECT 7, 'D' UNION ALL
SELECT 7, 'E'
go
declare @i int = 10;
while @i < 100000
begin
insert into #Purchase (CustomerID, ProductCode)
select CustomerID + @i, ProductCode + Left(ProductCode, 1)
From #Purchase
set @i = @i * 2
end
Then turn on statistics or open your SQL profiler
SET STATISTICS IO ON
And now you're ready.
Then you will also need an index on ProductCode.
Create Index IX_ProductCode On #Purchase (ProductCode) Include (CustomerID);