CompTIA Data+ (DA0-002)Data MiningMedium
A data analyst is querying a customer database to find all customers who have placed an order in the last 30 days but have not yet received a shipping confirmation. The database contains two tables: `Orders` (OrderID, CustomerID, OrderDate) and `Shipments` (ShipmentID, OrderID, ConfirmationDate). Which type of SQL join should the analyst use, combined with an appropriate WHERE clause, to achieve this result?
- AFULL OUTER JOIN
- BINNER JOIN
- CLEFT JOIN
- DRIGHT JOIN
Show answer & explanationAnswer & explanation
Correct answer: C. LEFT JOIN
A LEFT JOIN is required to return all records from the 'left' table (Orders) and the matching records from the 'right' table (Shipments). By then filtering for `Shipments.ConfirmationDate IS NULL`, you identify orders that have not yet received a shipping confirmation, while still retaining all orders from the Orders table.
Why the other options are wrong
- A. FULL OUTER JOIN would return all orders and all shipments, including non-matching ones from both sides, making it harder to filter specifically for unconfirmed orders.
- B. INNER JOIN would only return orders that have a matching shipment record, excluding those without a confirmation.
- D. RIGHT JOIN would return all shipments, even those without a corresponding order, which is not the goal.
SQL LEFT JOIN
A type of SQL join that returns all records from the left table (first table in the FROM clause) and the matching records from the right table. If there is no match, the right side will contain NULL values.
- Used to find records in one table that do or do not have corresponding records in another.
- Preserves all rows from the 'left' table.
- Often combined with `WHERE right_table.column IS NULL` to find unmatched records.
Memory trick: Joining tables is like connecting puzzle pieces, but sometimes you need all of one side.