CompTIA Data+ (DA0-002)Data MiningMedium
A data engineer is tasked with combining customer purchase history with product catalog information. The 'Purchases' table contains `CustomerID`, `ProductID`, and `PurchaseDate`. The 'Products' table contains `ProductID`, `ProductName`, and `Category`. The business requirement is to list all products that have ever been purchased, along with the customer who bought them, but also to include products from the catalog that have never been purchased. Which SQL join type is most appropriate for this scenario?
- AFULL OUTER JOIN
- BINNER JOIN
- CLEFT JOIN
- DRIGHT JOIN
Show answer & explanationAnswer & explanation
Correct answer: D. RIGHT JOIN
To include all products from the `Products` table (even those not purchased) and match them with purchasing customers from the `Purchases` table, a RIGHT JOIN is appropriate when `Products` is the 'right' table. This ensures all products are listed, and purchased products are accompanied by customer details, while unpurchased ones will show NULLs for customer information.
Why the other options are wrong
- A. FULL OUTER JOIN would also work, but a RIGHT JOIN is more precise if the primary focus is on ensuring all products from the catalog are present, and the `Products` table is designated as the right table.
- B. INNER JOIN would only show products that have been purchased, excluding unpurchased products.
- C. LEFT JOIN with `Purchases` as left and `Products` as right would show all purchases, but only purchased products, not all products from the catalog.
SQL RIGHT JOIN
A type of SQL join that returns all records from the right table (second table in the FROM clause) and the matching records from the left table. If there is no match, the left side will contain NULL values.
- Used when you want to ensure all records from a specific table (the 'right' one) are included in the result.
- Useful for finding records in one table that do or do not have corresponding records in another.
- Can often be rewritten as a LEFT JOIN by swapping table order.
Memory trick: Joining tables: RIGHT JOIN means 'I want all of your products, and any purchases that match'.