Help with primary keys

I have two tables with similar primary keys, table a and table b, and I want to find out all the key values that are disimilar between the tables. Can this be done with a select? if so what would it be.

If I understand the problem correctly one way to do this is to use a FULL JOIN; maybe something like:

declare @.tableA table (aKey int)
declare @.tableB table (bKey int)

insert into @.tableA
select 1 union all select 2 union all select 3 union all
select 5 union all select 6 union all select 8

insert into @.tableB
select 1 union all select 3 union all select 4 union all
select 6 union all select 7 union all select 8

select coalesce (aKey, bKey) as [Key],
case when aKey is null then 'Table B' else 'Table A'
end as sourceTable
from @.tableA
full join @.tableB
on aKey = bKey
where aKey is null
or bKey is null
order by coalesce (aKey, bKey)

/*
Key sourceTable
-- --
2 Table A
4 Table B
5 Table A
7 Table B
*/

Now that I think about it, a better way to do this is probably to do this differently; hang on and I'll get you a better method. This might perform a little better:

declare @.tableA table (aKey int)
declare @.tableB table (bKey int)

insert into @.tableA
select 1 union all select 2 union all select 3 union all
select 5 union all select 6 union all select 8

insert into @.tableB
select 1 union all select 3 union all select 4 union all
select 6 union all select 7 union all select 8

select 'TableA' as SourceTable,
aKey as [Key]
from @.tableA a
where not exists
( select 0 from @.tableB b
where aKey = bKey
)
union all
select 'TableB' as SourceTable,
bKey as [Key]
from @.tableB a
where not exists
( select 0 from @.tableA b
where aKey = bKey
)
order by [Key]

/*
SourceTable Key
-- --
TableA 2
TableB 4
TableA 5
TableB 7
*/

|||

If I understand you correctly, you want to find rows in TableA that do not exist in TableB, and conversely, rows in TableB that do not exist in TableA.

Code Snippet

SELECT
'TableA',
PKColumn
FROM TABLEA
WHERE PKColumn NOT IN ( SELECT PKColumn
FROM TableB
)
UNION

SELECT
'TableB',
PKColumn
FROM TABLEB
WHERE PKColumn NOT IN ( SELECT PKColumn
FROM TableA
)

If you are using SQL 2005, you could use a EXCEPT JOIN.

|||As usual, Arnie makes good points. Be aware of the EXCEPT join. In many cases it will be slower than the query Arnie put together. I try to avoid using the EXCEPT join.|||

Our (Kent's and mine) suggested solutions are virtually identical -since you are seeking PKeys, there are no duplicates -so DISTINCT is not necessary. And the query processor has to read the entire index anyway, so EXISTS and NOT IN have the same effect for this circumstance.

And if you are using SQL 2005, you could use a FULL OUTER JOIN. Example:

Code Snippet

SET NOCOUNT ON

DECLARE @.TableA table
( PKColumn int )

DECLARE @.Tableb table
( PKColumn int )

INSERT INTO @.TableA VALUES ( 1 )
INSERT INTO @.TableA VALUES ( 2 )
INSERT INTO @.TableA VALUES ( 3 )
INSERT INTO @.TableA VALUES ( 4 )
INSERT INTO @.TableA VALUES ( 5 )

INSERT INTO @.TableB VALUES ( 3 )
INSERT INTO @.TableB VALUES ( 4 )
INSERT INTO @.TableB VALUES ( 5 )
INSERT INTO @.TableB VALUES ( 6 )
INSERT INTO @.TableB VALUES ( 7 )

SELECT
'TableA' = a.PKColumn,
'TableB' = b.PkColumn
FROM @.TableA a
FULL OUTER JOIN @.TableB b
ON a.PKColumn = b.PKColumn
WHERE ( a.PKColumn IS NULL
OR b.PKColumn IS NULL
)
ORDER BY ( isnull( a.PKColumn, 0 ) + isnull( b.PKColumn, 0 ))

TableA TableB
-- --
1 NULL
2 NULL
NULL 6
NULL 7

IF this is a regular process, you might wish to check which of the possiblities is most efficient. (I'm betting on the FOJ.)