Exploring Additional Joins in Oracle

In addition to the joins you've just been looking at, Oracle 9i offers a few additional options, which you'll take a quick look at now.

Using Natural Joins

A natural join performs a join for all columns with matching values in two tables. To put this in context, you could rewrite an earlier example from this:

SELECT Class.ClassID,
        Class.CourseID,
        Class.Time,
        Room.Comments AS RoomName
FROM Class INNER JOIN Room
ON Class.RoomID = Room.RoomID
ORDER BY ClassID;

to this:

SELECT Class.ClassID,
        Class.CourseID,
        Class.Time,
        Room.Comments AS RoomName
FROM Class NATURAL JOIN Room
ORDER BY ClassID;

Working with the using Clause

This clause is used if several columns share the same name. Again, you can modify a previous example to demonstrate this:

SELECT Room.Comments, Class.Time
FROM Room
   INNER JOIN Class
   ON Room.RoomID = Class.RoomID;

This query lists the name of each room and the times when it's in use:

   Comments                     Time

   ---------------------------  ---------------------------------
   Science Department           Tue 16:00-17:00 Thu 15:00-17:00
   Science Department           Tue 10:00-12:00, Thu 14:00-15:00
   Science Room 1               Wed 09:00-11:00
   Science Room 1               Mon 14:00-16:00
   Languages Room 1             Fri 11:00-13:00
   Languages Room 1             Mon 11:00-12:00, Thu 09:00-11:00
   Languages Room 2             Mon 09:00-11:00
   Engineering Room 1           Wed 11:00-13:00 Fri 09:00-11:00
   Engineering Room 2           Fri 14:00-16:00
   Engineering Room 2           Tue 14:00-16:00

Using the USING clause, you could write this as follows:

SELECT Room.Comments, Class.Time
FROM Class
   JOIN Room
   USING (RoomID);

This would produce the same results. Note that you can't use any further qualifiers, including WHERE or GROUP BY in this statement.