Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как оператор ANY работает в SQL, используя его с подзапросом или предложением VALUES, а также его поведение при обработке нулевых значений.
Таблицы базы данных
Давайте предположим, что у нас есть две таблицы базы данных, которые образуют отношение “один ко многим”:
Таблица студент
является родительской и содержит следующие две записи:
| id | first_name | last_name | admission_score | |----|------------|-----------|-----------------| | 1 | Alice | Smith | 8.95 | | 2 | Bob | Johnson | 8.75 |
И таблица student_grade
является дочерней, и она содержит следующие строки:
| id | class_name | grade | student_id | |----|------------|-------|-----------| | 1 | Math | 10 | 1 | | 2 | Math | 9.5 | 1 | | 3 | Math | 9.75 | 1 | | 4 | Science | 9.5 | 1 | | 5 | Science | 9 | 1 | | 6 | Science | 9.25 | 1 | | 7 | Math | 8.5 | 2 | | 8 | Math | 9.5 | 2 | | 9 | Math | 9 | 2 | | 10 | Science | 10 | 2 | | 11 | Science | 9.4 | 2 |
SQL ЛЮБОЙ оператор
Если мы хотим, чтобы все учащиеся с баллом приема превышали хотя бы один класс, мы могли бы использовать следующий SQL-запрос:
SELECT id, first_name, last_name, admission_score FROM student WHERE admission_score > ANY ( SELECT student_grade.grade FROM student_grade WHERE student_grade.student_id = student.id ) ORDER BY id
Внешний запрос фильтрует строки student
таблицы с помощью оператора ANY, который возвращает true
, если admission_score
больше, чем по крайней мере одно значение оценки класса, возвращаемое базовым подзапросом. В противном случае оператор ANY возвращает false
, что означает, что admission_score
ниже, чем все оценки в классе учащихся.
При выполнении приведенного выше SQL-запроса мы получаем следующий набор результатов:
| id | first_name | last_name | admission_score | |----|------------|-----------|-----------------| | 2 | Bob | Johnson | 8.75 |
Поскольку у Боба 8,5 балла по математике, а его вступительный балл составил 8,75, оператор ANY возвращает true
, и запись Боба включена в набор результатов. С другой стороны, оценки всех классов Алисы превышают ее балл при поступлении, поэтому оператор ANY вернет false
, поэтому предложение outer query WHERE не будет включать строку Алисы в конечный набор результатов.
SQL Обработка ЛЮБОГО оператора с нулевым значением
Чтобы увидеть, как ведет себя оператор ANY, когда подзапрос возвращает значение NULL, рассмотрим следующий SQL-запрос:
SELECT id, first_name, last_name, admission_score FROM student WHERE admission_score > ANY ( VALUES (8), (null) ) ORDER BY id
Предложение VALUES
позволяет нам определить виртуальную таблицу с помощью списка строк, разделенных запятыми. В нашем случае виртуальная таблица состоит из одного столбца и двух строк.
При выполнении приведенного выше SQL-запроса мы видим, что он возвращает обоих наших студентов:
| id | first_name | last_name | admission_score | |----|------------|-----------|-----------------| | 1 | Alice | Smith | 8.95 | | 2 | Bob | Johnson | 8.75 |
Значение NULL игнорируется, поскольку предыдущий запрос эквивалентен следующему SQL-запросу:
SELECT id, first_name, last_name, admission_score FROM student WHERE admission_score > 8 OR admission_score > NULL ORDER BY id
Значение admission_score > NULL
оценивается как NULL
. Но из-за оператора ИЛИ
, если первое условие возвращает true
, то предложение WHERE также вернет true
. И, поскольку у обоих студентов балл допуска превышает 8, то оба студента будут включены во внешний набор результатов запроса.
Как и в случае с оператором OR, оператор ANY позволяет оптимизатору базы данных прервать выполнение на ранней стадии (например, короткое замыкание) при получении первой оценки true
условия, что означает, что значения слева и справа соответствуют предоставленному оператору сравнения.
Вывод
Оператор ANY принимает левое значение и сравнивает его с помощью предоставленного оператора (например, <, >, =) со всеми значениями, предоставленными правым подзапросом или выражением. Если хотя бы одно сравнение левого значения с правым значением возвращает true
, то оператор ANY вернет true
. В противном случае оператор ANY возвращает false
.
Оператор ANY эквивалентен НЕКОТОРЫМ, поэтому вы можете использовать обе формы взаимозаменяемо.