How do I find a "gap" in running counter with SQL?

Question

In MySQL and PostgreSQL:

SELECT  id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id
LIMIT 1

In SQL Server:

SELECT  TOP 1
        id + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )
ORDER BY
        id

In Oracle:

SELECT  *
FROM    (
        SELECT  id + 1 AS gap
        FROM    mytable mo
        WHERE   NOT EXISTS
                (
                SELECT  NULL
                FROM    mytable mi 
                WHERE   mi.id = mo.id + 1
                )
        ORDER BY
                id
        )
WHERE   rownum = 1

ANSI (works everywhere, least efficient):

SELECT  MIN(id) + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )

Systems supporting sliding window functions:

SELECT  -- TOP 1
        -- Uncomment above for SQL Server 2012+
        previd
FROM    (
        SELECT  id,
                LAG(id) OVER (ORDER BY id) previd
        FROM    mytable
        ) q
WHERE   previd <> id - 1
ORDER BY
        id
-- LIMIT 1
-- Uncomment above for PostgreSQL

Your answers all work fine if you have a first value id = 1, otherwise this gap will not be detected. For instance if your table id values are 3,4,5, your queries will return 6.

I did something like this

SELECT MIN(ID+1) FROM (
    SELECT 0 AS ID UNION ALL 
    SELECT  
        MIN(ID + 1)
    FROM    
        TableX) AS T1
WHERE
    ID+1 NOT IN (SELECT ID FROM TableX) 

There isn't really an extremely standard SQL way to do this, but with some form of limiting clause you can do

SELECT `table`.`num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
LIMIT 1

(MySQL, PostgreSQL)

or

SELECT TOP 1 `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL

(SQL Server)

or

SELECT `num` + 1
FROM `table`
LEFT JOIN `table` AS `alt`
ON `alt`.`num` = `table`.`num` + 1
WHERE `alt`.`num` IS NULL
AND ROWNUM = 1

(Oracle)

The first thing that came into my head. Not sure if it's a good idea to go this way at all, but should work. Suppose the table is t and the column is c:

SELECT 
    t1.c + 1 AS gap 
FROM t as t1 
LEFT OUTER JOIN t as t2 ON (t1.c + 1 = t2.c)
WHERE t2.c IS NULL 
ORDER BY gap ASC 
LIMIT 1

Edit: This one may be a tick faster (and shorter!):

SELECT 
    min(t1.c) + 1 AS gap 
FROM t as t1 
LEFT OUTER JOIN t as t2 ON (t1.c + 1 = t2.c) 
WHERE t2.c IS NULL

This works in SQL Server - can't test it in other systems but it seems standard...

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1))

You could also add a starting point to the where clause...

SELECT MIN(t1.ID)+1 FROM mytable t1 WHERE NOT EXISTS (SELECT ID FROM mytable WHERE ID = (t1.ID + 1)) AND ID > 2000

So if you had 2000, 2001, 2002, and 2005 where 2003 and 2004 didn't exist, it would return 2003.