Объединение таблиц(вложенные запросы, джойны

Вложенный запрос используется для выборки данных, которые будут использоваться в условии отбора записей основного запроса. Его применяют для:

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

ключевое слово SELECT после которого указываются имена столбцов или выражения (чаще всего список содержит один элемент) ; ключевое слово FROM и имя таблицы, из которой выбираются данные; необязательное предложение WHERE; необязательное предложение GROUP BY: необязательное предложение HAVING. Вложенные запросы могут включаться в WHERE или HAVING так (в квадратных скобках указаны необязательные элементы, через | – один из элементов):

WHERE | HAVING выражение операторсравнения (вложенный запрос); WHERE | HAVING выражение, включающее вложенный запрос; WHERE | HAVING выражение [NOT] IN (вложенный запрос); WHERE | HAVING выражение операторсравнения ANY | ALL (вложенный запрос). Также вложенные запросы могут вставляться в основной запрос после ключевого слова SELECT

Вложенный запрос, возвращающий одно значение, может использоваться в условии отбора записей WHERE как обычное значение совместно с операциями =, <>, >=, <=, >, <.

SELECT title, author, price, amount
FROM book
WHERE price = (
         SELECT MIN(price) 
         FROM book
      );

Вложенный запрос, возвращающий одно значение, может использоваться в выражениях как обычный операнд, например, к нему можно что-то прибавить, вычесть и пр.

SELECT title, author, amount 
FROM book
WHERE ABS(amount - (SELECT AVG(amount) FROM book)) >3;

Вложенный запрос может возвращать несколько значений одного столбца. Тогда его можно использовать в разделе WHERE совместно с оператором IN.

WHERE имя_столбца IN (вложенный запрос, возвращающий один столбец)

Оператор IN определяет, совпадает ли значение столбца с одним из значений, содержащихся во вложенном запросе. При этом логическое выражение после WHERE получает значение истина. Оператор NOT IN выполняет обратное действие – выражение истинно, если значение столбца не содержится во вложенном запросе.

SELECT title, author, amount, price
FROM book
WHERE author IN (
        SELECT author 
        FROM book 
        GROUP BY author 
        HAVING SUM(amount) >= 12
      );

SELECT author, title, amount
FROM book
WHERE author IN (
        SELECT amount 
        FROM book 
        GROUP BY amount 
        HAVING COUNT(amount) = 1
);

Вложенный запрос, операторы ANY и ALL

Вложенный запрос, возвращающий несколько значений одного столбца, можно использовать для отбора записей с помощью операторов ANY и ALL совместно с операциями отношения (=, <>, <=, >=, <, >).

Операторы ANY и ALL используются в SQL для сравнения некоторого значения с результирующим набором вложенного запроса, состоящим из одного столбца. При этом тип данных столбца, возвращаемого вложенным запросом, должен совпадать с типом данных столбца (или выражения), с которым происходит сравнение.

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

amount > ANY (10, 12) эквивалентно amount > 10

amount < ANY (10, 12) эквивалентно amount < 12

amount = ANY (10, 12) эквивалентно (amount = 10) OR (amount = 12), а также amount IN (10,12)

amount <> ANY (10, 12) вернет все записи с любым значением amount, включая 10 и 12

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

amount > ALL (10, 12) эквивалентно amount > 12

amount < ALL (10, 12) эквивалентно amount < 10

amount = ALL (10, 12) не вернет ни одной записи, так как эквивалентно (amount = 10) AND (amount = 12) amount <> ALL (10, 12) вернет все записи кроме тех, в которыхamount равно 10 или 12

Важно! Операторы ALL и ANY можно использовать только с вложенными запросами. В примерах выше (10, 12) приводится как результат вложенного запроса просто для того, чтобы показать как эти операторы работают. В запросах так записывать нельзя.

Вывести информацию о тех книгах, количество которых меньше самого маленького среднего количества книг каждого автора.

SELECT title, author, amount, price
FROM book
WHERE amount < ALL (
        SELECT AVG(amount) 
        FROM book 
        GROUP BY author 
      );

Вывести информацию о тех книгах, количество которых меньше самого большого среднего количества книг каждого автора.

SELECT title, author, amount, price
FROM book
WHERE amount < ANY (
        SELECT AVG(amount) 
        FROM book 
        GROUP BY author 
      );

Вложенный запрос после SELECT

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

Пример

Вывести информацию о книгах, количество экземпляров которых отличается от среднего количества экземпляров книг на складе более чем на 3, а также указать среднее значение количества экземпляров книг.

Запрос:

SELECT title, author, amount, 
    (
     SELECT AVG(amount) 
     FROM book
    ) AS Среднее_количество 
FROM book
WHERE abs(amount - (SELECT AVG(amount) FROM book)) >3;

+-----------------------+------------------+--------+--------------------+
| title                 | author           | amount | Среднее_количество |
+-----------------------+------------------+--------+--------------------+
| Мастер и Маргарита    | Булгаков М.А.    | 3      | 7.6667             |
| Братья Карамазовы     | Достоевский Ф.М. | 3      | 7.6667             |
| Стихотворения и поэмы | Есенин С.А.      | 15     | 7.6667             |
+-----------------------+------------------+--------+--------------------+