CODEWITHSUNDEEP
sqlsql-serverselectsql-in
user
user

Reputation: 253

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;

?

Upvotes: 1

Views: 79

Answers (1)

Mureinik
Mureinik

Reputation: 311978

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.

Upvotes: 1

Related Questions