Selecting elements that don't exist

Question

Hi!

I am working on an application that has to assign numeric codes to elements. This codes are not consecutives and my idea is not to insert them in the data base until have the related element, but i would like to find, in a sql matter, the not assigned codes and i dont know how to do it.

Any ideas? Thanks!!!

Edit 1

The table can be so simple:

code | element 
-----------------
3    | three 
7    | seven 
2    | two

And I would like something like this: 1, 4, 5, 6. Without any other table.

Edit 2

Thanks for the feedback, your answers have been very helpful.

Answer 1

This will return NULL if a code is not assigned:

SELECT  assigned_codes.code
FROM    codes 
LEFT JOIN
        assigned_codes
ON      assigned_codes.code = codes.code
WHERE   codes.code = @code

This will return all non-assigned codes:

SELECT  codes.code
FROM    codes 
LEFT JOIN
        assigned_codes
ON      assigned_codes.code = codes.code
WHERE   assigned_codes.code IS NULL

There is no pure SQL way to do exactly the thing you want.

In Oracle, you can do the following:

SELECT  lvl
FROM    (
        SELECT  level AS lvl
        FROM    dual
        CONNECT BY
                level <=
                (
                SELECT  MAX(code)
                FROM    elements
                )
        )
LEFT OUTER JOIN
        elements
ON      code = lvl
WHERE   code IS NULL

In PostgreSQL, you can do the following:

SELECT  lvl
FROM    generate_series(
        1,
        (
        SELECT  MAX(code)
        FROM    elements
        )) lvl
LEFT OUTER JOIN
        elements
ON      code = lvl
WHERE   code IS NULL

Answer 2

This the same idea which Quassnoi has published. I just linked all ideas together in T-SQL like code.

DECLARE
    series @table(n int)

DECLARE
    max_n int,
    i int

SET i = 1
-- max value in elements table
SELECT
    max_n = (SELECT MAX(code) FROM elements)

-- fill @series table with numbers from 1 to n
WHILE i < max_n BEGIN
    INSERT INTO @series (n) VALUES (i)

    SET i = i + 1
END

-- unassigned codes -- these without pair in elements table
SELECT
    n
FROM
    @series AS series
    LEFT JOIN
     elements
    ON
     elements.code = series.n
WHERE
    elements.code IS NULL

EDIT: This is, of course, not ideal solution. If you have a lot of elements or check for non-existing code often this could cause performance issues.

Answer 3

Contrary to the assertion that this cannot be done using pure SQL, here is a counter example showing how it can be done. (Note that I didn't say it was easy - it is, however, possible.) Assume the table's name is value_list with columns code and value as shown in the edits (why does everyone forget to include the table name in the question?):

SELECT b.bottom, t.top
    FROM (SELECT l1.code - 1 AS top
            FROM value_list l1
            WHERE NOT EXISTS (SELECT * FROM value_list l2
                                 WHERE l2.code = l1.code - 1)) AS t,    
         (SELECT l1.code + 1 AS bottom
            FROM value_list l1
            WHERE NOT EXISTS (SELECT * FROM value_list l2
                                 WHERE l2.code = l1.code + 1)) AS b
    WHERE b.bottom <= t.top
      AND NOT EXISTS (SELECT * FROM value_list l2
                         WHERE l2.code >= b.bottom AND l2.code <= t.top);

The two parallel queries in the from clause generate values that are respectively at the top and bottom of a gap in the range of values in the table. The cross-product of these two lists is then restricted so that the bottom is not greater than the top, and such that there is no value in the original list in between the bottom and top.

On the sample data, this produces the range 4-6. When I added an extra row (9, 'nine'), it also generated the range 8-8. Clearly, you also have two other possible ranges for a suitable definition of 'infinity':

• -infinity .. MIN(code)-1
• MAX(code)+1 .. +infinity

Note that:

1. If you are using this routinely, there will generally not be many gaps in your lists.
2. Gaps can only appear when you delete rows from the table (or you ignore the ranges returned by this query or its relatives when inserting data).
3. It is usually a bad idea to reuse identifiers, so in fact this effort is probably misguided.

However, if you want to do it, here is one way to do so.