Блог

У меня есть список значений. Некоторые из которых не существуют в БД. Я хотел бы найти, какие значения в списке не существуют.

Например. Учитывая таблицу:

 id name
--------
1 John
2 Amy
3 Kevin
4 Matt
5 Mark
6 Jim
7 Angela
8 Erin
  

Со списком, содержащим:

 John, Amy, Sarah, Sam
  

Я хотел бы вернуть только:

 Sarah, Sam
  

Как я могу добиться этого с помощью Oracle DB? Я знаю, что мог бы создать новую таблицу, вставить значения из моего списка, выполнить объединение, а затем удалить таблицу. Есть ли более простой способ?

Создайте таблицу «на лету»:

 select name from
(

  select 'John'  as name from dual union all
  select 'Amy'   as name from dual union all
  select 'Sarah' as name from dual union all
  select 'Sam'   as name from dual
) names
where name not in (select name from mytable);
  

Вы бы использовали left join или not exists :

 select name
from (select 'John' as name from dual union all
      select 'Amy' as name from dual union all
      select 'Sarah' as name from dual union all
      select 'Sam' as name from dual 
     ) n left join
     t
     using (name)
where t.name is null;
  

Вы можете использовать конструктор коллекции, например:

 select column_value from soda_key_list_t('John', 'Amy', 'Sarah', 'Sam');
  

Вы можете создать свой собственный тип коллекции «таблица строк», если у вас его еще нет, например

 create or replace type varchar2_tt as table of varchar2(4000);
  

или посмотрите, какие существующие типы доступны:

 select owner, type_name, length
from   all_coll_types t
where  t.coll_type = 'TABLE'
and    t.elem_type_name = 'VARCHAR2'
order by 1,2;
  

Я использовал soda_key_list_t который включен в Oracle 18c и далее как часть простой функции доступа к документам Oracle. Другие включают dbms_debug_vc2coll и ora_mining_varchar2_nt .

Вы также можете использовать подход без использования таблиц :

       select Number as list from (    
        select * from (
           select 'jn','jimmy','ronny' from dual ) 
        unpivot
       (  
        "Values" FOR "Number" IN ("'JN'","'JIMMY'","'RONNY'")   
       )
       )
     minus
    
     select list from your_table;