Oracle PL/SQL Tutorial/Table Joins/Inner Joins

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

Inner and Outer Joins

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE Room (

 2     RoomID   INT NOT NULL PRIMARY KEY,
 3     Comments VARCHAR(50),
 4     Capacity INT);

Table created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,"Languages Room 1",75); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE Class (

 2     ClassID     INT NOT NULL PRIMARY KEY,
 3     CourseID    INT NOT NULL,
 4     InstructorID INT NOT NULL,
 5     RoomID      INT NOT NULL,
 6     Time        VARCHAR(50));

Table created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (5,5,2,9,"Tue 14:00-16:00"); 1 row created. SQL> SQL> SQL> SQL> SELECT Class.ClassID,

 2         Class.CourseID,
 3         Class.Time,
 4         Room.ruments AS RoomName
 5  FROM Class INNER JOIN Room
 6  ON Class.RoomID = Room.RoomID
 7  ORDER BY ClassID;
  CLASSID   COURSEID TIME

---------- --------------------------------------------------

ROOMNAME


        2          2 Mon 11:00-12:00, Thu 09:00-11:00

Languages Room 1

        3          3 Mon 14:00-16:00

Science Room 1

        4          4 Tue 10:00-12:00, Thu 14:00-15:00

Science Department

3 rows selected. SQL> SQL> SQL> drop table Room; Table dropped. SQL> drop table Class; Table dropped. SQL></source>


Inner and Outer Joins(room and class)

   <source lang="sql">

SQL> SQL> CREATE TABLE Room (

 2     RoomID   INT NOT NULL PRIMARY KEY,
 3     Comments VARCHAR(50),
 4     Capacity INT);

Table created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE Class (

 2     ClassID     INT NOT NULL PRIMARY KEY,
 3     CourseID    INT NOT NULL,
 4     InstructorID INT NOT NULL,
 5     RoomID      INT NOT NULL,
 6     Time        VARCHAR(50));

Table created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (5,5,2,9,"Tue 14:00-16:00"); 1 row created. SQL> SQL> SELECT Class.ClassID,

 2         Class.CourseID,
 3         Class.Time,
 4         Room.ruments AS RoomName
 5  FROM Class LEFT OUTER JOIN Room
 6  ON Class.RoomID = Room.RoomID
 7  ORDER BY ClassID
 8

SQL> SQL> drop table class; Table dropped. SQL> drop table room; Table dropped. SQL></source>


Performing Inner Joins on Two Tables Using SQL/92

   <source lang="sql">

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></source>


Using Inner Joins

   <source lang="sql">

SQL> CREATE TABLE Room (

 2     RoomID   INT NOT NULL PRIMARY KEY,
 3     Comments VARCHAR(50),
 4     Capacity INT);

Table created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,"Languages Room 1",75); 1 row created. SQL> SQL> SQL> SQL> CREATE TABLE Class (

 2     ClassID     INT NOT NULL PRIMARY KEY,
 3     CourseID    INT NOT NULL,
 4     InstructorID INT NOT NULL,
 5     RoomID      INT NOT NULL,
 6     Time        VARCHAR(50));

Table created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00"); 1 row created. SQL> SQL> SELECT Room.RoomID, Class.Time

 2  FROM Room
 3     INNER JOIN Class
 4     ON Room.RoomID = Class.RoomID
 5  ORDER BY Room.RoomID;
   ROOMID TIME

--------------------------------------------------
        2 Tue 10:00-12:00, Thu 14:00-15:00
        3 Mon 14:00-16:00
        5 Mon 11:00-12:00, Thu 09:00-11:00

3 rows selected. SQL> SQL> SQL> drop table room; Table dropped. SQL> drop table class; Table dropped. SQL> SQL></source>