CODEWITHSUNDEEP
sql
Ram
Ram

Reputation: 41

SQL to get the common rows from two tables

I have two tables T1 and T2.

Can any one please help with a SQL query which will fetch the common rows from these two tables? (Assume T1 and T2 has 100 columns each)

P.S : I guess INNER JOIN on each of the columns will not be a good idea.

Thanks

Upvotes: 3

Views: 120585

Answers (5)

Thisizraaz
Thisizraaz

Reputation: 51

Yes, INNER JOIN will work.

eg. SELECT (column_1, column_2, ... column_n) FROM T1 JOIN T2 ON (condition) WHERE (condition)

This query will fetch the common records (intersection of) in both the tables according to ON condition.

Upvotes: 5

Hitesh Jain
Hitesh Jain

Reputation: 21

SELECT NAME FROM Sample1
UNION 
SELECT NAME FROM Sample2;

EX: Table Sample1
ID NAME
-------
1   A
-------
2   B
-------

Table Sample 2
ID NAME
--------
1   C
--------
2   B 
------

Output
NAME
----
A
---
B
---
C
---

Upvotes: -2

René Nyffenegger
René Nyffenegger

Reputation: 40499

select
  t1.*
from
  t1, t2
where
 (
  (t1.col1 is null and t2.col1 is null) or (
  (t1.col1         =   t2.col1        )
 ) and
 (
  (t1.col2 is null and t2.col2 is null) or (
  (t1.col2         =   t2.col2        )
 ) and
 (
  (t1.col3 is null and t2.col3 is null) or (
  (t1.col3         =   t2.col3        )
 ) and
 ....
 (
  (t1.col100 is null and t2.col100 is null) or (
  (t1.col100         =   t2.col100        )
 )

Upvotes: -1

Himanshu Upadhyay
Himanshu Upadhyay

Reputation: 743

If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records.

SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2

If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.

SELECT column1, column2
FROM table1
WHERE column1 IN
(
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
)

Upvotes: 8

Steven Eccles
Steven Eccles

Reputation: 239

Use INTERSECT

SELECT * FROM T1
INTERSECT
SELECT * FROM T2

Upvotes: 5

Related Questions