Oracle PL/SQL Tutorial/Table Joins/Non equality table join — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 10:03, 26 мая 2010

Example equijoins.

SQL>
SQL> SELECT a.table_name,b.column_name
  2      FROM dba_tables a, dba_tab_columns b
  3      WHERE a.table_name = b.table_name
  4      and  rownum < 50
  5      AND a.tablespace_name = "GRAPHICS_DATA";
no rows selected
SQL>


Use <> in non-equality 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>


Use > in non-equality table join

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  )
  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.
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/Table_Joins/Non_equality_table_join&oldid=2770»