Use <> in non-equality join : Non equality table join « Table Joins « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Table Joins
3. Non equality table join

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  )
  9  /

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>
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (5,      'Jane',  to_date('20050417','YYYYMMDD'), 7654,              4345,         'E')
  3  /

1 row created.

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

1 row created.

SQL> insert into Employee(EMPNO,  EName,   HIREDATE,                       ORIG_SALARY,       CURR_SALARY,  REGION)
  2               values (7,      'Jodd',  to_date('20030720','YYYYMMDD'), 5438,              7658,         'E')
  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
---------- --------------- --------- ----------- ----------- -
         5 Jane            17-APR-05        7654        4345 E
         6 James           18-JUL-04        5679        6546 W
         7 Jodd            20-JUL-03        5438        7658 E
         8 Joke            01-JAN-02        8765        4543 W
         9 Jack            29-AUG-01        7896        1232 E

SQL> select * from job
  2  /

     EMPNO JOBTITLE
---------- --------------------
         1 Tester
         2 Accountant
         3 Developer
         4 COder

SQL>
SQL>
SQL> SELECT e.ename, j.jobtitle FROM employee e, job j
  2  WHERE e.empno <> j.empno;

ENAME           JOBTITLE
--------------- --------------------
Jane            Tester
James           Tester
Jodd            Tester
Joke            Tester
Jack            Tester
Jane            Accountant
James           Accountant
Jodd            Accountant
Joke            Accountant
Jack            Accountant
Jane            Developer
James           Developer
Jodd            Developer
Joke            Developer
Jack            Developer
Jane            COder
James           COder
Jodd            COder
Joke            COder
Jack            COder

20 rows selected.

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

Table dropped.

SQL> drop table job
  2  /

Table dropped.

SQL>