Stacking One Rowset atop Another - SQL Server

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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table