Comparing WHERE-IN / WHERE-EXISTS / JOIN with an example
Suppose we are tasked to extract the information of employees whose salary is > 150000. Which among the 3 codes is the most efficient in delivering the results?
-------JOIN--------
SELECT e.*, s.salary
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
WHERE
salary > 150000
GROUP BY emp_no;
-------WHERE-EXISTS--------
SELECT *
FROM employees e
WHERE
EXISTS( SELECT *
FROM salaries s
WHERE
e.emp_no = s.emp_no
AND s.salary > 150000);
-------WHERE-IN--------
SELECT *
FROM employees e
WHERE
e.emp_no IN (SELECT
s.emp_no
FROM salaries s
WHERE
s.salary > 150000);
Is it also correct to say that only the JOIN statement can return columns from both tables, whereas the WHERE-IN and WHERE-EXISTS subqueries can only return columns from the outer query?
Hi Jake!
Thanks for reaching out.
I suppose the first one is the most efficient. However, all of these solutions are valid.
Yes, these subqueries can only return columns from the outer query.
Hope this helps.
Best,
Tsvetelin