Question
A business analyst is using a client table and an invoice table to create a database view that shows clients who have not made purchases yet. Which of the following joins is most appropriate for the analyst to use to create this database view?
- A. INNER JOIN ON Client.Key = Invoice.Key
- B. RIGHT JOIN ON Client.Key = Invoice.Key WHERE Client.Key IS NULL
- C. LEFT JOIN ON Client.Key = Invoice.Key
- D. LEFT JOIN ON Client.Key = Invoice.Key WHERE Invoice.Key IS NULL Correct
Explanation
A LEFT JOIN from Client to Invoice returns every client, with NULL in the invoice columns when there is no matching invoice, so filtering on Invoice.Key IS NULL leaves only clients without purchases (an anti-join). An INNER JOIN returns only clients that have invoices, a LEFT JOIN without the filter returns all clients, and the RIGHT JOIN keeps all invoices, so checking Client.Key IS NULL would find orphaned invoices rather than clients without purchases.
