TSQL -> Find missing number using Left Outer Join

Suppose that we have table that stores sequence of numbers. For examples year's sequence. Some years are missing. We have to find which of them.


/* first create table and put in some data */

IF OBJECT_ID('dbo.yseq') IS NOT NULL
DROP TABLE dbo.yseq;
GO

CREATE TABLE dbo.yseq
(
yrnum INT NOT NULL PRIMARY KEY,
label VARCHAR(10) NOT NULL
);
GO
INSERT INTO dbo.yseq(yrnum, label) VALUES(1995, 'Rep-1995');
INSERT INTO dbo.yseq(yrnum, label) VALUES(1996, 'Rep-1996');
INSERT INTO dbo.yseq(yrnum, label) VALUES(1997, 'Rep-1997');
INSERT INTO dbo.yseq(yrnum, label) VALUES(1999, 'Rep-1999');
INSERT INTO dbo.yseq(yrnum, label) VALUES(2000, 'Rep-2000');
INSERT INTO dbo.yseq(yrnum, label) VALUES(2002, 'Rep-2002');
INSERT INTO dbo.yseq(yrnum, label) VALUES(2003, 'Rep-2003');
GO

/* Left Outer Join helps to find Missing Years in the sequence. The join is made between two instances of years sequence table (self join) */

SELECT b.yrnum AS real_year, (a.yrnum + 1) AS virtual_year, b.label FROM dbo.yseq AS a LEFT JOIN dbo.yseq AS b
ON b.yrnum = a.yrnum + 1
WHERE (a.yrnum + 1) <= 2003;

/* This technique is efficient when only single numbers are missing from the sequence. If we'll have to find missing ranges of numbers (gaps), then the Left Join will have to be made against additional table representing true sequence without gaps. */



Query results will came as following:
real_yearvirtual_yearlabel
19961996Rep-1996
19971997Rep-1997
NULL1998NULL
20002000Rep-2000
NULL2001NULL
20032003Rep-2003


sqlexamples.info