Examine the contents of these tables: Now examine the expected results for a user with privileges to access the table: Which query returns the expected results? A. B. C. D.

Question

Exhibit

Options

• ASELECT e.emp_id, d.dept_name FROM employee e, department d WHERE d.dept_id = e.dept_id;
• BSELECT emp_id, (SELECT dept_name FROM department WHERE dept_id = employee.dept_id) FROM employee;
• CSELECT e.emp_id, d.dept_name FROM employee e LEFT JOIN department d ON d.dept_id = e.dept_id WHERE e.dept_id IS NULL;
• DSELECT emp_id, (SELECT dept_name FROM department) dept_name FROM employee WHERE dept_id = employee.dept_id;

• A
  7% (2)
• B
  72% (21)
• C
  3% (1)
• D
  17% (5)

Explanation

Option B uses a correlated subquery that executes for each row in employee, looking up the matching dept_name from department where dept_id matches - this correctly returns all employees, including those with no department (returning NULL for dept_name), which matches the expected output.

Why the distractors fail:

• A uses an implicit inner join (FROM employee e, department d WHERE ...), which silently excludes any employee whose dept_id is NULL or has no matching department row - missing rows the expected results include.
• C applies a LEFT JOIN but then adds WHERE e.dept_id IS NULL, turning it into a filter that returns only unmatched employees, the opposite of a complete result set.
• D's subquery (SELECT dept_name FROM department) has no correlated condition linking it to the outer query's dept_id, so it returns multiple rows and causes a runtime error; the outer WHERE dept_id = employee.dept_id also compares a column to itself (always true), which is logically broken.

Memory tip: When you see a subquery in the SELECT clause referencing an outer table's column in its WHERE clause (e.g., WHERE dept_id = employee.dept_id), that's a correlated subquery - it's the SQL equivalent of a LEFT JOIN that returns NULL for non-matches, making it your go-to when the expected results include rows with no corresponding child record.

No community discussion yet for this question.