How to stack one rowset atop another in SQL?

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 Staff, along with the
name and department number of each department in table Department.

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

Department Table

deptno dname loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

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

SELECT ename as ename_and_dname, deptno
FROM Staff
WHERE deptno = 10

UNION ALL

SELECT dname, deptno
FROM Department

Output

ename_and_dname deptno
CLARK 10
RAJ 10
MILLER 10
ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40

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  from Department

UNION ALL

SELECT name  FROM Staff
--------------------------------------------------------------------------------

SELECT deptno, name from Department

UNION ALL

SELECT deptno FROM Staff

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

SELECT deptno FROM Staff

UNION

SELECT deptno FROM Department

Output

deptno
10
20
30
40

Specifying UNION rather than UNION ALL will most likely result in a sort operation 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 Staff
UNION ALL
SELECT deptno FROM Department
 )

Output

deptno
10
20
30
40

Discussion

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. For example, although in this database we have limited the number of tables for teaching purposes, in real life if you are querying one table, there may be a more suitable way to query a single table.