Databricks Data Engineer Associate Question 182
Single answerYou are working with two datasets in Databricks: employees and departments. The employees table contains columns employee_id, name, and department_id. The departments table contains columns department_id and department_name. You run the following query:
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;
Which result will this query return?
- A
All employees with their corresponding department names, and nulls for employees without a matching department.
- B
Only employees who have a matching department, excluding employees without a department.
- C
All departments with their corresponding employee names, and nulls for departments without employees.
- D
A Cartesian product of all employees and departments, showing all possible combinations.
Show answer and explanation
Correct answer: A
Explanation
A LEFT JOIN ensures that all rows from the left table (employees) are included in the result set, even if there is no matching row in the right table (departments). For rows where no match is found, the columns from the right table (department_name) will contain null values.
- A. Correct.
Correct. A LEFT JOIN returns all records from the
employeestable (the left table) and matches records from thedepartmentstable (the right table). If no match is found, nulls are returned for the right table's columns. - B. Incorrect.
Incorrect. This describes an INNER JOIN, which only includes rows where there is a match between the two tables.
- C. Incorrect.
Incorrect. This describes a RIGHT JOIN or FULL OUTER JOIN, where all records from the
departmentstable would be included, even if no match exists in theemployeestable. - D. Incorrect.
Incorrect. This describes a CROSS JOIN, which generates a Cartesian product of all rows from both tables.