CODEWITHSUNDEEP
sqlsql-server
Tinydan
Tinydan

Reputation: 935

How to compare one table's column against another?

I'm currently working on merging two tables and I am trying to verify if there are any duplicates across two columns. I suppose I'm looking for a Query that lats me compare every value of the columns much like: 

WHERE 'column1' = 'value1'

but in place of 'value1' it'd be 'column2'

WHERE 'column1' = 'column2'

There would also be a bit of a function to count any duplicates.

Does anyone know if there's any function like this? So far I've drawn up blanks.

Cheers!

Doesn't matter - seems yesterday I made a bit problem over nothing. Thanks for getting my mind whirring. I'll Update with the result when I get some output.

Upvotes: 0

Views: 11548

Answers (4)

Gavin Doris
Gavin Doris

Reputation: 71

I would do it with a FULL OUTER JOIN to get comparison of both tables

SELECT A.column1, B.column1
 FROM table1 A 
FULL OUTER JOIN table2 B
ON A.column1=B.column1

This will give you all results on the table but will show null values where it is in one but not the other.

Upvotes: 0

Inus C
Inus C

Reputation: 1551

You might want to look into INTERSECT and EXCEPT

SELECT column1
FROM table1
INTERSECT
SELECT column2
FROM table2

This will give you all the result where they are the same

Refer to : http://msdn.microsoft.com/en-us/library/ms188055.aspx

Upvotes: 0

zxc
zxc

Reputation: 1526

You can try both but i prefer the first example

select count(*)   
from table1 as t1   
inner join table2 as t2   
on (values you need to compare)

or

select count(*)   
(  
from select *   
from table1 as t1   
where column is exists(select * from table2 as t2 on (values you need to compare))  
) as table

Upvotes: 0

Ron.B.I
Ron.B.I

Reputation: 2766

Use something similar to:

SELECT *
FROM TABLE A INNER JOIN TABLE B ON (//Whatever condition you need)
WHERE A.column1=B.column2

Upvotes: 1

Related Questions