|
|
When you join tables together, you're associating rows from one table with rows from another and extracting data from the combinations. Depending on the join condition you use, you may end up with every row from both tables being matched with a row from the other table, but you might not.
In an inner join, the data returned from a query consists of data from each matched combination—no unmatched rows are considered. This is the type of join you've been using in the examples in this chapter so far, and as you know, it was necessary to explicitly mark some of these joins as inner joins for MySQL and Access. Using JOIN on those platforms that support it implicitly performs an INNER JOIN.
Let's look at how inner joins work in a bit more detail: In order to illustrate the various types of join and how they work, consider two simple tables, each with four rows comprising one column and as follows:
Let's perform an inner join between the two tables, as follows:
SELECT Table1.Column1, Table2.Column2 FROM Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.Column2;
Figure 7-1 shows the rows that are returned.
A row is only returned if a particular column value appears in both Table1 and Table2. In this case, the results will be as follows:
To perform an INNER JOIN on the Room and Class tables, you use code similar to the following:
SELECT Room.RoomID, Class.Time FROM Room INNER JOIN Class ON Room.RoomID = Class.RoomID ORDER BY Room.RoomID;
This will yield the following results (presented ordered by RoomID):
RoomID Time ------- --------------------------------- 2 Tue 10:00-12:00, Thu 14:00-15:00 2 Tue 16:00-17:00 Thu 15:00-17:00 3 Wed 09:00-11:00 3 Mon 14:00-16:00 5 Mon 11:00-12:00, Thu 09:00-11:00 5 Fri 11:00-13:00 6 Mon 09:00-11:00 8 Wed 11:00-13:00 Fri 09:00-11:00 9 Tue 14:00-16:00 9 Fri 14:00-16:00
Because no classes are scheduled for room 1, you don't see any entries for room 1 in this list.
Outer joins, on the other hand, consider some of the table rows from source tables that aren't matched up. There are three types of outer join: left, right, and full, where all rows are included from the left table, the right table, and both tables, respectively.
You perform outer joins using a similar syntax to inner joins, using LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN clauses. All of these require an ON clause for joining:
SELECT * FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Column1 = Table2.Column2 SELECT * FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.Column1 = Table2.Column2 SELECT * FROM Table1 FULL OUTER JOIN Table2 ON Table1.Column1 = Table2.Column2
Let's see these in action.
Using the small test tables, you can perform a left outer join as described previously. Figure 7-2 shows the rows returned for this join.
Notice that in this case a row is returned from Table1, even if no corresponding value exists in Table2. So, the results would be as follows:
COLUMN1 COLUMN2 ----- ------------------ 1 2 2 3 4 4 4 4
You can perform a left outer join on the classes and room example using the following:
SELECT Room.RoomID, Class.Time FROM Room LEFT OUTER JOIN Class ON Room.RoomID = Class.RoomID ORDER BY Room.RoomID;
This yields the following results:
RoomID Time ------- --------------------------------- 1 2 Tue 10:00-12:00, Thu 14:00-15:00 2 Tue 16:00-17:00 Thu 15:00-17:00 3 Mon 14:00-16:00 3 Wed 09:00-11:00 4 5 Mon 11:00-12:00, Thu 09:00-11:00 5 Fri 11:00-13:00 6 Mon 09:00-11:00 7 8 Wed 11:00-13:00 Fri 09:00-11:00 9 Tue 14:00-16:00 9 Fri 14:00-16:00
Notice that rooms 1, 4, and 7, currently unused in the database, now appear in the list of rooms.
Figure 7-3 shows a right outer join.
This is basically the opposite of a right outer join. A row is returned from Table2, even if no corresponding value exists in Table1. So, the results would be as follows:
COLUMN1 COLUMN2
---------- ----------
2 2
4 4
4 4
5
Performing this join on the room and class example will yield the same results as an inner join because you don't have any null fields in the Class table, but the syntax you'd use is as follows:
SELECT Room.RoomID, Class.Time FROM Room RIGHT OUTER JOIN Class ON Room.RoomID = Class.RoomID ORDER BY Room.RoomID;
However, switching the SQL around (effectively inverting the example), you can produce the same results as the left inner join:
SELECT Room.RoomID, Class.Time FROM Class RIGHT OUTER JOIN Room ON Class.RoomID = Room.RoomID ORDER BY Room.RoomID;
Finally, Figure 7-4 shows the result of a full outer join.
In the test case, the results are as follows:
COLUMN1 COLUMN2
----- ------------------
1
2 2
3
4 4
4 4
5
Using the room and class example, you see the same set of results as produced when using a LEFT OUTER JOIN:
SELECT Room.RoomID, Class.Time FROM Room FULL OUTER JOIN Class ON Room.RoomID = Class.RoomID ORDER BY Room.RoomID;
Note that MySQL and Access don't support the FULL OUTER JOIN syntax.
Some RDBMSs still use an older method for performing outer joins, where operators such as + were used to specify the different types of join. However, this is a most confusing way of doing things when compared to this more explicit specification using keywords. Although in most cases RDBMSs have been upgraded to use the new syntax, it's worth looking at the operators here because you may well come across them.
You might see the operators *=, =*, or *=* being used in a WHERE clause as follows:
SELECT * FROM Table1, Table2 WHERE Table1.Column1 *= Table2.Column2 SELECT * FROM Table1, Table2 WHERE Table1.Column1 =* Table2.Column2 SELECT * FROM Table1, Table2 WHERE Table1.Column1 *=* Table2.Column2
These stand for left outer join, right outer join, and full outer join, respectively. Alternatively, when using Oracle, you might see a + operator being used in a similar way:
This syntax was used for Oracle versions prior to 9i. In the previous case, a null value would be returned for Table1 if there isn't any value in Column1 that corresponds to a value in Column2 of Table2. So, this is equivalent to a right outer join. You achieve a left outer join as follows:
SELECT * FROM Table1, Table2 WHERE Table1.Column1 = Table2.Column2(+)
As a final note before you move on to a proper example, note that you can include multiple tables in outer joins just as with inner joins. As you saw earlier with inner joins, you simply add more JOIN and ON clauses after specifying the first join. You can even mix types of join in a single query—although things quickly become complicated if you do!
Let's investigate the use of each join type in a full working example.
 |
In this example, you're extending the classes and rooms example, looking at matches between rows in the Class table and rows in the Room table where values in the RoomID columns match. This would be useful because just knowing the ID of a room isn't quite as useful as knowing the name of a room; unless you have a list to match the IDs against, you'll end up with the professor teaching himself.
Execute the following against the InstantUniversity database:
SELECT Class.ClassID,
Class.CourseID,
Class.Time,
Room.Comments AS RoomName
FROM Class INNER JOIN Room
ON Class.RoomID = Room.RoomID
ORDER BY ClassID;
This is the inner join example, and it produces the following:
ClassID CourseID Time RoomName ------- -------- --------------------------------- ------------------ 1 1 Mon 09:00-11:00 Languages Room 2 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 5 5 Tue 14:00-16:00 Engineering Room 2 6 6 Tue 16:00-17:00, Thu 15:00-17:00 Science Department 7 7 Wed 09:00-11:00 Science Room 1 8 8 Wed 11:00-13:00, Fri 09:00-11:00 Engineering Room 1 9 9 Fri 11:00-13:00 Languages Room 1 10 10 Fri 14:00-16:00 Engineering Room 2
Running this example results in a set of data that only includes those row combinations where matches are found. This gives you the name of the room for each class in the Class table.
Now enter and execute the following:
SELECT Class.ClassID,
Class.CourseID,
Class.Time,
Room.Comments AS RoomName
FROM Class RIGHT OUTER JOIN Room
ON Class.RoomID = Room.RoomID
ORDER BY ClassID;
The results are as follows:
ClassID CourseID Time RoomName ------- -------- --------------------------------- ----------------- NULL NULL NULL Main hall NULL NULL NULL Languages Block NULL NULL NULL Engineering Center 1 1 Mon 09:00-11:00 Languages Room 2 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 5 5 Tue 14:00-16:00 Engineering Room 2 6 6 Tue 16:00-17:00, Thu 15:00-17:00 Science Department 7 7 Wed 09:00-11:00 Science Room 1 8 8 Wed 11:00-13:00, Fri 09:00-11:00 Engineering Room 1 9 9 Fri 11:00-13:00 Languages Room 1 10 10 Fri 14:00-16:00 Engineering Room 2
This time around, you're including all rows in the second table, which is Room. This shows you explicitly which rooms aren't used for classes because you can see NULL values in the first three columns of the results for those rooms. At the same time, the results are including information that you wouldn't otherwise have obtained, namely a complete list of rooms available, not just those where classes are taking place.
|
You could also perform a left outer join as follows:
SELECT Class.ClassID,
Class.CourseID,
Class.Time,
Room.Comments AS RoomName
FROM Class LEFT OUTER JOIN Room
ON Class.RoomID = Room.RoomID
ORDER BY ClassID
However, this would give you the same result as the inner join because all RoomID values in the Class table have corresponding values in the Room table. If you were to add a record to the Class table that had a different RoomID value—10, for example—then as well as the results that are the same as those for an inner join, you would see a result as follows:
ClassID CourseID Time RoomName ------- -------- --------------------------------- ----------------- 11 3 Wed 14:00-16:00 NULL
You'd see all the data from the Class table columns, but because there's no corresponding row in the Room table, you get a NULL value for RoomName.
You could also have performed a full outer join, but with the default data this would give the same result as the right outer joins example. If you add a record as shown previously for a left outer join, though, then it too would be included. This would make the result for a full outer join as follows:
ClassID CourseID Time RoomName ------- -------- --------------------------------- ----------------- NULL NULL NULL Main hall NULL NULL NULL Languages Block NULL NULL NULL Engineering Center 1 1 Mon 09:00-11:00 Languages Room 2 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 5 5 Tue 14:00-16:00 Engineering Room 2 6 6 Tue 16:00-17:00, Thu 15:00-17:00 Science Department 7 7 Wed 09:00-11:00 Science Room 1 8 8 Wed 11:00-13:00, Fri 09:00-11:00 Engineering Room 1 9 9 Fri 11:00-13:00 Languages Room 1 10 10 Fri 14:00-16:00 Engineering Room 2 11 3 Wed 14:00-16:00 NULL
As you can see, although outer joins can be a little difficult to understand at first, they provide interesting possibilities.
|
|