SQL Select эксклюзивные идентификаторы, сравнивающие основной список с двумя подсписками

Question

Мне нужна помощь с запросом SQL. У меня есть "Мастер список таблиц (X и Y категории)". Я должен сравнить приведенный выше список с двумя таблицами подсписка - "Список X" и "Список Y"

Таблицы:

*MasterList*              
ID Cat Status           
101 X  Ready            
102 X  Ready            
103 Y  Dispatched Y     
104 X  Dispatched Y     
105 Y  Dispatched       
106 X  Ready
107 X  Dispatched Y
108 Y  Ready Y
109 X  Dispatched
110 Y  Dispatched
111 X  Ready Y
112 X  Dispatched
113 X  Dispatched Y

*ListX* 
ID    
101   
102   
106
109
112

*ListY*
ID
105
110

Я пытаюсь создать запрос, который выводит:

1. Идентификаторы Cat-X, которых нет в List-X
2. Идентификаторы Cat-Y, которых нет в List-Y

Выход

ID  Cat Status
103  Y  Dispatched
104  X  Dispatched
107  X  Dispatched
108  Y  Ready
111  X  Ready
113  X  Dispatched

Спасибо,

Рави.

Answer 1

Вы можете сделать это несколькими способами, например, как здесь:

select * 
  from master_list 
  where (cat = 'X' and id not in (select id from listx))
     or (cat = 'Y' and id not in (select id from listy))

Также попробуйте not exists и простые соединения.

---

Тестовые данные в Oracle и вывод:

create table master_list (id int, cat varchar2(1), status varchar2(15));
insert into master_list values (101, 'X', 'Ready');
insert into master_list values (102, 'X', 'Ready');
insert into master_list values (103, 'Y', 'Dispatched');
insert into master_list values (104, 'X', 'Dispatched');
insert into master_list values (105, 'Y', 'Dispatched');
insert into master_list values (106, 'X', 'Ready');
insert into master_list values (107, 'X', 'Dispatched');
insert into master_list values (108, 'Y', 'Ready');
insert into master_list values (109, 'X', 'Dispatched');
insert into master_list values (110, 'Y', 'Dispatched');
insert into master_list values (111, 'X', 'Ready');
insert into master_list values (112, 'X', 'Dispatched');
insert into master_list values (113, 'X', 'Dispatched');

create table listx as (select column_value id 
                         from table(sys.odcinumberlist(101, 102, 106, 109, 112)));
create table listy as (select column_value id 
                         from table(sys.odcinumberlist(105, 110)));

  ID CAT STATUS
---- --- ---------------
103 Y   Dispatched
104 X   Dispatched
107 X   Dispatched
108 Y   Ready
111 X   Ready
113 X   Dispatched

Answer 2

Вы можете использовать ИЛИ в предложении WHERE:

SELECT ID, CAT, STATUS 
  FROM MASTERLIST M
 WHERE (M.ID NOT IN (SELECT ID FROM LISTX) AND CAT = 'X')
    OR (M.ID NOT IN (SELECT ID FROM LISTY) AND CAT = 'Y');

Другой альтернативой будет разделение правил на два запроса и их объединение с UNION ALL:

SELECT ID, CAT, STATUS 
  FROM MASTERLIST M
 WHERE M.ID NOT IN (SELECT ID FROM LISTX) AND CAT = 'X'
 UNION ALL
SELECT ID, CAT, STATUS 
  FROM MASTERLIST M
 WHERE M.ID NOT IN (SELECT ID FROM LISTY) AND CAT = 'Y';

Оба метода приведут к одинаковым результатам. На самом деле, некоторые оптимизаторы (например, Oracle) будут пытаться преобразовать первое решение во второе в режиме реального времени. Это называется OR-расширением.