SQL query

Question

Which of the following queries has better performance while displaying the
details of the employees who are managed by the same manager and work
in the same department as the employees with identification numbers 174
and 178 ?

(A) SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN (SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (178, 174))
AND employee_id NOT IN (178, 174)

(B) SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id) IN (SELECT manager_id
FROM employees
WHERE employee_id IN (178, 174))
AND (department_id) IN (SELECT department_id
FROM employees
WHERE employee_id IN (178, 174))
AND employee_id NOT IN (178, 174)

(C) SELECT a.employee_id, a.manager_id, a.department_id
FROM employees a
WHERE exists (SELECT * FROM employees b
WHERE b.employee_id IN (178, 174)
AND a.manager_id = b.manager_id)
AND exists (SELECT * FROM employees c
WHERE c.employee_id IN (178, 174)
AND a.department_id = c.department_id)
AND a.employee_id NOT IN (178, 174)

(D) SELECT a.employee_id, a.manager_id, a.department_id
FROM employees a, employees b
WHERE b.employee_id IN (178, 174)
AND a.manager_id = b.manager_id
AND a.department_id = b.department_id
AND a.employee_id NOT IN (178, 174)

Comments

i am confused between A and D .ruling out B and C because there is a seperate query for manager and department in them which is not needed,i guess.

and between A and D,i guess A is efficient as here JOIN in not there wheras D had JOIN.

which is the coorect answer??pls correct me

Answer 1

As we know , Cartesian product is performed while performing JOIN and after that selection of rows takes place.
The Cartesian Product is expensive in terms of memory required and processing time.
So nested Query or Correlated query is the better option instead of JOIN.

Option D is not suitable.

Using Correlated Sub query decreases performance.

In Option A, Only one nested query with multi columned is used.
So I think Option A is best in terms of performance.

Correct me if I am wrong.