Search code examples
sqlsql-serverselectsql-in

Question about subqueries, why there is a need for a IN clause


Here is the given code:

SELECT * 
FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS 
WHERE SALARY > 4500);

Why we can't simply write:

SELECT *
FROM CUSTOMERS
WHERE SALARY > 4500;

?

Here is another code:

SQL>

INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS 
WHERE ID IN (SELECT ID 
FROM CUSTOMERS);

Again, I don't understand the point of this subquery, wouldn't it bring the same result if I was to write, -

INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS;

?


Solution

  • If id is not unique, there is a difference between the two queries.

    The second query clearly returns all the customers who's salaries are above 4500. The first query returns all the customers who have a row where their salary was above 4500 - e.g., if the table represents all the history of salary changes, you'll get all the rows for customers who at one time had a salary greater than 4500.

    If id is unique, using such an in clause is indeed redundant.