Использование подзапросов в ORACLE

Типы подзапросов:

Однострочные
возвращают 0 или 1 строку
(если к тому же возвращается и один столбец то подзапрос скалярный)

Многострочные
возвращают одну или несколько строк

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

многостолбцовые

корелированные
(ссылаются на один или несколько столбцов во внешнем запросе)

вложенные
(помещены внутрь другого  подзапроса, уровень вложенности может достигать 255)


Однострочные подзапросы.

Подзапросы во фразе WHERE:

select col1, col2
from tab
where col_tab_id = (select col_tab_id
                    from tab
                    where col2 = 'XXX');

Во фразе WHERE  можно использовать операторы сравнения такие как:
=, <>, <, >, <=, >=

select col1, col2, col3
from tab
where col3 > (select avg(col3)
              from tab);

этот подзапрос является примером скалярного подзапроса.


Подзапросы во фразе HAVING:

select col1, avg(col2)
from tab
group by col1
having avg(col2) <  (select max(avg(col2))
                     from tab
                     group by col1)
order by col1;


Подзапросы во фразе FROM:
(встроенные представления)


select col1
from (select col1
      from tab
      where col1 < 10);


более полезный пример:

select t1.col_tab1_id, col2, t2.COL_TAB1_ID_COUNT
from tab1 t1, (select col_tab1_id, count(col_tab1_id) COL_TAB1_ID_COUNT
               from tab2
               group by col_tab1_id) t2
where t1.col_tab1_id =  t2.col_tab1_id;


Подзапросы не могут содержать фразу order by.
Любое упорядочивание должно проводиться во внешнем запросе:

select col1, col2, col3
from tab
where col3 > (select avg(col3) from tab)
order by col1 desc;

Многострочные подзапросы
Возвращают во внешний запрос одну или более строк.

Чтобы обработать подзапрос, возвращающий несколько строк, необходимо использовать один из операторов:
IN, ANY или ALL.

select col1, col2
from tab
where col1 IN (5,6,7);

Использование IN в многострочных подзапросах:

select col1, col2
from tab
where col1 IN (select col1
                from tab
                where col2 like '_z%');


select col1, col2
from tab1
where col1 NOT IN (select col1 from tab2 );


Использование ANY в многострочных подзапросах:

ANY используется для сравнения значения с любым значением в списке.
Перед ANY можно использовать операторы сравнения такие как:
=, <>, <, >, <=, >=

select col1, col2
from tab1
where col3 < ANY (select col1 from tab2 );


Использование ALL в многострочных подзапросах:

ALL используется для сравнения значения со всеми значениями из списка.
Перед ALL можно использовать операторы сравнения такие как:
=, <>, <, >, <=, >=

select col1, col2
from tab1
where col3 > ALL (select col1 from tab2 );


Многостолбцовые подзапросы:

Подзапросы могут возвращать несколько столбцов:

select col1, col2, col3
from tab1
where (col1, col3) IN
      (select col1, min(col3)
       from tab1
       group by col2);


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

Коррелированный подзапрос выполняется по одному разу для каждой строки внешнего запроса.
Коррелированный подзапрос может разрешать значения NULL.

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

select col1, col2, col3, col4
from tab out
where col4 > (select avg(col4) from tab inn
              where inn.col2 = out.col2);

тут строки внешнего запроса по очереди передаются в подзапрос.


Использование EXISTS и NOT EXISTS с коррелированными подзапросами.

EXISTS используется для проверки, существует ли хоть одна строка, возвращенная подзапросом.
NOT EXISTS проверяет что не существует ни одной строки, возвращенной подзапросом.

select col1, col2
from tab out
where EXISTS (select col1 from tab inn
              where inn.col3 = out.col1);

не важно сколько строк возвращает подзапрос,
важно знать, возвращаются ли в принципе какие либо строки.

Подзапрос не обязян возвращать столбец, можно возвратить литеральное значение,
что повысит производительность:

select col1, col2
from tab out
where EXISTS (select 1 from tab inn
              where inn.col3 = out.col1);


до тех пор, пока подзапрос возвращает одну или более строк
EXISTS возвращает TRUE.

если подзапрос не возвращает ни одной строки
EXISTS возвращает FALSE.

Использование NOT EXISTS с коррелированными подзапросами.

select col1, col2
from tab1 out
where NOT EXISTS (select 1 from tab2 inn
                  where inn.col1 = out.col1);


Отличие EXISTS от IN

EXISTS проверяет только сам факт существования строк
IN проверяет их реальные значения
EXISTS более производительные оператор чем IN
Если в списке значений есть NULL, то NOT EXISTS возвратит TRUE, а NOT IN - FALSE.

select col1, col2
from tab1 out
where NOT EXISTS (select 1 from tab2 inn
                  where inn.col1 = out.col1);


select col1, col2
from tab1
where NOT IN (select col1 from tab2);

Разрешить NULL можно так:

select col1, col2
from tab1
where NOT IN (select nvl(col1,0) from tab2);

Вложенные подзапросы:

максимальная глубина вложенности - 255
Лучше использовать соединения чем вложенность.
Глубокая вложенность влияет на производительность.

select col1, avg(col2)
from tab1
group by col1
having avg(col2) < (select max(avg(col1))
                    from tab1
                    where col1 IN (select col1 from tab2 where col2 > 1)
                    group by col1)
order by col1;


UPDATE и DELETE c подзапросами:

update tab1 set col1 = (select avg(col1) from tab2)
where col2 = 4;

delete from tab1
where col1 > (select avg(col1) from tab2);