CompTIA Data+ (DA0-002)Data MiningEasy
A data analyst needs to combine customer order data with product information. The `Orders` table contains `OrderID`, `CustomerID`, and `ProductID`. The `Products` table contains `ProductID`, `ProductName`, and `Price`. The analyst wants to see all orders that have a matching product, along with the product details. Which SQL join type should be used?
- AFULL OUTER JOIN
- BLEFT JOIN
- CRIGHT JOIN
- DINNER JOIN
Show answer & explanationAnswer & explanation
Correct answer: D. INNER JOIN
An INNER JOIN returns only the rows where there is a match in both tables based on the join condition. In this case, it will show all orders that have a corresponding product entry, fitting the requirement.
Why the other options are wrong
- A. A FULL OUTER JOIN would return all orders and all products, including those without matches in either table, which provides more data than requested.
- B. A LEFT JOIN would return all orders, even those without a matching product, which is not what was requested.
- C. A RIGHT JOIN would return all products, even those without a matching order, which is not the primary goal here.
SQL INNER JOIN
An INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
- Returns only matching rows from both tables.
- Requires a common column (join key) between the tables.
- Effectively filters out non-matching records.
- Most common type of join for combining related data.
Memory trick: INNER JOIN: Only the 'common ground' gets shown.