CompTIA Data+ (DA0-002)Data MiningMedium
A data analyst needs to combine customer order data with product catalog information. The 'Orders' table contains 'OrderID' and 'ProductID', while the 'Products' table contains 'ProductID' and 'ProductName'. The analyst wants to see all orders along with their corresponding product names, and also see any products that exist in the catalog but have not yet been ordered. Which SQL JOIN type should be used?
- ALEFT JOIN
- BINNER JOIN
- CFULL OUTER JOIN
- DRIGHT JOIN
Show answer & explanationAnswer & explanation
Correct answer: D. RIGHT JOIN
A RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table (Products) and the matching rows from the left table (Orders). If there is no match, NULLs are used for the left table's columns. This satisfies the requirement to see all products, including those not yet ordered.
Why the other options are wrong
- A. LEFT JOIN would return all orders and matching products, but would exclude products without orders.
- B. INNER JOIN only returns rows where there is a match in both tables, excluding products not ordered.
- C. FULL OUTER JOIN would show all orders and all products, including orders without products (which isn't explicitly requested as a primary objective here) and products without orders.
SQL RIGHT JOIN
A SQL JOIN that returns all rows from the right table and the matched rows from the left table. If no match is found for a right table row, NULLs are returned for the left table's columns.
- Also known as RIGHT OUTER JOIN.
- Ensures all records from the 'right' table are included.
- Useful for finding unmatched records in the left table relative to the right.
Memory trick: Joining tables intelligently reveals connections.