I am not clear why SQL Server eliminates dups from my query below

declare @s table ( id int );
insert into @s (id)
select 1 union
select 2 union
select 3 union
select 4 union
select 5

(5 row(s) affected)

select count(id) from @s where id <= 3
-----------
3

select count(id) from @s where id >= 2
-----------
4

HERE is where I am puzzled: I thought "where id <= 3 OR id >= 2"
should produce overlapping results, i.e. rows with id=2 and id=3 OR'ed
together would be repeted in the resulting rows, but the results are
as if I specified DISTINCT

select id from @s where id <= 3 OR id >= 2
id
-----------
1
2
3
4
5

*** expecetd to see 1,2,2,3,3,4,5

select count(id) from @s where id <= 3 OR id >= 2
-----------
5

*** expected to see 7

Why it is so?

The WHERE clause is used to specify predicates filtering the result set. It does not imply union operation. To
accomplish what you want you have to use UNION ALL:

SELECT id FROM @s WHERE id <= 3
UNION ALL
SELECT id FROM @s WHERE id >= 2;

--
Plamen Ratchev
http://www.SQLStudio.com