Chapter 3. Working with Multiple Tables
This chapter introduces the use of joins and set operations to combine data from multiple tables. Joins are the foundation of SQL. Set operations are also very important. If you want to master the complex queries found in the later chapters of this book, you must start here, with joins and set operations.
3.1. Stacking One Rowset atop Another
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
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 7union all 8 select dname, deptno 9 from dept
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 ...