Use case: We have a simple two tables plus a link table setup using some modern RDBMS.
Hospital table (
Physician table (
Visiting_Privilege table (
Our goal is to find records that are "missing" (absent) from the link table.
Using our example: List physicians and hospitals where they are not authorized to see patients.
The proposed solution: Create a full Cartesian Product between the two main tables, JOIN it with the link table, filter records with either of the IDs as NULL.
SELECT p.name, h.name FROM Physicians p CROSS JOIN Hospitals h LEFT OUTER JOIN Visiting_Privilege v ON p.id = v.physician_id AND h.id = v.hospital_id WHERE v.physician_id IS NULL OR v.hospital_id IS NULL;
We assume that tables are properly indexed and the SQL engine provides reasonable query optimization.
Would it be a right way to attack the problem?
Is there a better solution that avoids the cost of a full Cartesian Product?