Create a numbers table first, if you don't already have a table that
contains every number:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp

SELECT N.number
FROM Numbers AS N
LEFT JOIN Table1 AS T1
ON N.number = T1.serialno
LEFT JOIN Table2 AS T2
ON N.number = T2.serialno
WHERE T1.serialno IS NULL
AND T2.serialno IS NULL
AND N.number BETWEEN 1 AND 50000

--
David Portas
SQL Server MVP
--

I have 2 tables say table1 and table2 with the same structure. Each record
is identified by a field 'SerialNo'. Now there should be a total of 500000
records in both tables with serialno from 1 to 500000. Either a record is in
table1 or table2. I want to find records (or SerialNo's) that are in
neither table (if deleted by accident etc). What would be the sql query?
I'm using SQL 6.5

thx

Mansoor,

Normalisation and DRI would suggest that you NOT have 2 tables.
e.g. How do you enforce a "no duplication" constraint easily ?

That said UNION and OUTER JOIN are useful in this case. eg:

create table One
(
SerialNo int
)
create table Two
(
SerialNo int
)


insert One values ( 1 )
insert One values ( 3 )
insert One values ( 4 )
insert One values ( 8 )
insert One values ( 16 )
insert Two values ( 2 )
insert Two values ( 7 )
insert Two values ( 14 )
insert Two values ( 15 )
insert Two values ( 18 )

Select a.SerialNo
from
( Select SerialNo
from One
Union
Select SerialNo
from Two
) a
left outer join
( Select SerialNo
from One
Union
Select SerialNo
from Two
) b
on b.SerialNo = a.SerialNo + 1
where b.SerialNo is NULL

Will return only 1 row, unless you have gaps

Regards
AJ


[quoted text, click to view]