Подзапросы с несколькими строками и одним столбцом

Если подзапрос возвращает более одной строки, его нельзя просто использовать с операторами сравнения, как это можно было делать со скалярными подзапросами.

Однако c подзапросами, возвращающими несколько строк и один столбец, можно использовать 3 дополнительных оператора.

Подзапрос и оператор ALL

С помощью оператора ALL мы можем сравнивать отдельное значение с каждым значением в наборе, полученным подзапросом. При этом данное условие вернёт TRUE, только если все сравнения отдельного значения со значениями в наборе вернут TRUE.

Например, нижеприведённый синтетический запрос проверяет для всех ли жилых помещений выполняется условие, что оно дешевле чем 200.

MySQL
SELECT 200 > ALL(SELECT price FROM Rooms)

Или же, более практический пример: нам необходимо найти имена всех владельцев жилья, которые сами при этом никогда не снимали жилье. Чтобы получить данный список, мы можем действовать следующим образом:

Получить список имён всех владельцев жилья

MySQL
SELECT DISTINCT name FROM Users INNER JOIN Rooms
ON Users.id = Rooms.owner_id

Получить список идентификаторов всех пользователей, снимавших жилье

MySQL
SELECT DISTINCT user_id FROM Reservations

Отфильтровать первый список всех владельцев по условию, что идентификатор владельца жилья не равен ни одному из идентификаторов пользователей, когда-либо снимавших жилье

MySQL
SELECT DISTINCT name FROM Users INNER JOIN Rooms
    ON Users.id = Rooms.owner_id
    WHERE Users.id <> ALL (
        SELECT DISTINCT user_id FROM Reservations
    )

Подзапрос и оператор IN

Оператор IN проверяет входит ли конкретное значение в набор значений. В качестве такого набора как раз может использоваться подзапрос, возвращающий несколько строк с одним столбцом.

Например, если нам необходимо получить всю информацию о владельцах жилья стоимостью больше 150 условных единиц, то это можно сделать следующим образом:

MySQL
SELECT * FROM Users WHERE id IN (
    SELECT DISTINCT owner_id FROM Rooms WHERE price >= 150
)

Подзапрос и оператор ANY

Условное выражение с ANY имеет схожее поведение, но оно возвращает TRUE, если хотя бы одно сравнение отдельного значения со значением в наборе вернёт TRUE.

Давайте с его помощью напишем такой же запрос, что мы делали с оператором IN: найдём пользователей, которые владеют хотя бы 1 жилым помещением стоимостью более 150.

MySQL
SELECT * FROM Users WHERE id = ANY (
    SELECT DISTINCT owner_id FROM Rooms WHERE price >= 150
)