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

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.

Answer 2

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.