CompTIA Data+ (DA0-002)Data MiningMedium
A business intelligence analyst is building a report that requires combining customer demographic data with their sales transaction history. They have two tables: `Customers` (CustomerID, Name, Address) and `Transactions` (TransactionID, CustomerID, Product, Amount). They only want to see customers who have made at least one transaction and the details of those specific transactions. Which SQL JOIN type should be used?
- ALEFT JOIN
- BCROSS JOIN
- CINNER JOIN
- DFULL OUTER JOIN
Show answer & explanationAnswer & explanation
Correct answer: C. INNER JOIN
An INNER JOIN returns only the rows where there is a match in both tables. Since the analyst only wants to see customers who have made 'at least one transaction' and the details of 'those specific transactions', an INNER JOIN will correctly filter out customers with no transactions and transactions without a matching customer.
Why the other options are wrong
- A. LEFT JOIN would include all customers, even those with no transactions, which is not desired.
- B. CROSS JOIN creates a Cartesian product, returning every possible combination of rows, which is incorrect for this scenario.
- D. FULL OUTER JOIN would include customers with no transactions and transactions with no customers, which is not desired.
SQL INNER JOIN
An INNER JOIN returns only the rows that have matching values in both tables involved in the join. It effectively selects the intersection of the two tables based on the join condition.
- Most common type of join.
- Requires a matching key in both tables.
- Excludes rows that do not have a match in the other table.
Memory trick: Inner Join: Only the overlap, no stragglers!