CompTIA Data+ (DA0-002)Data MiningMedium
A financial analyst is querying a large transactional database to identify customers who have made purchases in both the 'Electronics' and 'Apparel' categories. The database has two tables: `Customers (customer_id, customer_name)` and `Orders (order_id, customer_id, category, amount)`. Which SQL join type should the analyst use to retrieve only the `customer_name` of customers present in both categories, ensuring each customer appears only once?
- AINNER JOIN
- BFULL OUTER JOIN
- CRIGHT JOIN
- DLEFT JOIN
Show answer & explanationAnswer & explanation
Correct answer: A. INNER JOIN
An INNER JOIN returns only the rows that have matching values in both tables. To find customers in BOTH categories, we can join the `Customers` table with `Orders` twice, once for 'Electronics' and once for 'Apparel', using an INNER JOIN each time to ensure a match in both filtered sets. Then, distinct customer names can be selected.
Why the other options are wrong
- B. FULL OUTER JOIN would return all customers from both categories, including those who only bought one, which is too broad for the requirement of customers in 'both'.
- C. RIGHT JOIN is similar to LEFT JOIN but prioritizes the right table, which is not suitable for finding common records across two specific conditions.
- D. LEFT JOIN would return all customers, even those who only bought 'Electronics' or 'Apparel', and nulls for the other category, which doesn't fulfill the 'both categories' requirement.
SQL INNER JOIN
An SQL join type that returns only the rows from both tables where the join condition is met, effectively showing the intersection of the two datasets.
- Requires matching values in the join columns of both tables.
- Excludes rows that do not have a match in the other table.
- Often used to combine related data and filter for common elements.
Memory trick: Joins connect data, like puzzle pieces that fit.