Removing Duplicates rows from Inner Join

[asgars]

i have two tables, tab1 having N1 col and tab2 N2 col. now N1 is subset
of N2.
I need the information from tab2 (having N2) of all rows having the
matching entry in N1 in tab1.
For this i am using Inner Join on cols N1 and n2. But result is giving
duplicate rows. Can anyone suggest how do u i remove those duplicate
rows? or may be a better way to do the above work... Thanks

[markc600]

Sounds like you should be using EXISTS
rather than a join

SELECT * FROM tab2
WHERE EXISTS (SELECT * FROM tab1 WHERE tab1.N1=tab2.N2)

[asgars]

thanks mark
but i have tables with large data and running this querry is taking a
lot of time.
can u suggest a better method or optimize this querry?

[amit.tzafrir]

Did you try to use DISTINCT to remove the duplicates?
SELECT DISTINCT tab2.* FROM tab2 INNER JOIN tab1 ON tab2.N2 = tab1.N1

[markc600]

Can you post your DDL including indexes

[Erland Sommarskog]

asgars (as****@gmail.com) writes:

thanks mark
but i have tables with large data and running this querry is taking a
lot of time.
can u suggest a better method or optimize this querry?

Which query? It's very difficult to suggest optimizations to a query
without seeing it, and without knowledge of the tables.

Please post:

o The query you are using now.
o CREATE TABLE and CREATE INDEX statements for the inolved tables.
o Some indication on number of rows in the table.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx