5. Multiple tables: Step 1

Demo

List the contents of two tables

We can combine two or more tables by listing them in the FROM clause:

SQL

SELECT * FROM dept, emp;

Output

deptno dname location empno ename job mgr hiredate sal comm deptno
10 Accounting New York 7369 SMITH CLERK 7902 1993-06-13 800.00 0.00 20
20 Research Dallas 7369 SMITH CLERK 7902 1993-06-13 800.00 0.00 20
30 Sales Chicago 7369 SMITH CLERK 7902 1993-06-13 800.00 0.00 20
40 Operations Boston 7369 SMITH CLERK 7902 1993-06-13 800.00 0.00 20
10 Accounting New York 7499 ALLEN SALESMAN 7698 1998-08-15 1600.00 300.00 30
20 Research Dallas 7499 ALLEN SALESMAN 7698 1998-08-15 1600.00 300.00 30
30 Sales Chicago 7499 ALLEN SALESMAN 7698 1998-08-15 1600.00 300.00 30
40 Operations Boston 7499 ALLEN SALESMAN 7698 1998-08-15 1600.00 300.00 30
10 Accounting New York 7521 WARD SALESMAN 7698 1996-03-26 1250.00 500.00 30
20 Research Dallas 7521 WARD SALESMAN 7698 1996-03-26 1250.00 500.00 30
30 Sales Chicago 7521 WARD SALESMAN 7698 1996-03-26 1250.00 500.00 30
40 Operations Boston 7521 WARD SALESMAN 7698 1996-03-26 1250.00 500.00 30
10 Accounting New York 7566 JONES MANAGER 7839 1995-10-31 2975.00 20
20 Research Dallas 7566 JONES MANAGER 7839 1995-10-31 2975.00 20
30 Sales Chicago 7566 JONES MANAGER 7839 1995-10-31 2975.00 20
40 Operations Boston 7566 JONES MANAGER 7839 1995-10-31 2975.00 20
10 Accounting New York 7654 MARTIN SALESMAN 7698 1998-12-05 1250.00 1400.00 30
20 Research Dallas 7654 MARTIN SALESMAN 7698 1998-12-05 1250.00 1400.00 30
30 Sales Chicago 7654 MARTIN SALESMAN 7698 1998-12-05 1250.00 1400.00 30
40 Operations Boston 7654 MARTIN SALESMAN 7698 1998-12-05 1250.00 1400.00 30
10 Accounting New York 7698 BLAKE MANAGER 7839 1992-06-11 2850.00 30
20 Research Dallas 7698 BLAKE MANAGER 7839 1992-06-11 2850.00 30
30 Sales Chicago 7698 BLAKE MANAGER 7839 1992-06-11 2850.00 30
40 Operations Boston 7698 BLAKE MANAGER 7839 1992-06-11 2850.00 30
10 Accounting New York 7782 CLARK MANAGER 7839 1993-05-14 2450.00 10
20 Research Dallas 7782 CLARK MANAGER 7839 1993-05-14 2450.00 10
30 Sales Chicago 7782 CLARK MANAGER 7839 1993-05-14 2450.00 10
40 Operations Boston 7782 CLARK MANAGER 7839 1993-05-14 2450.00 10
10 Accounting New York 7788 SCOTT ANALYST 7566 1996-03-05 3000.00 20
20 Research Dallas 7788 SCOTT ANALYST 7566 1996-03-05 3000.00 20
30 Sales Chicago 7788 SCOTT ANALYST 7566 1996-03-05 3000.00 20
40 Operations Boston 7788 SCOTT ANALYST 7566 1996-03-05 3000.00 20
10 Accounting New York 7839 KING PRESIDENT 1990-06-09 5000.00 0.00 10
20 Research Dallas 7839 KING PRESIDENT 1990-06-09 5000.00 0.00 10
30 Sales Chicago 7839 KING PRESIDENT 1990-06-09 5000.00 0.00 10
40 Operations Boston 7839 KING PRESIDENT 1990-06-09 5000.00 0.00 10
10 Accounting New York 7844 TURNER SALESMAN 7698 1995-06-04 1500.00 0.00 30
20 Research Dallas 7844 TURNER SALESMAN 7698 1995-06-04 1500.00 0.00 30
30 Sales Chicago 7844 TURNER SALESMAN 7698 1995-06-04 1500.00 0.00 30
40 Operations Boston 7844 TURNER SALESMAN 7698 1995-06-04 1500.00 0.00 30
10 Accounting New York 7876 ADAMS CLERK 7788 1999-06-04 1100.00 20
20 Research Dallas 7876 ADAMS CLERK 7788 1999-06-04 1100.00 20
30 Sales Chicago 7876 ADAMS CLERK 7788 1999-06-04 1100.00 20
40 Operations Boston 7876 ADAMS CLERK 7788 1999-06-04 1100.00 20
10 Accounting New York 7900 JAMES CLERK 7698 2000-06-23 950.00 30
20 Research Dallas 7900 JAMES CLERK 7698 2000-06-23 950.00 30
30 Sales Chicago 7900 JAMES CLERK 7698 2000-06-23 950.00 30
40 Operations Boston 7900 JAMES CLERK 7698 2000-06-23 950.00 30
10 Accounting New York 7902 FORD ANALYST 7566 1997-12-05 3000.00 20
20 Research Dallas 7902 FORD ANALYST 7566 1997-12-05 3000.00 20
30 Sales Chicago 7902 FORD ANALYST 7566 1997-12-05 3000.00 20
40 Operations Boston 7902 FORD ANALYST 7566 1997-12-05 3000.00 20
10 Accounting New York 7934 MILLER CLERK 7782 2000-01-21 1300.00 10
20 Research Dallas 7934 MILLER CLERK 7782 2000-01-21 1300.00 10
30 Sales Chicago 7934 MILLER CLERK 7782 2000-01-21 1300.00 10
40 Operations Boston 7934 MILLER CLERK 7782 2000-01-21 1300.00 10

Discussion

However, this combines every row on the dept table with every row on the emp table i.e. 4 * 14 = 56 rows. As you can see this is usually going to be useless. Only certain row combinations are interesting. Which rows do you think are meaningful? Identify some.

Progress

Proceed to Next step