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

Question

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;

?

I can't think of any good reason why you wouldn't just write that as `SELECT * FROM CUSTOMERS WHERE SALARY > 4500;` Same with the second. I assume these are just examples from a class; perhaps just to showcase how `IN` can be used? — JNevill, Jul 08 '19 at 13:01
It is not said whether it's a unique or not, but I presume it's not. — user, Jul 08 '19 at 13:02
In the second one, I wonder if having `NULL` `ID`'s in some rows will make a difference. Odd examples to be sure. If you intend to insert all rows from the customers table into customers_bkp, the IN-clause is definitely not needed. The question is if some constellation of NULL-marks will make a difference. — Lasse V. Karlsen, Jul 08 '19 at 13:16
**However**, code should never be used as "this is what we want to achieve". Is there any description along with these code examples that explains what the goal is? Code is implementation, there should be a separate text for the goal/design. — Lasse V. Karlsen, Jul 08 '19 at 13:17

score 1 · Accepted Answer · answered Jul 08 '19 at 13:07

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.

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

1 Answers1