Fetching duplicate rows uisng IN clause

I have two rows in table1 and these two rows are then duplicated in
table2 making four rows. I then try and use the following query to
select the four rows from table2.
select * from Product P
where P.ProductVersionID in (select ProductVersionID from PartSet where
SetID = ?)
Running the above query only return the two rows from Product instead
of the four rows that exist in SetPart.
Now if I run the query below I get back the four rows that I want.
select * from Product P
inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
SP.SetID = ?
Can someone please explain to me why the query with the IN clause
doesn't return the four rows that I want?
DML:
CREATE TABLE SetPart (
SetID int NOT NULL,
ProductVersionID int NOT NULL,
ProductTypeID int NOT NULL
)
GO
CREATE TABLE Product (
ProductVersionID int NOT NULL,
ProductName varchar (20) NOT NULL
)
GOmohaaron@.gmail.com wrote:
> I have two rows in table1 and these two rows are then duplicated in
> table2 making four rows. I then try and use the following query to
> select the four rows from table2.
> select * from Product P
> where P.ProductVersionID in (select ProductVersionID from PartSet where
> SetID = ?)
> Running the above query only return the two rows from Product instead
> of the four rows that exist in SetPart.
> Now if I run the query below I get back the four rows that I want.
> select * from Product P
> inner join SetPart SP on P.ProductVersionID = SP.ProductVersionID where
> SP.SetID = ?
> Can someone please explain to me why the query with the IN clause
> doesn't return the four rows that I want?
Because IN is not a JOIN. IN just determines whether a row (or rows)
exists in the subquery. Apparently you want the join instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Can someone please explain to me why the query with the IN clause doesn't
IN() implies only the distinct rows in a subquery result. It is different
from an INNER JOIN which returns all rows based on the column used in the
join. If the columns participating in the INNER JOIN are not unique
duplicates can occur in the result.
In general, keyless tables are practically useless and it is recommended
that every table must have a column or set of columns that can uniquely
identify a row in the table.
Anith