WHERE IN CLAUSE WITH same literal

[skcvasanth]

Hi, I'm working on a application program where I need to use where in function to include my literals which are received during run time. Assume below is my table.
Table name : Master
SEQ_NO NAME
1 XXXX
2 YYYY
1 ZZZZ

My queries are..
The literals will change based on the need.

select * from master where seq_no in(1,2)
This query is returning just 3 rows which seems to be OK and I don't have questions on that.
select * from master where seq_no in(1,1)
This query is returning just 2 rows. It makes sense because only 2 rows are available. I'm using DB2 and the question is how DB2 decides it's the same row and eliminates the second literal and not returning 4 same rows ? if the literals are same, it just ignores the 2nd one ?
select * from master where seq_no in(2,2)
This query is returning just 1 row. It makes sense because only 1 row is available. I'm using DB2 and the question is.. how DB2 decides it's the same row and eliminates the second literal and not returning 2 same rows ? if the literals are same, it just ignores the 2nd one ?


Thanks for your help in Advance!
CVR

 

[SkipVought]

HI,

(1,1) returns 1 row despite there are 2 rows in your example.

(2,2) returns 2 rows despite there is 1 row in your example.

Did I get this correct?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[skcvasanth]

thanks for the response skipvought!

Sorry - The question I placed was exchanged between for some reason. I didn't mean that way. I corrected my most.

(1,1) returns 2 rows. -- > how DB2 decides it's the same row and eliminates the second literal and not returning 4 same rows ? if the literals are same, it just ignores the 2nd one ?

(2,2) returns 1 rows. --> how DB2 decides it's the same row and eliminates the second literal and not returning 2 same rows ? if the literals are same, it just ignores the 2nd one ?

 

[SkipVought]

Ahhhh!

I believe that I see what your are assuming. You're saying that you expect ...

IN (1) to return
[pre]
SEQ_NO NAME
1 XXXX
1 ZZZZ
[/pre]

IN (1,1) to return
[pre]
SEQ_NO NAME
1 XXXX
1 ZZZZ
1 XXXX
1 ZZZZ
[/pre]

IN (1,1,1) to return
[pre]
SEQ_NO NAME
1 XXXX
1 ZZZZ
1 XXXX
1 ZZZZ
1 XXXX
1 ZZZZ
[/pre]

...etc.

Does not work that way. The IN() statement is like a list of ORs. All it's requiring is to return the rows that meet the criteria: and there are only TWO rows that meet that criteria.

If you want what you expect, then use UNIONs
[pre]
select * from master where seq_no in(1,1)
UNION
select * from master where seq_no in(1,1)
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]