Last answered:

22 May 2023

Posted on:

15 May 2023

0

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?
 

1 answers ( 0 marked as helpful)
Instructor
Posted on:

22 May 2023

0

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

Submit an answer