views:

44

answers:

2

Assuming I have two SQL tables consisting of a single column,

i.e.

Table 1               Table 2
a1                    a2
b1                    b2
c1                    c2

Is there a succinct SQL command to compare each record in one table against each record in the other? (and return true if any record from table 1 matches any record from table 2)

i.e.

if( a1 = a2 OR a1 = b2 OR a1 = c2 OR b1 = a2 OR b1 = b2...)

I want

If any record from table a matches table b (i.e., in a table of ints, they are the same int), return true.

+1  A: 

Why not simply

if exists (select 1 from T1 inner join TB on T1.Col = T2.Col)
mquander
A: 

A full join is well suited to finding differences. To find rows that are missing in either table:

select  *
from    t1
full join    
        t2
on      t1.col1 = t2.col1
where   t1.col1 is null
        or t2.col1 is null

This assumes that the single column is unique (i.e. has no duplicate values.)

Andomar