How do I handle long comparisons in SQL

Question

I have a pathological issue with SQL, so I usually sort all of my database issues by building quickie software applications to handle my SQL problems.

(as I am also doing in this case)

Thanks to StackOverflow I think I can be shamed into correctness, so I would like to learn how to make this kind of SQL troubleshooting in actual SQL or T-SQL itself (if possible):

I have:

database DB1 Table A (unitNo, BuildingNo)

database DB2 Table B (unitNo, BuildingNo)

1. I want to come up with the Units (unit numbers) existing on Table A from Database DB1 which do not exist on table B from Database DB2 and vice versa.

2. There can be more than one unit with the same unit number, this will happen because the same unit number can be given to units of different buildings.

3. I do not have write access to any of the databases.

I hope this isn't seen as a "gimme teh codz" post, I would like to know how people with more SQL fluency than me sort this kind of algorithm, posts to tutorials or hints are more than welcomed, no full code required, but if it helps to makes sense, then please do.

At first I thought I could just get all the unit numbers from one table and exclude them on a select from the other like so:

select concated.unit from 
( SELECT (  unitNo + ',' + CONVERT(varchar(12), BuildingNo) ) as unit
FROM A) concated 
having concated.unit not in

(
'201,1',
'202,1',
'203,1',
'204,1',
'205,1',
'206,1',
[...]

This would usually work, but the number of units from any one table is immense, trying this crashes the SQL server with:

"ran out of stack space"

Thanks,

Ric

Answer 1

I think you're looking for full outer join. Which gives you the unit numbers you asked for in Part 1.

Select 
   A.UnitNumber, B.UnitNumber
from
   DB1.dbo.TableA A FULL OUTER JOIN DB2.dbo.TableB B 
       on A.UnitNumber = B.UnitNumber
       and A.BuildingNumber = B.BuildingNumber
Where
   A.UnitNumber is null or B.UnitNumber is null

Other queries that might be of interest I've outlined below.

This gives you the records in A not in B.

Select
    A.UnitNumber
From
    DB1.dbo.TableA A Left Join DB2.dbo.TableB B 
        on A.UnitNumber = B.UnitNumber 
        and A.BuildingNumber = B.BuildingNumber
Where
    B.UnitNumber is null

And you just reverse it to find the records in B that aren't in A.

Select
    B.UnitNumber
From
    DB2.dbo.TableB B left join DB1.dbo.TableA A 
        on B.UnitNumber = A.UnitNumber
        and B.BuildingNumber = A.BuildingNumber
Where
    A.UnitNumber is null

Answer 2

As JPunyon said, but if you want them all in one list then something like:

Select 
    [UnitNumber] = COALESCE(A.UnitNumber, B.UnitNumber),
    [Source] = CASE WHEN A.UnitNumber IS NOT NULL THEN 'A' ELSE 'B' END
from
    DB1.dbo.TableA A
    FULL OUTER JOIN DB2.dbo.TableB B 
        on A.UnitNumber = B.UnitNumber
Where
    A.UnitNumber is null or B.UnitNumber is null
ORDER BY COALESCE(A.UnitNumber, B.UnitNumber)