Select Query with IN clause - having duplicate values in IN clause

Question

I have a query as below,

SELECT count(*) from Employee where e_id IN (121, 234, 536, 234).

In above query, 234 is repeated twice.

But the above query returns result with count = 3 instead of 4.

My Question is how duplicate data gets filtered in Select query when i have not used DISTINCT.

or how does database treats IN clause, is it sort of List(duplicate values) or Set(unique values) or neither.

If you wish to know the performance impacts, have a look here stackoverflow.com/questions/10944339/… — Nikhil Kuriakose
– Nikhil Kuriakose, Commented May 22, 2017 at 8:43

Gordon Linoff · Accepted Answer · 2016-09-06 12:05:37Z

5

The WHERE clause only filters rows. It does not multiply them.

So, if a row matches one condition or all conditions doesn't matter. The row simply is filtered or not filtered.

If you want duplicates, then use a JOIN:

select count(*)
from employee e join
     (select 121 as e_id union all
      select 234 union all
      select 536 union all
      select 234
     ) matches
     using (e_id);

answered Sep 6, 2016 at 12:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ankur Singhal Over a year ago

i hve further updated my q's, how does database treats IN clause, is it sort of List(duplicate values) or Set(unique values) or neither.

Barmar Over a year ago

@ankur-singhal What difference does it make? Either a row is in the result of not. If it matches twice, it's in the result.

redneb Over a year ago

@ankur-singhal it is treated as a set. See also my answer.

redneb · Accepted Answer · 2016-09-07 23:19:58Z

5

e_id IN (121, 234, 536, 234) works as predicate: for every row of Employee, the value of e_id is checked to see if it matches with any of the values in list. So (121, 234, 536, 234) is treated as a set here.

edited Sep 7, 2016 at 23:19

answered Sep 6, 2016 at 12:05

redneb

24.4k6 gold badges50 silver badges57 bronze badges

Collectives™ on Stack Overflow

Select Query with IN clause - having duplicate values in IN clause

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related