To audit a junction table, first produce the expected key pairs from the trusted source data. Compare expected and actual pairs in both directions: one query finds missing relationships, and the other finds unexpected relationships.
Last updated: October 19, 2026.
WITH Expected AS (
SELECT p.ProductId, s.SupplierId
FROM dbo.ImportedProductSupplier AS i
JOIN dbo.Product AS p
ON p.ProductName = i.ProductName
JOIN dbo.Supplier AS s
ON s.SupplierName = i.SupplierName
),
Missing AS (
SELECT ProductId, SupplierId FROM Expected
EXCEPT
SELECT ProductId, SupplierId FROM dbo.ProductSupplier
),
Unexpected AS (
SELECT ProductId, SupplierId FROM dbo.ProductSupplier
EXCEPT
SELECT ProductId, SupplierId FROM Expected
)
SELECT 'Missing' AS Issue, ProductId, SupplierId FROM Missing
UNION ALL
SELECT 'Unexpected', ProductId, SupplierId FROM Unexpected;An empty result means the two sets contain the same distinct key pairs. Keep the issue label so an audit immediately shows which side needs investigation.
Validate the expected set before comparing
Microsoft documents that EXCEPT returns distinct rows present on the left but absent on the right. Run it twice because equality requires both sets to exclude nothing from the other.
The expected query is only as reliable as its joins. Joining by names can create missing or duplicate matches when spelling, whitespace, or collation differs. Before trusting the audit, check the staging table for unmatched products and suppliers, and require a stable source identifier when one is available.
Check duplicates and enforce future integrity
EXCEPT compares distinct rows, so it does not reveal duplicate pairs. Audit those separately with GROUP BY ProductId, SupplierId HAVING COUNT(*) > 1. After cleaning the data, add a composite primary key or unique constraint across both junction columns.
Each column should also have a foreign key to its parent table. Microsoft’s constraint guidance explains how primary and foreign keys enforce entity and referential integrity. Constraints prevent orphaned or duplicate relationships after the one-time comparison is complete.
Run the audit inside a transaction or against a consistent snapshot when other sessions may edit these tables. Continue with SQL joins, foreign-key lookups during inserts, and composite primary keys.
