¿Huecos en una tabla?

[glopez]

¿Cómo puedo obtener los huecos que existen en una tabla enumerada sin tener que recorrerla? ¿Alguna función?

Ej:
1 Articulo 1
2 Articulo 2
4 Articulo 4
5 Articulo 5

Me debería devolver 3.

[Casimiro Notevi]

Bueno, eso no es un "hueco en una tabla"
Para hacer lo que quieres tendrás que tener unas reglas fijas en ese campo, si TODOS los artículos se llaman "Articulo " 1, 2, 3, etc. entonces podrás hacer una búsqueda ordenada.
Si son códigos numéricos será más fácil, obviamente.
Si tienes "Articulo 1", "Otro artículo", "artículo x"... me temo que es algo imposible.

[mamcx]

Dependiendo del motor puede hacerse. En PostgreSQL tienen una función que genera datos al vuelo:

Código SQL [-]
SELECT generate_series AS id FROM generate_series(1, 5)
NOT IN (
   SELECT id FROM tu_tabla 
)

[Casimiro Notevi]

Empezado por mamcx

Dependiendo del motor puede hacerse. En PostgreSQL tienen una función que genera datos al vuelo:

Creo que no es eso lo que pregunta, ya que la generación automática "aleatoria" está disponible en casi todas las herramientas de manejo de bases de datos.

[mamcx]

Empezado por Casimiro Notevi

Creo que no es eso lo que pregunta, ya que la generación automática "aleatoria" está disponible en casi todas las herramientas de manejo de bases de datos.

Eso no es la función que mostré.

Esta genera datos basados en un rango (no aleatorio) y devuelve una tabla como si se hubiera llenado manualmente.

https://www.citusdata.com/blog/2018/...-generate-sql/

[Casimiro Notevi]

Pues eso, que no es lo que está buscando

[[duilioisola]]

Suponiendo que la estructura de latabla es:

ARTICULOS(
    ID          INTEGER, 
    DESCRIPCION VARCHAR(100)
)

Este SQL te devuelve el primer hueco en el ID de la tabla:

Código SQL [-]
/*
Busca el primer ID cuyo "ID+1" que no exista en la tabla.
Devuelve el "ID+1"
*/
SELECT FIRST 1 ID + 1 AS HUECO
FROM ARTICULOS A
WHERE
NOT EXISTS(SELECT ID
           FROM ARTICULOS
           WHERE
           ID = C.ID + 1)

[Neftali [Germán.Estévez]]

Yo creo que podrías sacerlo con algo así; Creo que todo lo que he utilizado existe en todos los motores de SQL (es estandard de SQL).
Tal vez cambie cómo se definen las variables, pero la sintaxis debe ser similar.
Suponiendo que la tabla se llame TABLA1 y tenga estos valores:

ID NOMBRE
--------------
0 Articulo 0
2 Articulo 2
4 Articulo 4
5 Articulo 5
7 Articulo 7

Una consulta como esta, debería devolver los valores que no existen en esa tabla en el campo ID.

Para ello se crea una temporal con todos los posibles, entre el maximo y mínimo existente en TABLA1, y luego se miran los que faltan.

Código SQL [-]
-- Definimos la tabla con TODOS los valores posibles
DECLARE @tablatmp table (NUMEROS INT);
-- Variables de máximo y mínimo de la TABLA1
declare @max int;
declare @min int;
--Valor máximo de la tabla
set @max = (SELECT MAX(ID) NUMMAX From TABLA1)
--Valor mínimo de la tabla
set @min = (SELECT MIN(ID) NUMMIN From TABLA1)
-- llenar la tabla con los valores desde el mínima al máximo 
declare @i int
set @i=@min
while (@i <= @max) begin
  set @i = @i + 1
  insert into @tablatmp (NUMEROS) VALUES (@i)
end
-- Devolver los valores uqe faltan en el campo ID de la TABLA1
SELECT NUMEROS from @tablatmp WHERE NUMEROS NOT IN (SELECT ID FROM Tabla1)

En la imagen, lo primero es TABLA1 y lo segundo la consulta anterior ejecutada:

[glopez]

Hola, ante todo muchas gracias a todos por vuestras respuestas.
Quizás no me explique bien.
Lo que necesito es ver que saltos hay en la numeración en un campo de una tabla (número factura). Lo necesito para ver si existen saltos en la numeración que le asigna un usuario a las facturas recibidas y/o emitidas en un programa contable.
He probado la solución de duilioisola porque la veía más simple pero la ejecución de la consulta es muy lenta unos 20s en unos 2000 registros y falla cuando hay dos saltos consecutivos (1,2,5,6,7...).

La solución de Neftali no la he probado pero la veo un poco complicada y creo que no obtendré más rapidez en los resultados, porque hay que ir insertando valores en una tabla. Tengo clientes con millones de registros.

¿Alguna otra solución?

[Casimiro Notevi]

Entonces, tienes una tabla con un campo numérico.

1
2
4
5
6
8
9

Y quieres sabe qué números faltan, ¿es eso?

[glopez]

Exactamente eso.

[[duilioisola]]

Este SQL te devuelve una lista de números entre 1 y 100.

Código SQL [-]
with recursive n as (
      select 1 /*nro. inicial*/ as n
      from rdb$database
      union all
      select n.n + 1
      from n
      where n < 100 /*nro. final*/
     )
select n.n
from n;

Con esto como base restrinjo el rango al min/max id de la tabla articulos.
De este rango devuelvo solo los que no existen en la tabla de artículos.
Se parece a la respuesta de Neftali, pero para Firebird.
El único problema es que debido a la recursividad solo permite rangos de 1024 registros.

Código SQL [-]
WITH RECURSIVE N
AS (SELECT (SELECT MIN(ID)
            FROM ARTICULOS) AS N
    FROM RDB$DATABASE
    UNION ALL
    SELECT N.N + 1
    FROM N
    WHERE
    N < (SELECT MAX(ID)
         FROM ARTICULOS))
SELECT N.N
FROM N
WHERE
NOT EXISTS(SELECT ID
           FROM ARTICULOS
           WHERE
           ID = N.N)

Con respecto a la velocidad, lo he probado con una tabla con 800 registros indexados por el ID y no ha tardado nada.

[[duilioisola]]

Si puedes crear un procedimiento que te devuelva la lista de números ya no hay problemas.
Lo he probado en una base con 23.000 registros y ha tardado 31ms
La tabla tiene un índice único por el campo ID.

Código SQL [-]
/* Procedimiento que devuelve lista de numeros */
create procedure dame_rango (
    ini integer,
    fin integer)
returns (
    n integer)
as
begin
    n = ini;
    while (n <= fin) do
    begin
        suspend;
        n = n + 1;
    end
end

Código SQL [-]
/* Recorre lista de numeros y devuelve los que no existe en la tabla ARTICULOS */
WITH RECURSIVE N
AS (SELECT N
    FROM DAME_RANGO((SELECT MIN(ID)
                     FROM ARTICULOS),
                    (SELECT MAX(ID)
                     FROM ARTICULOS)))
SELECT N.N
FROM N
WHERE
NOT EXISTS(SELECT ID
           FROM ARTICULOS
           WHERE
           ID = N.N)

[cloayza]

Propongo esta opción...Procedimiento almacenado en firebird

Código SQL [-]
reate or alter procedure check_correlativos
returns (id_falta integer)
as
declare variable id integer;
declare variable id_next integer;
declare variable oldNext integer;
BEGIN
     OldNext=-1;
     FOR
       select c.id, c.id+1
       from articulos c
       INTO :ID, :ID_NEXT
     DO
     BEGIN
          id_falta=null;
          if (OldNext=-1) then
             OldNext=ID;
   
          if (OldNext<>id) then
             id_falta=OldNext;
   
          OldNext=ID_NEXT;
   
          if (not id_falta is null) then
             suspend;
     END
END

[glopez]

Muchas gracias a todos.
Al final me he decantado por la solución de duilioisola, iba un poco lento porque los campos por los que tenía que buscar no tenían un índice. Lo he creado y va perfecto.

[elrayo76]

Buenas noches,

Hace un tiempo tuve que hacer lo mismo porque un cliente cada vez que eliminaba un registro quería que el código que quedaba libre se utilizara nuevamente.

Código SQL [-]
SELECT a.Sector + 1
FROM UbicacionSector a
LEFT JOIN UbicacionSector b ON (a.Sector = b.Sector - 1)
WHERE b.Sector IS NULL

Aclaro que esto en su momento fue hecho en Oracle, pero como el código es SQL puro se pude implementar en cualquier motor de base de datos

Saludos,
El Rayo