Database Reference
In-Depth Information
Figure 6.41
Use right outer join to select data from two tables.
To demonstrate the use of a right outer join, let us reverse the tables used in the example for
the left outer join. hat is, the table CLASS will be the left table and DAYS will be the right table.
he output is shown in Figure 6.41.
his time, the NULL values appear on the left-hand side.
In the above, we have used joins to link two tables. In fact, the join method can also be used
to join multiple tables. he topic of multiple joins will be covered next.
6.4.3 Multiple Joins
Multiple tables can be joined in the FROM clause. As an example, let us consider a query that
will retrieve information, including student names from the table STUDENT, the courses they
have taken, and their grades from each class. To get the student names and their grades, we need
to join the tables STUDENT and STUDENT_CLASS. To get the course names, we need to irst
join the table CLASS since CLASS contains both StudentID and CourseID. hen, join the table
COURSE. Since data are selected from four diferent tables, STUDENT, STUDENT_CLASS,
CLASS, and COURSE, we join all of them in a query as shown in Figure 6.42.
As you can see, the keyword JOIN is used three times to join the four tables. In the above
example, the join conditions are speciied in the FROM clause.
6.4.4 Self-Join
here are situations where you need to join a table to itself. For example, if you want to pair the
students who are taking the same class, you need to match a given student with another student
in the same class. Since both students are from the same table, you need to join the table to itself
and use the column ClassID as the common column for the join condition.
Since the tables to be joined are the same, you must distinguish their roles by giving the table
two diferent aliases in the FROM clause. In our example, the aliases C1 and C2 are used to rep-
resent the left table and right table, respectively. To be speciic, the join condition should look like
the one below:
C1.ClassID
=
C2.ClassID
