Know which values within the IN condition are not linked to the table

Asked

Viewed 72 times

4

Hello,

I have a very simple table as an example:

inserir a descrição da imagem aqui

I also have a query that checks the existing values within the IN, example:

SELECT * FROM tLetras l WHERE l.letras IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

This query will certainly return me all the values that are in the table tLetras.

What I really need is to know which letters (values) who are inside the IN and that are not within the TABLE. I mean, the values I need to know are 'E','H','I','L','O','P','Q','R','S','T','U','V','W','X','Y','Z', which are precisely the values that are not in the TABLE but who are in the IN.

sql oracle

  • Try using the MINUS operator, it’s EXCEPT on Oracle.

2 answers

3


Since your list of letters is not in a table, you need to put it in a "pseudo-table", or "dummy table". The oracle has an interesting resource to do that which is to declare a table in the body of the select, thus:

select *  
  from tLetras  l
  right join table(sys.odcivarchar2list('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')) lista on l.letras=lista.column_value
  where l.letras is null

Making the right join brings the elements that do not exist on the left (table tLetras ) but exist in the list on the right. Finally, the l.letra is null brings the letters that do not exist, because will return null where do not match the Join

See here an example working: sqlfiddle

Here the documentation of odcivarchar2list

2

Cannot select something that does not exist in the table.

What can be done is to have a table with all the letters and use

SELECT letra FROM LetrasTodas 
WHERE letra NOT IN (SELECT letras FROM tLetras);

Example with sys.odcivarchar2list, as suggested in reply Ricardo Pontual, but using NOT IN instead of JOIN, which in this case seems more appropriate.

SELECT lista.column_value
FROM table(sys.odcivarchar2list('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')) lista
WHERE lista.column_value NOT IN (SELECT letras FROM tLetras)

sqlfiddle.

Browser other questions tagged sql oracle

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