CompTIA Data+ (DA0-002)Data MiningMedium
A data analyst is performing an exploratory data analysis on a sales dataset. They have two tables: `Customers` (CustomerID, CustomerName, City) and `Orders` (OrderID, CustomerID, OrderDate, Amount). The analyst needs a list of ALL customers, including those who have placed no orders, AND all orders, even those without a matching customer (due to data entry errors). Which SQL JOIN type should be used?
- AFULL OUTER JOIN
- BINNER JOIN
- CLEFT JOIN
- DRIGHT JOIN
Show answer & explanationAnswer & explanation
Correct answer: A. FULL OUTER JOIN
A FULL OUTER JOIN returns all rows from both tables, with NULLs in place of non-matching entries. This ensures that every customer is listed (even without orders) and every order is listed (even without a customer), fulfilling the requirement to see 'ALL customers' AND 'all orders'.
Why the other options are wrong
- B. INNER JOIN returns only matching rows from both tables, excluding non-matching customers or orders.
- C. LEFT JOIN returns all rows from the left table and matching rows from the right, excluding orders without customers.
- D. RIGHT JOIN returns all rows from the right table and matching rows from the left, excluding customers without orders.
SQL FULL OUTER JOIN
A FULL OUTER JOIN (or simply OUTER JOIN in some SQL dialects) returns all rows from both the left and right tables, combining matching rows and showing NULLs for non-matching entries from either side.
- Combines results of both LEFT JOIN and RIGHT JOIN.
- Useful for identifying data discrepancies between two tables.
- Can result in a large dataset if many non-matching rows exist.
Memory trick: Full Outer: Everyone's invited, even if they come alone!