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_year virtual_year label 1996 1996 Rep-1996 1997 1997 Rep-1997 NULL 1998 NULL 2000 2000 Rep-2000 NULL 2001 NULL 2003 2003 Rep-2003

sqlexamples.info