Answer-first summary for fast verification
Answer: | customer_id | spend | store_id | |-------------|---------|----------| | a1 | 28.94 | s1 | | a3 | 874.12 | NULL | | a4 | 8.99 | s2 |
## Explanation The correct answer is **C** because: 1. **LEFT JOIN** returns all rows from the left table (`sales`) and matching rows from the right table (`favorite_stores`). 2. When there's no match in the right table, `NULL` values are returned for the right table's columns. 3. The query selects only `sales.customer_id`, `sales.spend`, and `favorite_stores.store_id`. **Analysis of each row:** - **a1**: Exists in both tables → `store_id = s1` - **a3**: Exists only in `sales` table → `store_id = NULL` (no match in `favorite_stores`) - **a4**: Exists in both tables → `store_id = s2` - **a2**: Exists only in `favorite_stores` → NOT included because LEFT JOIN starts from `sales` table **Why other options are incorrect:** - **A**: Missing row for `a3` (should be included with NULL store_id) - **B**: Includes `units` column (not selected in query) and missing row for `a3` - **D**: Includes row for `a2` (should not be included as it's not in `sales` table) - **E**: Includes row for `a2` (should not be included) and missing row for `a3`
Author: Keng Suppaseth
Ultimate access to all questions.
No comments yet.
A data engineer is working with two tables. Each of these tables is displayed below in its entirety.
sales
| customer_id | spend | units |
|---|---|---|
| a1 | 28.94 | 7 |
| a3 | 874.12 | 23 |
| a4 | 8.99 | 1 |
favorite_stores
| customer_id | store_id |
|---|---|
| a1 | s1 |
| a2 | s1 |
| a4 | s2 |
The data engineer runs the following query to join these tables together:
SELECT
sales.customer_id,
sales.spend,
favorite_stores.store_id
FROM sales
LEFT JOIN favorite_stores
ON sales.customer_id = favorite_stores.customer_id;
SELECT
sales.customer_id,
sales.spend,
favorite_stores.store_id
FROM sales
LEFT JOIN favorite_stores
ON sales.customer_id = favorite_stores.customer_id;
Which of the following will be returned by the above query?
A
| customer_id | spend | store_id |
|---|---|---|
| a1 | 28.94 | s1 |
| a4 | 8.99 | s2 |
B
| customer_id | spend | units | store_id |
|---|---|---|---|
| a1 | 28.94 | 7 | s1 |
| a4 | 8.99 | 1 | s2 |
C
| customer_id | spend | store_id |
|---|---|---|
| a1 | 28.94 | s1 |
| a3 | 874.12 | NULL |
| a4 | 8.99 | s2 |
D
| customer_id | spend | store_id |
|---|---|---|
| a1 | 28.94 | s1 |
| a2 | NULL | s1 |
| a3 | 874.12 | NULL |
| a4 | 8.99 | s2 |
E
| customer_id | spend | store_id |
|---|---|---|
| a1 | 28.94 | s1 |
| a2 | NULL | s1 |
| a4 | 8.99 | s2 |