Right Outer Join(room vs class) : Right Outer Joins « Table Joins « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Table Joins
  3. Right Outer Joins

Right Outer Join(room vs class)

   
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 Room.RoomID, Class.Time
  2  FROM Class
  3     RIGHT OUTER JOIN Room
  4     ON Class.RoomID = Room.RoomID
  5  ORDER BY Room.RoomID;

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

5 rows selected.

SQL>
SQL> drop table Room;

Table dropped.

SQL> drop table Class;

Table dropped.








Related examples in the same category

1.right outer join between department table and employee table
2.Right outer joins:return all the rows from the table on the right instead of the table on the left
3.Group by and right join department id
4.Right Outer Join, room table and class table
5.Right join with where in clause