Simplifying Joins with the USING Keyword : USING « Table Joins « Oracle PL/SQL Tutorial (original) (raw)

1. Oracle PL/SQL Tutorial
2. Table Joins
3. USING

SQL/92 allows you to further simplify the join condition through the USING clause, but only when your query has the following limitations:

Your query must use an equijoin.

The columns in your equijoin have the same name.

Don't use a table name or alias when referencing columns used in a USING clause.

SQL> -- create demo table SQL> create table Employee( 2 EMPNO NUMBER(3), 3 ENAME VARCHAR2(15 BYTE), 4 HIREDATE DATE, 5 ORIG_SALARY NUMBER(6), 6 CURR_SALARY NUMBER(6), 7 REGION VARCHAR2(1 BYTE), 8 MANAGER_ID NUMBER(3) 9 ) 10 /

Table created.

SQL> SQL> create table job ( 2 EMPNO NUMBER(3), 3 jobtitle VARCHAR2(20 BYTE) 4 ) 5 /

Table created.

SQL> SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (4,'COder');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (5,'Director');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (6,'Mediator');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (7,'Proffessor');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (8,'Programmer');

1 row created.

SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');

1 row created.

SQL> SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID) 2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E', 2) 3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID) 2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W', 3) 3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID) 2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E', 3) 3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID) 2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W', 4) 3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID) 2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E', 4) 3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID) 2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W', 5) 3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID) 2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E', 6) 3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION) 2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W') 3 /

1 row created.

SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION) 2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E') 3 /

1 row created.

SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee 2 /

 EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R MANAGER_ID

     1 Jason           25-JUL-96        1234        8767 E          2
     2 John            15-JUL-97        2341        3456 W          3
     3 Joe             25-JAN-86        4321        5654 E          3
     4 Tom             13-SEP-06        2413        6787 W          4
     5 Jane            17-APR-05        7654        4345 E          4
     6 James           18-JUL-04        5679        6546 W          5
     7 Jodd            20-JUL-03        5438        7658 E          6
     8 Joke            01-JAN-02        8765        4543 W
     9 Jack            29-AUG-01        7896        1232 E

9 rows selected.

SQL> select * from job 2 /

 EMPNO JOBTITLE

     1 Tester
     2 Accountant
     3 Developer
     4 COder
     5 Director
     6 Mediator
     7 Proffessor
     8 Programmer
     9 Developer

9 rows selected.

SQL> SQL> SELECT e.ename, j.jobtitle FROM employee e INNER JOIN job j USING (empno);

ENAME JOBTITLE

Jason Tester John Accountant Joe Developer Tom COder Jane Director James Mediator Jodd Proffessor Joke Programmer Jack Developer

9 rows selected.

SQL> SQL> SQL> -- clean the table SQL> drop table Employee 2 /

Table dropped.

SQL> drop table job 2 /

Table dropped.

SQL>