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

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

Однако 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
)