retagged by
579 views
1 votes
1 votes
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)
retagged by

1 Answer

0 votes
0 votes
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.

Related questions

0 votes
0 votes
1 answer
3
rayhanrjt asked Jan 6, 2023
719 views
Write SQL command to find DepartmentID, EmployeeName from Employee table whose average salary is above 20000.
2 votes
2 votes
1 answer
4
Subhrangsu asked Jun 18, 2022
431 views
Write SQL query to show all employees hired on June 4,1984 (non-default format)emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)