Підзапити з предикатом IN (Not In)

Запит – знайти усіх викладачів, які мають усі ті посади, що є на кафедрі №2 .

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost) In (select KodPost from VYKLADACH where NomKaf=2)));

А якщо треба, щоб в результатній таблиці не було викладачів кафедри №2, то

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.NomKaf)<>2) AND (( VYKLADACH.KodPost) In (select KodPost from VYKLADACH where NomKaf=2)));

Запит – знайти усіх викладачів, які мають посади, яких немає на кафедрі №2 .

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost) Not In (select KodPost from VYKLADACH where NomKaf=2)));

7.5.5 Підзапити, що починаються з операторів порівняння та ключових слів ANY або ALL

7.5.5.1 Підзапити з ключовим словом ANY

Запит : знайти посади викладачів, що більше (за кодом, а не за семантичним значенням) будь якої посади викладачів кафедри №12 (на кафедрі дві посади з кодами „01” і „03”).

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost)>Any (select KodPost from VYKLADACH where NomKaf=12)));

Як бачимо, в результат попали усі рядки, де KodPost >”01”. Змінимо оператор порівняння на “<”.

Як бачимо, в результат попали усі рядки, де KodPost <”04”. Змінимо оператор порівняння на “=”.

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost)=Any (select KodPost from VYKLADACH where NomKaf=12)));

Результат цього запиту повністю співпав би із результатом запиту з предикатом In. Покажемо це на запиті для кафедри №2 (див вище запит для цієї кафедри з предикатом In).

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost) =Any (select KodPost from VYKLADACH where NomKaf=2)));

Приклади.

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost) <>Any (select KodPost from VYKLADACH where NomKaf=2)));

7.5.5.2 Підзапити з ключовим словом ALL

Запит : знайти посади викладачів, що більше (за кодом, а не за семантичним значенням) самої більшої (за кодом) посади викладачів кафедри №12 (на кафедрі дві посади з кодами „01” і „03”).

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost)>All (select KodPost from VYKLADACH where NomKaf=12)));

Як що оператор порівняння змінимо на “<All”, то отримаємо пусту таблицю, оскільки посади з кодом, меншим за „01” в таблиці VYKLADACH не існує. Якщо ми зробимо аналогічний запит для кафедри №2 (там є посади з кодами “02” і “04”), то отримаємо список викладачів код посади у яких має значення “01”.

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost) <All (select KodPost from VYKLADACH where NomKaf=2)));

При вживанні операторів порівняння “=All” і “<>All” отримаємо пусті таблиці, оскільки для таблиць ці оператори не визначені. Крім того, якщо внутрішній підзапит, що починається з ALL і оператора порівняння, повертає в якості одного зі своїх значень NULL, вважається, що запит у цілому завершився невдало.

Приклад. Для демонстрації вищенаведеного змінимо значення поля KodPost для викладача з номером 22 на Null (він працює на кафедрі №12, яка буде фігурувати у внутрішньому запиті), а також занесемо одне значення Null для викладача с номером 19 (він працює на кафедрі №2, яка буде оброблятися зовнішнім запитом).

UPDATE VYKLADACH SET VYKLADACH.KodPost = Null

WHERE ((( VYKLADACH.KodVykl)=22) or (( VYKLADACH.KodVykl)=19));

Тепер на кафедрі №12, на якій працює два викладача, код посади залишився тільки у одного і він дорівнює „03”, у другого – не визначений, а на кафедрі №2 із трьох викладачів у двох коди посад “02” “04”, у третього - – не визначений.

Повторимо запит з використанням предикатів “>All” та “<All” (див. вище) для оновленої таблиці VYKLADACH.

SELECT VYKLADACH.PrizvVykl, VYKLADACH.NomKaf, VYKLADACH.KodPost

FROM VYKLADACH

WHERE ((( VYKLADACH.KodPost) >All (select KodPost from VYKLADACH where NomKaf=12)));

Результат – пуста таблиця, хоча в таблиці VYKLADACH є посади з кодом „04”, який більше ніж „03”, але старшинство між „Null” і „04”, як і будь-яким іншим значенням коду, не визначено системою. Такий же результат отримаємо, коли замінимо предикат на “<All”, хоча в таблиці VYKLADACH є посади з кодом „01” і „02”, який менше ніж „03”, але не визначено їх порівняння з „Null”.

Повернемося до попереднього стану таблиці VYKLADACH.

UPDATE VYKLADACH SET VYKLADACH.KodPost = “01”

WHERE (( VYKLADACH.KodVykl)=22);

UPDATE VYKLADACH SET VYKLADACH.KodPost = “04”

WHERE (( VYKLADACH.KodVykl)=19));