Databricks Certified Data Engineer - Associate
Get started today
Ultimate access to all questions.
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
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
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 is the correct result of this query?
Real Exam
Community
KKeng
A
customer_idspendstore_id
a1 28.94 s1
a2 NULL s1
a4 8.99 s2
customer_idspendstore_id
a1 28.94 s1
a2 NULL s1
a4 8.99 s2
B
customer_idspendstore_id
a1 28.94 s1
a4 8.99 s2
customer_idspendstore_id
a1 28.94 s1
a4 8.99 s2
C
customer_idspend store_id
a1 28.94 s1
a3 874.12 NULL
a4 8.99 s2
customer_idspend store_id
a1 28.94 s1
a3 874.12 NULL
a4 8.99 s2
D
customer_idspend store_id
a1 28.94 s1
a2 NULL s1
a3 874.12 NULL
a4 8.99 s2
customer_idspend store_id
a1 28.94 s1
a2 NULL s1
a3 874.12 NULL
a4 8.99 s2
Explanation:
Explanation
This question tests understanding of LEFT JOIN behavior in SQL.
Key Concepts:
- LEFT JOIN returns all rows from the left table (
sales), and matched rows from the right table (favorite_stores) - If there's no match in the right table,
NULLvalues are returned for columns from the right table - Rows that exist only in the right table but not in the left table do not appear in the result
Analysis:
Left Table (sales):
a1→ spend=28.94a3→ spend=874.12a4→ spend=8.99
Right Table (favorite_stores):
a1→ store_id=s1a2→ store_id=s1a4→ store_id=s2
LEFT JOIN Results:
a1(from sales) matchesa1(from favorite_stores) →store_id = s1a3(from sales) has no match in favorite_stores →store_id = NULLa4(from sales) matchesa4(from favorite_stores) →store_id = s2a2exists in favorite_stores but NOT in sales → does NOT appear in results (because LEFT JOIN starts from left table)
Why Other Options Are Incorrect:
- Option A: Includes
a2withstore_id = s1→ Wrong becausea2is not in the sales table - Option B: Missing
a3→ Wrong because LEFT JOIN should include all rows from sales table - Option D: Includes
a2→ Wrong for same reason as Option A
Important Note:
The customer_id a2 appears in the favorite_stores table but NOT in the sales table. In a LEFT JOIN, only rows from the left table (sales) are guaranteed to appear. Since a2 is not in sales, it will not appear in the result set, even though it exists in favorite_stores.
This demonstrates a common misconception about LEFT JOINs - they preserve all rows from the left table, not all matching customer IDs from both tables.
Comments
Loading comments...