Answer-first summary for fast verification
Answer: | customer_id | spend | store_id | |-------------|---------|----------| | a1 | 28.94 | s1 | | a3 | 874.12 | NULL | | a4 | 8.99 | s2 |
The correct answer is C because a LEFT JOIN preserves all records from the left table and includes matching records from the right table. When there is no match in the right table, NULL values are returned for the right table's columns. In this case: - **a1** has matching data in both tables (spend 28.94 and store_id s1) - **a3** exists only in the left table with spend 874.12, so store_id is NULL - **a4** has matching data in both tables (spend 8.99 and store_id s2) - **a2** is not included because it doesn't appear to exist in the left table based on the query results Option A is incorrect because it's missing a3. Option B is incorrect because it includes a 'units' column that shouldn't be in the result. Option D is incorrect because it includes a2 which doesn't appear to be in the left table. Option E is incorrect because it's missing a3.
Author: Keng Suppaseth
Ultimate access to all questions.
A data engineer is working with two tables. Each of these tables is displayed below in its entirety.
| customer_id | spend | units |
|---|---|---|
| a1 | 28.94 | 7 |
| a3 | 874.12 | 23 |
| a4 | 8.99 | 1 |
| 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 |