Recipe3.1.Stacking One Rowset atop Another | SQL Cookbook (Cookbooks (OReilly))

Recipe 3.1. Stacking One Rowset atop Another

Problem

You want to return data stored in more than one table, conceptually stacking one result set atop the other. The tables do not necessarily have a common key, but their columns do have the same data types. For example, you want to display the name and department number of the employees in department 10 in table EMP, along with the name and department number of each department in table DEPT. You want the result set to look like the following:

 ENAME_AND_DNAME      DEPTNO ---------------  ---------- CLARK                    10 KING                     10 MILLER                   10 ---------- ACCOUNTING               10 RESEARCH                 20 SALES                    30 OPERATIONS               40

Solution

Use the set operation UNION ALL to combine rows from multiple tables:

 1  select ename as ename_and_dname, deptno 2    from emp 3   where deptno = 10 4   union all 5  select '----------', null 6    from t1 7   union all 8  select dname, deptno 9    from dept

Discussion

UNION ALL combines rows from multiple row sources into one result set. As with all set operations, the items in all the SELECT lists must match in number and data type. For example, both of the following queries will fail:

 select deptno   |  select deptno, dname   from dept     |    from dept  union all      |   union select ename    |  select deptno   from emp      |    from emp

It is important to note, UNION ALL will include duplicates if they exist. If you wish to filter out duplicates, use the UNION operator. For example, a UNION between EMP.DEPTNO and DEPT.DEPTNO returns only four rows:

  select deptno   from emp  union select deptno   from dept    DEPTNO ---------        10        20        30        40

Specifying UNION rather than UNION ALL will most likely result in a sort operation in order to eliminate duplicates. Keep this in mind when working with large result sets. Using UNION is roughly equivalent to the following query, which applies DISTINCT to the output from a UNION ALL:

  select distinct deptno   from ( select deptno   from emp  union all select deptno   from dept          )    DEPTNO ---------        10        20        30        40

You wouldn't use DISTINCT in a query unless you had to, and the same rule applies for UNION; don't use it instead of UNION ALL unless you have to.