1) Вкладені підзапити
SQL дозволяє вкладати запити один в одного. Зазвичай підзапит повертає одне значення, яке перевіряється на предмет істинності предиката.
Види умов пошуку:
• Порівняння з результатом вкладеного запиту (=, <>.> =)
• Перевірка на приналежність результатами підзапиту (IN)
• Перевірка на існування (EXISTS)
• Багаторазове (кількісне) порівняння (ANY, ALL)
Примітки по вкладеним запитам:
• підзапитів повинен вибирати тільки один стовпець (за винятком підзапиту з предикатом EXISTS), і тип даних його результату повинен відповідати типу даних значення, зазначеного в предикате.
• У ряді випадків можна використовувати ключове слово DISTINCT для гарантії отримання одного значення.
• У вкладеному запиті не можна включати розділ ORDER BY і UNION.
• підзапитів може перебувати і лева і праворуч від умови пошуку.
• У підзапитах можуть використовуватися функції агрегування без розділу GROUP BY, які автоматично видають спеціальне значення для будь-якої кількості рядків, спеціальний предикат IN, а також висловлювання, засновані на шпальтах.
• По можливості слід замість підзапитів використовувати з'єднання таблиць JOIN.
Приклади на вкладені запити:
2) пов'язані підзапити
У SQL можна створювати вкладені запити з посиланням на таблицю з зовнішнього запиту. В цьому випадку підзапит виконується багаторазово, по одному разу для кожного рядка таблиці із зовнішнього запиту. Тому важливо, щоб підзапит використовував індекс. Підзапит може звертатися до тієї ж таблиці, щоб і зовнішній. Якщо зовнішній запит повертає відносно невелике число рядків, то пов'язаний підзапит буде працювати швидше незв'язаного. Якщо підзапит повертає невелике число рядків, то пов'язаний запит виконається повільніше незв'язаного.
Приклади на пов'язані підзапити:
SELECT * FROM SalesPeople Main WHERE 1 (SELECT AVG (Amt) FROM Orders O2 WHERE O2.CNum = O1.CNum) // повертає всі замовлення, величина яких перевищує середню величини замовлення для даного покупця
3) Предикат EXISTS
Синтаксична форма: [NOT] EXISTS ()
Предикат використовує підзапит як аргумент і оцінює його як істинний, якщо в підзапиті є вихідні дані, а в іншому випадку як помилковий. Виконується підзапит один раз і може містити декілька стовпців, оскільки їх значення не перевіряються, а просто фіксується результат наявності рядків.
Примітки по предикату EXISTS:
• EXISTS - предикат, який повертає значення TRUE або FALSE, і його можна застосовувати окремо або разом з іншими булеві виразами.
• EXISTS не може використовувати функції агрегування в своєму підзапиті.
• В корелюють (пов'язаних, залежних - Correlated) підзапитах предикат EXISTS виконується для кожного рядка зовнішньої таблиці.
• Можна комбінувати предикат EXISTS з сполуками таблиць.
Приклади на предикат EXISTS:
SELECT * FROM Customer WHERE EXISTS (SELECT * FROM Customer WHERE City = 'San Jose') - повертає всіх покупців, якщо хтось із них проживає в San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send.SNum = First.SNum AND Send.CNum<>First.CNum) - повертає номера продавців, обслужити лише одного покупця.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum = T.SNum AND S.CNum<>T.CNum AND F.SNum = S.SNum) - повертає номера, імена та міста проживання всіх продавців, обслужити декількох покупців.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum = Send.SNum AND 1
4) Предикати кількісного порівняння
Синтаксична форма: [NOT] | = |> ANY | ALL ()
Ці предикати використовують як аргумент підзапит, однак, у порівнянні з предикатом EXISTS, вони застосовуються в кон'юнкції з предикатами відносини (=,<>.> =). У цьому сенсі вони схожі з предикатом IN, але застосовуються тільки з підзапитах. Стандарт допускає використовувати замість ANY ключове слово SOME, однак не всі СУБД його підтримують.
Примітки по предикатам порівняння:
• Предикат ALL приймає значення TRUE, якщо кожне значення, вибране в процесі виконання підзапиту, задовольняє умові, заданому в предикате зовнішнього запиту. Найчастіше він використовується з нерівностями.
• Предикат ANY приймає значення TRUE, якщо хоча б одне значення, вибране в процесі виконання підзапиту, задовольняє умові, заданому в предикате зовнішнього запиту. Найчастіше він використовується з нерівностями.
• Якщо підзапит не повертає рядків, то ALL автоматично приймає значення TRUE (вважається, що умова порівняння виконується), а для ANY - FALSE.
• Якщо порівняння не має значення TRUE ні для одного рядка і є одна або кілька рядків з NULL значенням, то ANY повертає UNKNOWN.
• Якщо порівняння не має значення FALSE ні для одного рядка і є одна або кілька рядків з NULL значенням, то ALL повертає UNKNOWN.
Приклади на предикат кількісного порівняння:
SELECT * FROM SalesPeople WHERE City = ANY (SELECT City FROM Customer)
SELECT * FROM Orders WHERE AmtALL (SELECT Rating FROM Customer WHERE City = 'Rome')
5) Предикат унікальності
Предикат служить для перевірка унікальності (відсутність дублів) в вихідних даних підзапиту. Причому в предикате UNIQUT рядки з NULL значеннями вважаються унікальними, а в предикате DISTINCT два невизначених значення вважаються рівними один одному.
6) Предикат збігів
Предикат MATCH перевіряє, чи буде значення рядка запиту збігатися зі значенням будь-якого рядка, отриманої в результаті підзапиту. Від предикатів IN І ANY такий підзапит відрізняється тим, що дозволяє обробляти «часткові» (PARTIAL) збігу, які можуть зустрічатися серед рядків, що мають частину NULL-значень.
7) Запити в розділі FROM
SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM (Amt) AS Tot_Amt FROM Orders GROUP BY CNum) WHERE City = 'London' AND Customer.CNum = Orders.CNum
// підзапит повертає сумарну величину замовлень, зроблених кожним покупцем з Лондона.
8) Рекурсивні запити
WITH RECURSIVE
Q1 AS SELECT ... FROM ... WHERE ...
Q2 AS SELECT ... FROM ... WHERE ...