Oracle PL/SQL Tutorial/Table Joins/SQL 92 Syntax

Performing Inner Joins on Multiple Columns Using SQL/92

If your join uses more than one column from the two tables, you provide those columns in your ON clause along with the AND operator.

SELECT ...
FROM table1 INNER JOIN table2
ON table1.column1 = table2.column1
AND table1.column2 = table2.column2;

Performing Inner Joins on Two Tables Using SQL/92, on clause

SQL>
SQL>
SQL>
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 ON e.empno = j.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> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL> drop table job
  2  /
Table dropped.
SQL>
SQL>

Performing Joins Using the SQL/92 Syntax

With the introduction of Oracle9i, the database also implements the ANSI SQL/92 standard syntax for joins.

To make your SQL fully compliant with the new standard, you should use SQL/92 in your queries.

7. 9. SQL 92 Syntax 7. 9. 1. Performing Joins Using the SQL/92 Syntax 7. 9. 2. <A href="/Tutorial/Oracle/0140__Table-Joins/PerformingInnerJoinsonMultipleColumnsUsingSQL92.htm">Performing Inner Joins on Multiple Columns Using SQL/92</a> 7. 9. 3. <A href="/Tutorial/Oracle/0140__Table-Joins/PerformingInnerJoinsonTwoTablesUsingSQL92onclause.htm">Performing Inner Joins on Two Tables Using SQL/92, on clause</a>