Comparing data in two different tables

I am trying to find an easy set based alghoritm for comparing data in two
different tables.
Given tables like this
create table dbo.IwExclusions (
Id bigint not null,
Number bigint not null,
constraint IwExclusionsPKCO primary key clustered (Id, Number))
create table dbo.IwLimitations (
Id bigint not null,
Number bigint not null,
constraint IwLimitationsPKCO primary key clustered (Id, Number))
insert into IwExclusions (1, 1960)
insert into IwExclusions (1, 1962)
insert into IwLimitations (1, 1960)
insert into IwLimitations (1, 1963)
How do I best write a query returning TRUE
if data in the two tables are equal for Id = 1?
Grateful for tips.
/kThere is no boolean return type in SQL Server. You can use EXISTS.
Here's one way:
IF EXISTS (
SELECT E.id, E.number
FROM IwExclusions AS E
FULL JOIN IwLimitations AS L
ON E.id = L.id
AND E.number = L.number
WHERE E.id IS NULL
OR L.id IS NULL)
.. /* do something */
David Portas
SQL Server MVP
--|||Came up with these 2 soulutions:
truncate table Exclusions
truncate table Limitations
insert into Exclusions values (1, 1960)
insert into Exclusions values (1, 1962)
insert into Limitations values (1, 1960)
insert into Limitations values (1, 1963)
go
select * from Exclusions
select * from Limitations
go
--solution 1
IF EXISTS (
SELECT 9
FROM Exclusions AS E
FULL JOIN Limitations AS L
ON E.warrantid = L.warrantid
AND E.number = L.number
WHERE E.warrantid IS NULL
OR L.warrantid IS NULL)
select 'different'
else select 'identical'
--solution 2
declare @.E bigint, @.L int
select @.E = checksum_agg(binary_checksum(*)) from Exclusions where warrantid
= 1
select @.L = checksum_agg(binary_checksum(*)) from Limitations where
warrantid = 1
if @.E <> @.L select 'different' else select 'identical'
opinions?
/k