SQL How do I find values from a list that are not in a table

Question

I have a table with values in a field called 'code'.

ABC
DFG
CDF

How would I select all codes that are not in the table from a list I have? Eg:

SELECT * from [my list] where table1.code not in [my list]

the list is not in a table.

The list would be something like "ABC","BBB","TTT" (As strings)

Answer 1

Try this:

SELECT code 
FROM Table1
WHERE code NOT IN ('ABC','CCC','DEF') --values from your list

It will result:

DFG
CDF

If the list is in another table, try this:

SELECT code 
FROM Table1
WHERE code NOT IN (SELECT code FROM Table2)

As per your requirement, try this:

SELECT list
FROM Table2
WHERE list NOT IN (SELECT code from table1)

It will select the list values that are not in code. See an example in SQL Fiddle

Answer 2

Can you use common table expressions?

WITH temp(code) AS (VALUES('ABC'),('BBB'),('TTT'),(ETC...))
SELECT temp.code FROM temp WHERE temp.code NOT IN 
(SELECT DISTINCT table1.code FROM table1); 

This would allow you to create a temporary table defined with your list of strings within the VALUES statement. Then use standard SQL to select values NOT IN your table1.code column.

Answer 3

The question key point need to set "ABC","BBB","TTT" source data trun to a table.

that table will look like

|---+
|val|
|---+
|ABC|
|BBB|
|TTT|

Sqlite didn't support sqlite function. so that will be a little hard to sqlite your list to be a table.

You can use a CTE Recursive to make like sqlite function

1. You need to use replace function to remove " double quotes from your source data.
2. There are two column in the CTE
   • val column carry your List data
   • rest column to remember current splite string

You will get a table from CTE like this.

|---+
|val|
|---+
|ABC|
|BBB|
|TTT|

Then you can compare the data with table1.

---

Not IN

WITH RECURSIVE split(val, rest) AS (
    SELECT '',  replace('"ABC","BBB","TTT"','"','') || ',' 
    UNION ALL
    SELECT  
         substr(rest, 0, instr(rest, ',')),
         substr(rest, instr(rest, ',')+1)
    FROM split
    WHERE rest <> '')
SELECT * from (
  SELECT val
  FROM split 
  WHERE val <> ''
) t where t.val not IN (
  select t1.code 
  from table1 t1
)

sqlfiddle:https://sqliteonline.com/#fiddle-5adeba5dfcc2fks5jgd7ernq

Outut Result:

+---+
|val|
+---+
|BBB|
|TTT|

---

If you want to show it in a line,use GROUP_CONCAT function.

WITH RECURSIVE split(val, rest) AS (
    SELECT '',  replace('"ABC","BBB","TTT"','"','') || ',' 
    UNION ALL
    SELECT  
         substr(rest, 0, instr(rest, ',')),
         substr(rest, instr(rest, ',')+1)
    FROM split
    WHERE rest <> '')
SELECT GROUP_CONCAT(val,',') val from (
  SELECT val
  FROM split 
  WHERE val <> ''
) t where t.val not IN (
  select t1.code 
  from table1 t1
)

Outut Result:

BBB, TTT

sqlfiddle:https://sqliteonline.com/#fiddle-5adecb92fcc36ks5jgda15yq

---

Note:That is unreasonable on SELECT * from [my list] where table1.code not in [my list],because This query has no place to find table1 so you couldn't get table1.code column

You can use not exists or JOIN to make your expect.

sqlfiddle:https://sqliteonline.com/#fiddle-5adeba5dfcc2fks5jgd7ernq

Answer 4

Is this solution good, or am I missing something?

create table table10 (code varchar(20));

insert into table10 (code) values ('ABC');
insert into table10 (code) values ('DFG');
insert into table10 (code) values ('CDF');

select * from (
  select 'ABC' as x
  union all select 'BBB'
  union all select 'TTT'
) t where t.x not in (select code from table10);
-- returns: BBB
--          TTT

See SQL Fiddle.

Answer 5

This can also be achieved using a stored procedure:

DELIMITER //
drop function if exists testcsv
//
create function testcsv(csv varchar(255)) returns varchar(255)
deterministic
begin
    declare pos, found int default 0;
    declare this, notin varchar(255);
    declare continue handler for not found set found = 0;

    set notin = '';
    repeat
        set pos = instr(csv, ',');
        if (pos = 0) then
            set this = trim('"' from csv);
            set csv = '';
        else
            set this = trim('"' from trim(substring(csv, 1, pos-1)));
            set csv = substring(csv, pos+1);
        end if;
        select 1 into found from table1 where code = this;
        if (not found) then
            if (notin = '') then
                set notin = this;
            else
                set notin = concat(notin, ',', this);
            end if;
        end if;
        until csv = ''
    end repeat;
    return (notin);
end
//
select testcsv('"ABC","BBB","TTT","DFG"')

Output:

BBB, TTT