In SQL, why is "Distinct" not used in a subquery, when looking for some items "not showing up" in the other table?

Question

Usually when looking for some items not showing up in the other 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 line is with "distinct" added, so that the resulting table is smaller, and probably let the search for "not in" faster too.

So, won't using "distinct" be desirable? Often than not, I don't see it being used in the subquery in such a case. Is there advantage or disadvantage of using it? thanks.

Answer 1

When you call DISTINCT on a result, it required a scan through the list in order to find and remove the duplicated. This is a slow operation, and there is a good chance that the query as a whole will be faster without it.

Answer 2

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 as well.
Its just that, you will have to find which one works better for you (in terms of performance or other criteria).

EDIT: Here is the page which says, it is better to use NOT IN or LEFT JOIN.

Hope that helps.
Note: I don't have any experience on mysql

Answer 3

So, won't using "distinct" be desirable? Often than not, I don't see it being used in the subquery in such a case. Is there advantage or disadvantage of using it?

The result of these queries will always be the same.

MySQL's optimizer is well aware of that and will use the same plan for both queries.

If sentgifts.giftID is indexed, the query will be optimized to use the index_subquery access path: it will search the index and return TRUE on index miss or FALSE on first index hit.

This will be same whether you use DISTINCT or not.