Использование операторов IN или EXISTS вместо оператора ANY

Мы можем также использовать оператор IN, чтобы создать запрос, аналогичный предыдущему:

SELECT *
from Salespeople
WHERE city IN (SELECT city
FROM Customers);

Этот запрос будет производить вывод, показанный в Рисунке 13.2.

=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city IN (SELECT city |
| FROM Customers); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1002 Serres San Jose 0.13 |
| 1004 Motika London 0.11 |
===============================================

Рисунок 13.2. Использование IN в качестве альтернативы к ANY.

Однако оператор ANY может использовать другие реляционные операторы, кроме равенства (=), и, таким образом, делать сравнения, которые являются выше возможностей IN. Например, мы могли бы найти всех продавцов с их заказчиками, имена которых следуют в алфавитном порядке за именами продавцов (вывод показан на Рисунке 13.3).

SELECT *
FROM Salespeople
WHERE sname < ANY (SELECT cname
FROM Customers);

=============== SQL execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE sname < ANY |
| (SELECT cname |
| FROM Customers); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1003 Axelrod New York 0.10 |
===============================================

Рисунок 13.3. Использование оператора ANY с оператором "меньше" (<).

Были выбраны все строки, кроме Serres и Rifkin, потому что нет заказчиков, чьи имена следовали бы за ними в алфавитном порядке. Обратите внимание, что это является основным эквивалентом следующему запросу с EXISTS (вывод показывается на Рисунке 13.4):

SELECT *
FROM Salespeople outer
WHERE EXISTS (SELECT *
FROM Customers inner
WHERE outer.sname < inner.cname);

=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople outer |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers inner |
| WHERE outer.sname < inner.cname); |
| ============================================= |
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1003 Axelrod New York 0.10 |
===============================================

Рисунок 13.4. Использование EXISTS как альтернатива оператору ANY.

Любой запрос, который может быть сформулирован с ANY (или, как мы увидим, с ALL), мог быть также сформулирован с EXISTS, хотя наоборот будет неверно. Строго говоря, вариант с EXISTS не абсолютно идентичен вариантам с ANY или с ALL из-за различия в том, как обрабатываются пустые (NULL) значения (что будет обсуждаться позже в этой главе). Тем не менее, с технической точки зрения, вы могли бы делать это без ANY и ALL, если бы вы стали очень находчивы в использовании EXISTS (и IS NULL).

Большинство пользователей, однако, находят ANY и ALL более удобными в использовании, чем EXISTS, который требует соотнесенных подзапросов. Кроме того, в зависимости от реализации, ANY и ALL могут, по крайней мере, в теории, быть более эффективными, чем EXISTS.

Подзапросы ANY или ALL могут выполняться один раз и иметь вывод, используемый чтобы определять предикат для каждой строки основного запроса. EXISTS, с другой стороны, берет соотнесенный подзапрос, который требует, чтобы весь подзапрос повторно выполнялся для каждой строки основного запроса. Сервер SQL пытается найти наиболее эффективный способ выполнения любой команды и может попробовать преобразовать менее эффективную формулу запроса в более эффективную, но вы не всегда можете рассчитывать на получение самой эффективной формулировки.

Основная причина для формулировки EXISTS как альтернативы ANY и ALL в том, что ANY и ALL могут быть несколько неоднозначны, из-за способа использования этих терминов в английском языке, как вы это скоро увидите. С приходом понимания различия способов формулирования данного запроса, вы сможете поработать над процедурами, которые сейчас кажутся Вам трудными или неудобными.