CODEWITHSUNDEEP
sql-servert-sql
thx0125
thx0125

Reputation: 127

SQL Server : how to do an inner join by various columns

I have two tables, both Table1 and Table2, share 4 columns named C1, C2, C3 and C4; those columns store a numerical sequence from 1 to 56; also, there can be not duplicated numbers in one row, and the numbers always go from lowest to highest.

Example:

Table1

C1 C2 C3 C4
 1  6 10 44
 5  7 23 50
45 46 52 56

Now I have to check for sequences that appear in the two tables, but instead of a join on the four columns 

select * 
from table1 a 
inner join table 2 on a.c1 = b.c1 and a.c2 = b.c2 and a.c3 = b.c3 and a.c4 = b.c4

is there another way to do it?

Upvotes: 1

Views: 43

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Yes, there is a different way, use INTERSECT.

SELECT c1, c2, c3, c4
FROM table1
INTERSECT
SELECT c1, c2, c3, c4
FROM table2

Note: This will not tell you how many times a match is found, it will show the unique matches between the tables

Upvotes: 3

Related Questions