Check codes that are not in the database with a query

Asked

Viewed 28 times

0

I know I can check codes that are in one table and are not in another with the following query:

SELECT cod FROM tabela1 WHERE cod NOT IN (SELECT cod FROM tabela2)

But how can I check the records that are not in a table but are in a comma-separated list?

Example: Let’s say I have a table names with the following data:

cod | nome
----|-----------------
1   | César
3   | Maurício

And I want to make a query by passing the following codes 1, 2, 3, 4 hoping the return will be 2, 4 which are the codes that are not in the database.

How can I do that?

sql query

1 answer

0

If the list is fixed, you can do this with unions:

select * from (
  select 1 item union
  select 2 union 
  select 3 union
  select 4
)
where item not in (select cod from tabela1)

But if as the list extends, the query will get slower and slower.

Fiddle: https://www.db-fiddle.com/f/5PcXEWxYa9S4D3NBb5cyxK/0

  • Juliano, thank you for answering the question, but I did not put the SGDB because I am looking for something more universal that is not attached to the SGDB nor to a Procedure. Looks like this one query works only for Sqlite.

  • It would also be interesting to be able to query by passing the comma-separated list.

  • I believe that this solution will always be idiomatic from bank to bank. Unless you create a table just to generate this result.

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.