Select elements that are absent in a table from a list of value

Question

I have a table T with id and labels

ID  LABEL
1   label_1
2   label_2
3   label_3

I have a list of labels:

('label_1', 'label_4', 'label_6' )

I would like to select those that do not exist in table T. In my case I would get 'label_4' and 'label_6'. How can I do that in SQL?

EDIT 1

Those labels are external values. I would like to print values that do not exist in my table in a log file, probably using a spool

EDIT 2

I am currently trying to put all my values in a table using pl/SQL since it is basically a huge list of values. I have this:

DECLARE
  type array_t is varray(603) of varchar2(50 BYTE);
  labels array_t := array_t(
        '210',
                'label_1',
                'label_6',
                'label_4'
  );
BEGIN
  For i in labels LOOP

    Insert Into TEMP_LABEL (LABEL)
    Values (i);

  END LOOP;
  commit;
END;

but I get the following error: pls-00456 item is not a cursor I am stuck here.

Do `label_4` and `label_6` exist in a lookup table somewhere? Or are they external values which may not be in the database at all? What exactly did you want your results to look like? — Bacon Bits, Apr 23 '15 at 16:03
Those labels are external values. I would like to print values that do not exist in my table in a log file, probably using a `spool` — kaligne, Apr 23 '15 at 16:05
The answer depends on your database engine, which you didn't specify. — Dan Bracuk, Apr 23 '15 at 16:10
Are you able to create a temp table or table variable with these external values? What database system are you using? After creating a temp table, you should be able to do a ```LEFT JOIN``` then filter on ```NULL``` values to get what you need. — marksiemers, Apr 23 '15 at 16:10
Does this mean that what I am asking to do is impossible without storing my external values in a (temporary) table? — kaligne, Apr 23 '15 at 16:25
Not impossible, but if you are wanting to use SQL to do this (rather than application code or a programmatic approach) then using a table makes sense, as other commenters have mentioned, if you let us know what database engine you are using, there may be a way to accomplish this without a table - using something like PL/pgSQL (for Postgres) — marksiemers, Apr 23 '15 at 16:34
I am now trying ot put all of my vlues into a table using PL/SQL. Please have a look at my second edit. — kaligne, Apr 23 '15 at 16:47
For using a CURSOR, you want to declare that, see the manual here: http://www.postgresql.org/docs/current/interactive/plpgsql-cursors.html For using a temp table, here is a good start http://stackoverflow.com/questions/17873735/select-from-a-table-variable — marksiemers, Apr 23 '15 at 17:27

marksiemers · Accepted Answer · 2015-11-29T00:05:55.003

See my sqlfiddle (and postgres version) for a solution using a table

The pertinent query is the following:

   SELECT T_TEMP.LABEL, T.LABEL
     FROM T_TEMP
LEFT JOIN T
       ON T_TEMP.LABEL = T.LABEL
    WHERE T.LABEL IS NULL;

If there is a strong reason for not using a table to store these values, please let us know.

Depending on how huge the data set is, choosing between the following queries can have an impact on performance (see this excellent explanation comparing the methods):

-- LEFT JOIN / IS NULL
SELECT    T_TEMP.LABEL, T.LABEL
FROM      T_TEMP
LEFT JOIN T ON T_TEMP.LABEL = T.LABEL
WHERE     T.LABEL IS NULL;

-- NOT EXISTS
SELECT  T_TEMP.LABEL
FROM    T_TEMP
WHERE   NOT EXISTS
    (
    SELECT  T.LABEL
    FROM    T
    WHERE   T_TEMP.LABEL = T.LABEL
    );

-- NOT IN    
SELECT  T_TEMP.LABEL
FROM    T_TEMP
WHERE   LABEL NOT IN
    (
    SELECT  T.LABEL
    FROM    T
    );

As the dataset grows, the LEFT JOIN / IS NULL or NOT EXISTS should perform better, but according the the execution plan in this fiddle, the NOT IN seems to be performing with the lowest cost.

Thank you the `LEFT JOIN / IS NULL` method seems to work. Strangely enough the `NOT IN` method does not output any result, even though it should return the same result than the others. I will try to find out where my (very likely) error comes from. — kaligne, Apr 24 '15 at 08:14
If you have one of them working, I would stick with that, but if you do want ```NOT IN``` to work, double check that you have ```WHERE LABEL NOT IN``` as opposed to just ```WHERE NOT IN``` - it differs from ```WHERE NOT EXISTS``` — marksiemers, Apr 24 '15 at 14:13

openwonk · Answer 2 · 2015-04-23T19:45:25.413

Here is my answer before more information was provided in "EDIT 2". I think more context-specific information is needed. Anyways, this code returns rows of labels not in original table:

CREATE TABLE OriginalTable (Id INT, Label VARCHAR(20));

INSERT INTO OriginalTable (Id, Label)
VALUES (1, 'label_1'), (2, 'label_2'), (3, 'label_3');

-- Create a table with the labels you want to compare
CREATE TABLE ComparisonTable (Id INT, Label VARCHAR(20));

INSERT INTO ComparisonTable (Id, Label)
VALUES (1, 'label_1'), (2, 'label_4'), (3, 'label_6');

SELECT Label
FROM ComparisonTable
WHERE Label NOT IN (SELECT Label FROM OriginalTable);

Select elements that are absent in a table from a list of value

EDIT 1

2 Answers2