Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Table Joins

 

Use in 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> -- 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> -- 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> 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> -- clean the table SQL> drop table Employee   2  / Table dropped. SQL> drop table job   2  / Table dropped.