SQL Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-24 : 09:16:33

Vishal writes "What will be the easiest way of finding out the non-matching rows from two tables. My problem is I have to find out the
non-matching rows from two tables (based on the Key) each will have around 300,000 records and multiple columns. For example
And also I have to check if records is present in one table and not in another. For Example


Table 1
Id      Name
1       abc
2       xyz
3       def

Table 3
Id      Name
1       jkl
2       xyz
4       opq


The output should be

Id      Name
1       abc
1       jkl
3       def
4       opq

SQL Server 6.5, Windows NT 4.6 service Pack 6

Thanks
Vishal"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-01-24 : 09:38:42

You could link them into access and do a find unmatched query OR just use that SQL in the query analyzer

Nazim
A custom title

1408 Posts

Posted - 2002-01-24 : 09:56:21

select tn1.id,tn1.name from tn1 , tn2
where tn1.id=tn2.id
and tn1.name<>tn2.name
union
select tn2.id,tn2.name from tn1 , tn2
where tn1.id=tn2.id
and tn1.name<>tn2.name

this should do what you are looking for . but there should be a better way .

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-01-24 : 10:13:26

This will determine what's in Table 1 but not in Table 2. You can easily change it to the other way around:

SELECT ID,Name FROM tn1 LEFT JOIN tn2 ON tn1.Name=tn2.Name WHERE tn2.Name IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources