Подзапросы в Oracle

Подзапросы представляют собой обычные SQL-запросы, которые являются частью другого SQL-запроса.

Подзапросы - важная часть в изучении SQL. Некоторые данные просто не могут быть получены, если их не использовать. Далее будут рассмотрены примеры использования подзапросов в Oracle.

Подготовка тестовых данных

create table books(
    book_id number primary key,
    book_name varchar2(200) not null,
    author varchar2(50 char) not null,
    release_year number not null
);

create table book_orders(
    book_id number not null,
    quantity number(2) not null,
    order_date date not null
);


comment on table books is 'Книги';
comment on table book_orders is 'Статистика продаж за день';


insert into books
values(1, 'Властелин колец', 'Толкин', 1954);

insert into books
values(2, 'Гордость и предубеждение', 'Джейн Остин', 1813);

insert into books
values(3, 'Тёмные начала', 'Филип Пулман', 1995);

insert into books
values(4, 'Автостопом по галактике', 'Дуглас Адамс', 1979);

insert into book_orders
values(1, 1, to_date('31.12.2005', 'dd.mm.yyyy'));

insert into book_orders
values(1, 4, to_date('30.12.2005', 'dd.mm.yyyy'));

insert into book_orders
values(2, 2, to_date('10.05.2005', 'dd.mm.yyyy'));

insert into book_orders
values(2, 1, to_date('12.05.2005', 'dd.mm.yyyy'));

insert into book_orders
values(3, 2, to_date('05.11.2005', 'dd.mm.yyyy'));

Подзапросы в where- части запроса

Получим информацию о продажах книги "Властелин колец":

select bo.*
from book_orders bo
where bo.book_id = (
    select book_id
    from books
    where book_name = 'Властелин колец'
);

Здесь использовался подзапрос, чтобы определить id книги с названием "Властелин колец".

Если выполнить подзапрос отдельно:

select book_id
from books
where book_name = 'Властелин колец'

То мы получим одну строку, которая будет содержать значение book_id, равое 1. Поэтому самый первый запрос эквивалентен следующему:

select bo.*
from book_orders bo
where bo.book_id = 1

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

select bo.*
from book_orders bo
where bo.book_id = (
	select book_id,
               book_name
        from books
        where book_name = 'Властелин колец'
)

Данный запрос выдаст ошибку ORA-00913: too many values, т.к. подзапрос возвращает одну строку с двумя колонками.

select bo.*
from book_orders bo
where bo.book_id = (select book_id from books)

А здесь будет ошибка ORA-01427: single-row subquery returns more than one row, что переводится как "однострочный подзапрос возвращает более одной строки". Из-за этого результат выполнения данного подзапроса нельзя подставить в условие сравнения, т.к. сравнение должно работать с одиночными значениями.

Подзапросы в select-части

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

select b.*,
       (select count(*) from book_orders) ord_cnt
from books b

Здесь мы добавили колонку "ord_cnt", которая содержит количество всех имеющихся заказов по всем книгам.

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

Т.к. утверждение 2 > 10 ложно, подзапрос не вернет ни одной записи, поэтому значение в соответствующей колонке будет NULL.

Подзапросы во FROM части

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

select b_orders.*
from (
select b.book_id, b.book_name, bo.quantity, bo.order_date
from books b
join book_orders bo on bo.book_id = b.book_id) b_orders
where b_orders.quantity > 1

Здесь мы написали отдельный запрос, дали ему псевдоним b_orders, поместили его во FROM часть, как будто это обычная таблица, и дальше работаем с псевдонимом данного подзапроса.

В подзапросе использовались соединения.

Сам подзапрос можно выполнить отдельно:

select b.book_id, b.book_name, bo.quantity, bo.order_date
from books b
join book_orders bo on bo.book_id = b.book_id

Как можно заметить, там есть строки, в которых количество(столбец quantity) равен 1.

Но в первом примере этих строк нет, т.к. мы прописали условие where b_orders.quantity > 1.

Подзапросов во FROM части может быть несколько, т.е. мы можем соединять их, как обычные таблицы(опять, про соединения таблиц можно почитать вот здесь.

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

Коррелированные подзапросы

Коррелированный подзапрос - это такой подзапрос, который использует для своей работы данные из внешнего по отношению к нему запроса. Например:

select b.*,
       (select count(*)
        from book_orders
        where book_id = b.book_id) ord_cnt
from books b

Здесь подзапрос подсчитывает количество дней, в которые производились продажи определенной книги. Т.е. подзапрос считает количество строк в таблице book_orders по значению колонки book_id, которую он берет из внешнего запроса. В условии прописывается where book_id = b.book_id, что означает: "Возьми для каждой строки из основного запроса значение колонки book_id, и посчитай количество строк в таблице book_orders с таким же book_id."

Подзапросы в IN, NOT IN

Ранее уже рассматривались примеры и особенности использования IN и NOT IN в SQL. В качестве перечисляемых значений в этих операторах были значения, которые прописывал сам программист. На практике чаще всего в качестве источника для значений в этих операторах используются подзапросы:

select b.*
from books b
where b.book_id in (
    select book_id
    from book_orders bo
    where bo.quantity < 2)

Данный запрос выводит список книг, у которых были продажи менее, чем по 2 штуки в день.

Cписок книг для оператора `IN` формируется в результате выполнения подзапроса, а не ручного кодирования значений программистом.

Подзапросы в IN и NOT IN должны возвращать строки с одной колонкой. Следующий запрос выдаст ошибку ORA-00913: too many values, т.к. подзапрос получает список строк с двумя колонками:

select b.*
from books b
where b.book_id in (
    select book_id, quantity
    from book_orders bo
    where bo.quantity < 2)

При этом не следует забывать об особенности использования NOT IN: Если в списке значений для проверки есть хотя бы одно NULL-значение, то результат выражения будет ложным, и запрос не вернет никаких данных:

select b.*
from books b
where b.book_id  not in (
    select book_id
    from book_orders bo
    where bo.quantity < 2

    union

    select null
    from dual)

Здесь при помощи объединения запросов в выборку подзапроса была добавлена строка с одним NULL-значением, и как следствие, запрос не вернул никаких данных.

Комментарии