JOIN
in Oracle:
If two tables have a named field in common than the natural join will create a link between these two tables.
SQL> select * from emp;
EMPNO
ENAME JOB MGR HIREDATE
SAL COMM DEPTNO
7369
SMITH CLERK 7902
17-DEC-80 800 20
7499
ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521
WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566
JONES MANAGER 7839 02-APR-81
2975 20
7654
MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698
BLAKE MANAGER 7839 01-MAY-81 2850
30
7782
CLARK MANAGER 7839 09-JUN-81 2450 10
7788
SCOTT ANALYST 7566 19-APR-87 3000
20
7839
KING PRESIDENT 17-NOV-81 5000
10
7844
TURNER SALESMAN 7698 08-SEP-81 1500
0 30
7876
ADAMS CLERK 7788 23-MAY-87 1100
20
7900
JAMES CLERK 7698 03-DEC-81 950
30
7902
FORD ANALYST 7566 03-DEC-81 3000
20
7934
MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from dept;
DEPTNO
DNAME LOC
---------- -------------- -------------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
Natural Join:
Now I am going to code the natural join. Note that I list the fields that I want in the query but I do associate the table name with the field they have in common, deptno. Rather than use a where clause, the natural join clause is used in the from.
SQL> select empno, ename, deptno, dname
2 from emp natural join dept;
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369
SMITH 20 RESEARCH
7499
ALLEN 30 SALES
7521
WARD 30 SALES
7566
JONES 20 RESEARCH
7654
MARTIN 30 SALES
7698
BLAKE 30 SALES
7782
CLARK 10 ACCOUNTING
7788
SCOTT 20 RESEARCH
7839
KING 10 ACCOUNTING
7844
TURNER 30 SALES
7876
ADAMS 20 RESEARCH
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7900
JAMES 30 SALES
7902
FORD 20 RESEARCH
7934
MILLER 10 ACCOUNTING
14 rows selected.
When I tried it with the table name.field name, I got an error saying that I cannot have qualifiers on a column being used in a natural join. As we have note before, the deptno field/column in the emp table will be equal to the deptno field/column in the dept table by defination when they are linked so there is no need for the qualifier.
SQL> select empno, ename, emp.deptno,
dept.deptno, dname
2 from emp natural join dept;
select empno, ename, emp.deptno, dept.deptno, dname
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have
qualifier
JOIN:
I can also use the JOIN with the accompany USING to join the two tables together. The USING specifies the field that is to be used to link the two tables.
SQL> select empno, ename, deptno, dname
2 from emp join dept
3 using (deptno);
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369
SMITH 20 RESEARCH
7499
ALLEN 30 SALES
7521
WARD 30 SALES
7566
JONES 20 RESEARCH
7654
MARTIN 30 SALES
7698
BLAKE 30 SALES
7782
CLARK 10 ACCOUNTING
7788
SCOTT 20 RESEARCH
7839
KING 10 ACCOUNTING
7844
TURNER 30 SALES
7876
ADAMS 20 RESEARCH
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7900
JAMES 30 SALES
7902
FORD 20 RESEARCH
7934
MILLER 10 ACCOUNTING
14 rows selected.
If the two tables do not have a name in common then you can use the ON clause with the join to specify the relationship to be used in the join. For example if on one table I have used deptnum and on the other table I had used deptno I could make the join as shown.
SQL> select empno, ename, emp.deptno, dname
2 from emp join dept
3 on emp.deptno = dept.deptnum;
Since on these tables, the names are the same, I will illustrate using this example:
SQL> select empno, ename, emp.deptno, dname
2 from emp join dept
3 on emp.deptno = dept.deptno;
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369
SMITH 20 RESEARCH
7499
ALLEN 30 SALES
7521
WARD 30 SALES
7566
JONES 20 RESEARCH
7654
MARTIN 30 SALES
7698
BLAKE 30 SALES
7782
CLARK 10 ACCOUNTING
7788
SCOTT 20 RESEARCH
7839
KING 10 ACCOUNTING
7844
TURNER 30 SALES
7876
ADAMS 20 RESEARCH
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7900
JAMES 30 SALES
7902
FORD 20 RESEARCH
7934
MILLER 10 ACCOUNTING
14 rows selected.
Outer join:
I am now going to create a new table and add another dept to it. Actually I did not have to do this because looking at emp there were no records with deptno sales. Well, it gave me an opportunity to show you how to create a table from an existing table.
SQL> create table deptplus as select * from dept;
Table created.
SQL> select * from deptplus;
DEPTNO
DNAME LOC
---------- -------------- -------------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
SQL> insert into deptplus
2 values(50, 'IT', 'BOSTON');
1 row created.
SQL> select * from deptplus;
DEPTNO
DNAME LOC
---------- -------------- -------------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
50
IT BOSTON
I am now going to look at an outer join using these tables: emp and deptplus. Note that the table listed to the right is the table that will show the unmatched items.
SQL> select empno, ename, emp.deptno, dname
2 from emp right outer join deptplus
3 on emp.deptno = deptplus.deptno;
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369
SMITH 20 RESEARCH
7499
ALLEN 30 SALES
7521
WARD 30 SALES
7566
JONES 20 RESEARCH
7654
MARTIN 30 SALES
7698 BLAKE 30 SALES
7782
CLARK 10 ACCOUNTING
7788
SCOTT 20 RESEARCH
7839
KING 10 ACCOUNTING
7844
TURNER 30 SALES
7876
ADAMS 20 RESEARCH
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7900
JAMES 30 SALES
7902
FORD 20 RESEARCH
7934
MILLER 10 ACCOUNTING
IT
OPERATIONS
16 rows selected.
When I change the word right to left, I will see different results. The two departments that do not have any employees in them will not be listed.
SQL> select empno, ename, emp.deptno, dname
2 from emp left outer join deptplus
3 on emp.deptno = deptplus.deptno;
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7934
MILLER 10 ACCOUNTING
7839
KING 10 ACCOUNTING
7782
CLARK 10 ACCOUNTING
7902
FORD 20 RESEARCH
7876
ADAMS 20 RESEARCH
7788
SCOTT 20 RESEARCH
7566
JONES 20 RESEARCH
7369
SMITH 20 RESEARCH
7900
JAMES 30 SALES
7844
TURNER 30 SALES
7698
BLAKE 30 SALES
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7654
MARTIN 30 SALES
7521
WARD 30 SALES
7499 ALLEN 30 SALES
14 rows selected.
I am now creating a copy of the emp table called empplus and adding a record with deptno 60. Note that deptno 60 does not exist in the dept table. I will then run a left join to show John Doe and dept 60 with no deptno.
SQL> create table empplus as select * from emp;
SQL> insert into empplus
2 values (8888,'John
Doe','ANALYST',7788,'10-NOV-88',2900,100,60);
1 row created.
SQL>
select empno, ename, empplus.deptno, dname
2 from empplus left outer join deptplus
3 on empplus.deptno = deptplus.deptno;
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7934
MILLER 10 ACCOUNTING
7839
KING 10 ACCOUNTING
7782
CLARK 10 ACCOUNTING
7902
FORD 20 RESEARCH
7876
ADAMS 20 RESEARCH
7788
SCOTT 20 RESEARCH
7566
JONES 20 RESEARCH
7369
SMITH 20 RESEARCH
7900
JAMES 30 SALES
7844
TURNER 30 SALES
7698
BLAKE 30 SALES
EMPNO
ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7654
MARTIN 30 SALES
7521
WARD 30 SALES
7499
ALLEN 30 SALES
8888
John Doe 60
15 rows selected.
Non-equality or non-equi joins:
Now I am going to run a non-equality join using the salgrade table and come up with the grade for each employee using a non-equality join.
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select empno, ename, sal, grade, losal,
hisal
2 from emp join salgrade
3 on sal between losal and hisal;
EMPNO
ENAME SAL GRADE LOSAL HISAL
---------- ---------- ---------- ----------
---------- ----------
7369
SMITH 800 1 700 1200
7876
ADAMS 1100 1 700 1200
7900
JAMES 950 1 700 1200
7521
WARD 1250 2
1201 1400
7654
MARTIN 1250 2 1201 1400
7934
MILLER 1300 2 1201 1400
7499
ALLEN 1600 3 1401 2000
7844
TURNER 1500 3 1401 2000
7566
JONES 2975 4 2001 3000
7698
BLAKE 2850 4 2001 3000
7782
CLARK 2450 4 2001 3000
EMPNO
ENAME SAL
GRADE LOSAL HISAL
---------- ---------- ---------- ----------
---------- ----------
7788
SCOTT 3000 4 2001 3000
7902
FORD 3000 4 2001 3000
7839
KING 5000 5 3001 9999
14 rows selected.
Note: using the where clause this would be written:
SQL> select empno, ename, sal, grade, losal,
hisal
2 from emp, salgrade
3 where sal between losal and hisal;
EMPNO
ENAME SAL GRADE LOSAL HISAL
---------- ---------- ---------- ----------
---------- ----------
7369
SMITH 800 1 700 1200
7876
ADAMS 1100 1 700 1200
7900 JAMES 950 1 700 1200
7521
WARD 1250 2 1201 1400
7654
MARTIN 1250 2 1201 1400
7934
MILLER 1300 2 1201 1400
7499 ALLEN 1600
3 1401 2000
7844
TURNER 1500 3 1401 2000
7566
JONES 2975 4 2001 3000
7698
BLAKE 2850 4 2001 3000
7782
CLARK 2450 4 2001 3000
EMPNO
ENAME SAL GRADE LOSAL HISAL
---------- ---------- ---------- ----------
---------- ----------
7788
SCOTT 3000 4 2001 3000
7902
FORD 3000 4 2001 3000
7839
KING 5000 5 3001 9999
14 rows selected.
Self-join:
Finally we will look at self-joins. As you can tell from the data, the mgr field uses the empno to designate the manager. Therefore, we can do a self join using these two fields. We are connecting the manager of the employee back to the employee number to get the name of the manager for each employee. Note that there are only 13 records instead of the usually 14. King does not have a manager and since we are only looking for employees with managers, he is excluded.
SQL> desc emp;
Name
Null? Type
----------------------------------------- -------- -------------
EMPNO NOT NULL
NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select e.empno, e.ename, e.mgr, m.empno,
m.ename
2 from emp e join emp m
3 on e.mgr = m.empno;
EMPNO
ENAME MGR EMPNO ENAME
---------- ---------- ---------- ----------
----------
7369
SMITH 7902 7902 FORD
7499
ALLEN 7698 7698 BLAKE
7521
WARD 7698 7698 BLAKE
7566
JONES 7839 7839 KING
7654
MARTIN 7698 7698 BLAKE
7698
BLAKE 7839 7839 KING
7782
CLARK 7839 7839 KING
7788
SCOTT 7566 7566 JONES
7844
TURNER 7698 7698 BLAKE
7876
ADAMS 7788 7788 SCOTT
7900
JAMES 7698 7698 BLAKE
EMPNO
ENAME MGR EMPNO ENAME
---------- ---------- ---------- ----------
----------
7902
FORD 7566 7566 JONES
7934
MILLER 7782 7782 CLARK
13 rows selected.
Now I am going to look at joining three tables. First I will do the two joins separately and then I will put them together. The end result will link emp and dept based on deptno and emp to salgrade based on a non-equality join. Note they are not in the same order to get the desired order you need to use an order by clause.
SQL> select empno, ename, dname
2 from emp join dept on emp.deptno =
dept.deptno;
EMPNO
ENAME DNAME
---------- ---------- --------------
7369
SMITH RESEARCH
7499
ALLEN SALES
7521 WARD SALES
7566
JONES RESEARCH
7654
MARTIN SALES
7698
BLAKE SALES
7782
CLARK ACCOUNTING
7788
SCOTT RESEARCH
7839
KING ACCOUNTING
7844
TURNER SALES
7876
ADAMS RESEARCH
EMPNO
ENAME DNAME
---------- ---------- --------------
7900
JAMES SALES
7902
FORD RESEARCH
7934
MILLER ACCOUNTING
14 rows selected.
SQL> select empno, ename, grade
2 from emp join salgrade on emp.sal between
salgrade.losal and salgrade.hisal;
EMPNO
ENAME GRADE
---------- ---------- ----------
7369
SMITH 1
7876
ADAMS 1
7900
JAMES 1
7521
WARD 2
7654
MARTIN 2
7934
MILLER 2
7499
ALLEN 3
7844
TURNER 3
7566
JONES 4
7698
BLAKE 4
7782
CLARK 4
EMPNO
ENAME GRADE
---------- ---------- ----------
7788
SCOTT 4
7902
FORD 4
7839
KING 5
14 rows selected.
SQL> select empno, ename, dname, grade
2 from emp join dept on emp.deptno =
dept.deptno
3 join salgrade on emp.sal between
salgrade.losal and salgrade.hisal;
EMPNO
ENAME DNAME GRADE
---------- ---------- -------------- ----------
7369
SMITH RESEARCH 1
7876
ADAMS RESEARCH 1
7900 JAMES SALES
1
7521
WARD SALES 2
7654
MARTIN SALES 2
7934
MILLER ACCOUNTING 2
7499
ALLEN SALES 3
7844
TURNER SALES 3
7566
JONES RESEARCH 4
7698
BLAKE SALES 4
7782
CLARK ACCOUNTING 4
EMPNO
ENAME DNAME GRADE
---------- ---------- -------------- ----------
7788
SCOTT RESEARCH 4
7902
FORD RESEARCH 4
7839
KING ACCOUNTING 5
14 rows selected.