5. Multiple tables: Step 8

Demo

Table aliases - 1.

Suppose we wanted to find the name of an employee's manager. Look at the data in the emp table and identify the employee number of SMITH's manage in the MGR field. Now look that number up as an EMPNO in the table again to find SMITH's managers name ,

We can do it easily just one employee at a time, but we want to do it quickly for all of them, so we need to formulate this match using SELECT. There's a bit of a trick here. Take two 'copies' of the table [actually no copying is done, its just convenient to look at it this way]. We have to give them each an 'alias' so that we can refer to fields in either table..Now we can combine each row of one copy with each row of the other.

The rows will be limited to the first 20

SQL

SELECT * FROM emp e, emp m LIMIT 20;

Output

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

Discussion

How many rows would have been produced without the LIMIT clause?

Progress

Proceed to Next step