In SQL, why is “Distinct” not used in a subquery, when searching for some items, it “doesn't appear” in another table?

Question

Usually when searching for some elements that are not displayed in another table, we can use:

select * from gifts where giftID not in (select giftID from sentgifts);

or

select * from gifts where giftID not in (select distinct giftID from sentgifts);

the second row is added with the addition of "different", so that the resulting table is smaller and probably allows faster searches "not in".

So will not use the "excellent"? Often, I don’t see that it is used in a subquery in such a case. Is there an advantage or disadvantage to using it? thank.

Answer 1

DISTINCT , . , , .

Answer 2

, ""? , , . ?

.

MySQL .

sentgifts.giftID, index_subquery: TRUE FALSE .

, DISTINCT .

Answer 3

select * from gifts where not exists 
(select giftID from sentgifts where sentgifts.giftID = gifts.giftID);

I think you can write the same query in the above style. That's right, you will need to find which one works best for you (in terms of performance or other criteria).

EDIT: Here's a page that says it's better to use NOT INor LEFT JOIN.

Hope this helps. Note. I have no experience withmysql