Объединение запросов
Функция

Определение объединения результатов запросов выборки данных.

Спецификация
       
< объединение запросов >::=
< список столбцов >::=
< составной запрос >::=
Синтаксические правила
  1. Если не используется конструкция CORRESPONDING, то все < составные запросы > одного < объединения запросов > должны иметь одинаковое количество совместимых по типу данных столбцов (различными могут быть только имена столбцов).

  2. Допускается объединение столбцов с фиксированной и переменной длиной без явного указания приведения типа:

    • для столбцов типа CHAR и VARCHAR результат VARCHAR;

    • для столбцов типа BYTE и VARBYTE результат VARBYTE;

    • для столбцов типа NCHAR и NCHAR VARYING результат NCHAR VARYING.

    select 'Модели автомобиля' from auto
     union
    select distinct model from auto;
    
    select 'Модель', 'год выпуска' from auto
     union
    select distinct model, to_char(year+1900) from auto;
  3. Типы числовых литералов при необходимости преобразуются к другим числовым типам при выполнении объединения: SMALLINT = > INTEGER = > BIGINT = > DECIMAL = > REAL = > DOUBLE, для столбцов и выражений с числовыми типами данных неявное приведение типов не производится.

    Запрос:

    select 5.78
     union
    select 10;
    эквивалентен:
    select 5.78
     union
    select cast 10 as decimal;
    | 5.78|
    | 10.0|
  4. Синтаксические скобки «(» и «)» являются необязательными и предназначены, в основном, для логического выделения элементов SQL-запроса.

    
    from S1 union S2
    from (S1) union (S2)
    from (S1 union S2)
    from ((S1) union (S2))
    from S1 union (S2)
    from (S1 union (S2))
    from (S1) union S2
    from ((S1) union S2)
    create or replace table t1 (i int, c char(5));
    insert into t1 (i,c) values(1,'aaaaa');
    insert into t1 (i,c) values(2,'bbbbb');
    
    create or replace table t2 (i int, c char(5));
    insert into t1 (i,c) values(3,'ccccc');
    insert into t1 (i,c) values(4,'ddddd');

    Эти 3 конструкции эквивалентны:

    select * from ((select * from t1) union (select * from t2));
    select * from ((table t1) union (table t2));
    select * from (values (1,'aaaaa'), (2, 'bbbbb')) union (values (3,'ccccc'), (4, 'ddddd'));
    |          1|aaaaa|
    |          2|bbbbb|
    |          3|ccccc|
    |          4|ddddd|
    
    select * from (select * from t1 union select * from t2);
    select * from select * from t1 union select * from t2;  не работает
    select * from (select * from t1) union (select * from t2);
    
    select * from (select * from t1) union select * from t2;
    select * from (select * from t1 union (select * from t2));
    select * from ((select * from t1) union select * from t2);
  5. Если задана конструкция CORRESPONDING BY (объединение перечисленных столбцов), то < объединение запросов > будет содержать столбцы, указанные в < списке столбцов >.

  6. < Список столбцов > должен содержать не повторяющиеся имена столбцов, совпадающие с именами столбцов из < составных запросов >.

  7. Каждое имя столбца в списке соответствия должно быть явно задано во всех < составных запросах >.

    select x1, x2, a, x3, b, c from T1 …
    union corresponding  by (а, b, c)
    select  a, y1, b, c, y2, y3 from T2 …
  8. Дублирование имен столбцов в списке CORRESPONDING запрещено. Недопустимая конструкция: CORRESPONDING BY (make, make).

Общие правила
  1. Если UNION не указан, то < объединение запросов > является просто < запросом выборки >.

  2. Если UNION указан, то результат < объединения запросов > получается следующим образом:

    • результат инициализируется пустой таблицей;

    • в результат вставляются все строки первого < составного запроса > и затем все строки второго < составного запроса >.

  3. Если ALL не указан, из результата исключаются дубликаты строк.

    Пусть есть две таблицы:

     tab1   tab2
    1       2
    2       4
    3       5
    4       7
    3
    select * from tab1      select * from tab1
    union all               union
    select * from tab2;     select * from tab2;
    | 1|                    |          1|
    | 2|                    |          2|
    | 3|                    |          3|
    | 4|                    |          4|
    | 3|                    |          5|
    | 2|                    |          7|
    | 4|
    | 5|
    | 7|
  4. В < объединении запросов > < ORDER BY-спецификация > может использоваться только один раз. Она располагается в последнем операторе SELECT < объединения запросов > и применяется ко всему результату. Столбцы упорядочения в этом случае задаются путем указания их порядковых номеров или имен столбцов, если имена столбцов идентичны во всех < составных запросах >.

    select 'Модель', 'год выпуска' from auto
    union
    select distinct model, to_char(year+1900) from auto order by 2;

    select personid from auto
    union all
    select personid from person order by personid;
  5. В < объединении запросов > < GROUP BY-спецификация > может применяться как к составным запросам, так и ко всей комбинированной выборке.

    create or replace table test1(i int);
    insert into test1 values(2);
    insert into test1 values(1);
    insert into test1 values(2);
    
    create or replace table test2(j int);
    insert into test2 values(3);
    insert into test2 values(2);
    insert into test2 values(3);
    
    select v1, count(v1) from
    (select i as v1 from test1
      union all
     select j as v1 from test2)
    group by v1;
    
    V1
     --
    |          1|          1|
    |          2|          3|
    |          3|          2|
    (select i,count(i) from test1 group by i)
    union all
    (select j,count(j) from test2 group by j);
    
    |          1|          1|
    |          2|          2|
    |          2|          1|
    |          3|          2|
  6. I-й столбец результата < объединения запросов > будет именован только в том случае, если среди исходных i-х столбцов есть хотя-бы один именованный столбец. В качестве имени результирующего столбца будет взято имя первого именованного столбца из операндов комбинированного запроса.

    select 1 as aaa, 1,         1
    union
    select 2 as bbb, 2,         2
    union
    select 3 as ccc, 3 as ccc,  3;
    
    
    AAA         CCC
     ---         ---
    |          1|          1|          1|
    |          2|          2|          2|
    |          3|          3|          3|

  7. Разрешено объединение по UNION столбцов следующих типов:

    • CHAR и VARCHAR (результат VARCHAR);

    • BYTE и VARBYTE (результат VARBYTE);

    • NCHAR и NVARCHAR (результат NVARCHAR).

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

    create or replace table t_1 (i int, b boolean, c char(10));
    insert into t_1 values (1,TRUE,'Sample');
    insert into t_1 values (2,FALSE,'One more');
    insert into t_1 values (3,FALSE,'Extra');
    
    create or replace table t_2 (i int, b boolean, c char(10));
    insert into t_2 values (2,FALSE,'Union');
    insert into t_2 values (3,FALSE,'Intersect');
    insert into t_2 values (4,FALSE,'Except');
    
    create or replace table t_3 (i int, b boolean, c char(10));
    insert into t_3 values (3,FALSE,'Insert');
    insert into t_3 values (4,TRUE,'Delete');
    insert into t_3 values (5,FALSE,'Update');
    
    create or replace view v as
    select * from t_1
     union all
    select * from t_2
     union all
    select * from t_3;
    select * from v;
    |          1|T|Sample    |
    |          2|F|One more  |
    |          3|F|Extra     |
    |          2|F|Union     |
    |          3|F|Intersect |
    |          4|F|Except    |
    |          3|F|Insert    |
    |          4|T|Delete    |
    |          5|F|Update    |
    
    insert into v values (6,FALSE,'New');
    insert into v values (7,FALSE,'More new');
    insert into v values (8,FALSE,'Last new');
    delete from v where b;
    update v set c = '3rd' where i=3;
    select * from v;
    |          2|F|One more  |
    |          3|F|3rd       |
    |          6|F|New       |
    |          7|F|More new  |
    |          8|F|Last new  |
    |          2|F|Union     |
    |          3|F|3rd       |
    |          4|F|Except    |
    |          3|F|3rd       |
    |          5|F|Update    |
    
    
    delete from v where c like '%e%';
    select * from v;
    |          3|F|3rd       |
    |          2|F|Union     |
    |          3|F|3rd       |
    |          3|F|3rd       |
  9. Сравнительные результаты выполнения комбинированного запроса приведены в таблице 2.

    Таблица 2. Сравнение результатов выполнения комбинированного запроса
    Конструкция CORRESPONDING
    Не заданаЗадана
     без опции BY…с опцией BY…
    Первый и второй < составные запросы > должны иметь одинаковое количество выбираемых столбцов. Имена столбцов < составных запросов > могут быть разными. Объединение значений столбцов выполняется по порядковым номерам столбцов < составных запросов >.Первый и второй < составные запросы > могут иметь разное количество выбираемых столбцов. Имена столбцов < составных запросов > и их количество могут быть разными, но, по крайней мере, одно имя столбца должно быть общим для первого и второго < составного запроса >. Столбцы с общими именами в первом и втором < составных запросах > могут иметь разные порядковые номера. Объединение значений столбцов выполняется по общим именам столбцов из < составных запросов > и в том порядке, в каком они перечислены в первом < составном запросе >.Первый и второй < составные запросы > могут иметь разное количество выбираемых столбцов. Имена столбцов < составных запросов > и их количество могут быть разными, но имена столбцов, перечисленные в < списке столбцов >, должны присутствовать во всех < составных запросах >. Если столбец указан в < списке столбцов >, но отсутствует в некоторых < составных запросах >, то в < объединение запросов > он не включается. Столбцы с общими именами в первом и втором < составных запросах > могут иметь разные порядковые номера. Объединение значений столбцов выполняется по именам столбцов из < списка столбцов > и в том порядке, в каком они перечислены в < списке столбцов >.

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

    create or replace table t_1 (i int, b boolean, c char(10));
    insert into t_1 values (1,TRUE,'Sample');
    insert into t_1 values (2,FALSE,'One more');
    insert into t_1 values (3,FALSE,'Extra');
    
    create or replace table t_2 (i int, b boolean, c char(10));
    insert into t_2 values (2,FALSE,'Union');
    insert into t_2 values (3,FALSE,'Intersect');
    insert into t_2 values (4,FALSE,'Except');
    
    create or replace table t_3 (i int, b boolean, c char(10));
    insert into t_3 values (3,FALSE,'Insert');
    insert into t_3 values (4,TRUE,'Delete');
    insert into t_3 values (5,FALSE,'Update');
    
    create or replace view v as
    select * from t_1
     union all
    select * from t_2
     union all
    select * from t_3;
    select * from v;
    
    |          1|T|Sample    |
    |          2|F|One more  |
    |          3|F|Extra     |
    |          2|F|Union     |
    |          3|F|Intersect |
    |          4|F|Except    |
    |          3|F|Insert    |
    |          4|T|Delete    |
    |          5|F|Update    |
    
    insert into v values (6,FALSE,'New');
    insert into v values (7,FALSE,'More new');
    insert into v values (8,FALSE,'Last new');
    delete from v where b;
    update v set c = '3rd' where i=3;
    select * from v;
    
    |          2|F|One more  |
    |          3|F|3rd       |
    |          6|F|New       |
    |          7|F|More new  |
    |          8|F|Last new  |
    |          2|F|Union     |
    |          3|F|3rd       |
    |          4|F|Except    |
    |          3|F|3rd       |
    |          5|F|Update    |
    
    
    delete from v where c like '%e%';
    select * from v;
    
    |          3|F|3rd       |
    |          2|F|Union     |
    |          3|F|3rd       |
    |          3|F|3rd       |
Примеры
create or replace table test1(id int, i int, ch char(11), j int, nch nchar(13), b boolean);

create or replace table test2(id int, vch varchar(11), k int, nch nchar(13), ch char(20), i int);

create or replace table test3(id int, nvch nchar varying(11), l int, ch char(13), nch nchar(16), i int, b blob);

insert into test1 values(1,10,'aaa',10,n'naaa',true);
insert into test1 values(2,20,'bbb2',20,n'nbbb',true);
insert into test2 values(2,'bbb1',20,n'nbbb','bbb2',20);
insert into test3 values(3,n'nccc1',30,'ccc',n'nccc2',30,NULL);

а) без использования < списка столбцов >.

Общими для всех трех составных запросов являются столбцы id, ch, nch.

В объединенный запрос они попадают в том порядке, в каком перечислены в первом составном запросе.

select * from test1
union all corresponding
select * from test2
union all corresponding
select * from test3;

или

table test1
union all corresponding
table test2
union all corresponding
table test3;
ID         I           CH           NCH
--         -           --           ---
|     1   |   10      |     aaa    |    naaa       |
|     2   |   20      |     bbb2   |    nbbb       |
|     2   |   20      |     bbb2   |    nbbb       |
|     3   |   30      |     ccc    |    nccc2      |

б) с использованием < списка столбцов >.

Общими для все трех составных запросов являются столбцы ch, id. Столбец i, хотя и указан в < списке столбцов >, в < объединение запросов > не попадает, т.к. отсутствует во втором и третьем составном запросе. Порядок столбцов в < объединении запросов > определяется первым составным запросом, а не списком столбцов в опции BY….

select * from test1
 union all corresponding by (ch,id,i)
select * from test2
 union all corresponding by (id,ch)
select * from test3;

или

table test1
union all corresponding by (ch,id,i)
table test2
union all corresponding by (id,ch)
table test3;
ID          CH
 --          --
|          1|aaa                 |
|          2|bbb2                |
|          2|bbb2                |
|          3|ccc                 |