JOIN in Oracle:

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

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.