We can combine two or more tables by listing them in the FROM clause:
SELECT * FROM dept, emp;
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 |
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.