SQL Server: Find Common Rows in Tables using INTERSECT

Suppose you have a SQL Server database with tables having similar structures and you want to find out similar rows among these tables. The most common method is to use a JOIN on all the columns.

Consider the following tables

create table #t1 (id int, names varchar(100))
create table #t2 (id int, names varchar(100))
create table #t3 (id int, names varchar(100))

insert into #t1
select 1,'test1' union all
select 2,'test2'
insert into #t2
select 1,'test1' union all
select 3,'test2'
insert into #t3
select 1,'test1' union all
select 20,'test2'

select t1.* from #t1 as t1
inner join #t2 as t2 on t1.id=t2.id
inner join #t3 as t3 on t1.id=t3.id

OUTPUT

The above select statement joins all these three tables by all columns to get similar rows among these tables. Then these similar rows in all three tables are displayed.

The problem with this approach is that if the table has many columns, you will need to specify all the columns in the join statement, which will make maintaining these queries a nightmare.

Another easy method to find common rows without actually specifying any columns is to use the INTERSECT Operator

OUTPUT

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan