For inner joins (and outer joins), tables participating
in the join are listed in the FROM clause. In this case,
the Genre table and the Movie table are being joined.
In simple inner-join queries, the order in which you list
the tables doesn’t matter. For example, the code
SELECT g.Genre, m.Movie
FROM Genre g
INNER JOIN Movie m
ON g.GenreID = m.GenreID
ORDER BY g.Genre, m.Movie
would provide the same results. That’s not the case in
some other types of joins, as you’ll see shortly.
The ON keyword is used to indicate which columns should be matched together. In
this case, we’re matching the
GenreID columns from both
tables. Other column comparisons
can be added after the
ON keyword by using the AND
keyword.
Finally, the ORDER BY
clause is used to sort the results
alphabetically first by genre, then
by movie. Figure 2 shows the results.
Now suppose you want the result set to show only
those movies in the Sci-Fi genre, as Figure 3 shows.
This can be accomplished by using a WHERE clause
to add a filter:
SELECT g.Genre, m.Movie
FROM Movie m
INNER JOIN Genre g
ON g.GenreID = m.GenreID
WHERE g.Genre = ‘Sci-Fi’
ORDER BY m.Movie
As you can see, the INNER JOIN syntax clearly separates
the joining criterion from the filtering criterion. In
all five types of joins, the WHERE clause filters data
from the result set after the join has already taken place.
This is a key difference between the JOIN clauses used
in SQL Server 7.0 and later and the old join technique
used in SQL Server 6.5 and earlier. In the old join
technique, both the join and filter criteria are in the
WHERE clause.
The OUTER JOIN Clauses
There are three types of outer joins: left outer join,
right outer join, and full outer join. When considering
outer joins, it helps to think of two tables arranged
side by side. The table on the left is the first table in
the join. The table on the right is the second table in
the join. Left outer joins return all rows from the first
table and only rows from the second table that meet the
join criteria. Columns from the second table that don’t
match the criteria will contain NULL values. Right
outer joins return all rows from the second table and
only rows from the first table that meet the join criteria.
Columns in the first table that don’t match the criteria
contain NULL values. Full outer joins return all rows
from both tables. Whenever a row in one table has
no match in the other table, the columns will contain
NULL values.
Let’s perform a left outer join on the Genre and
Movie tables to see whether there are any genres that
don’t have a matching movie. In the following query,
Genre is the left table and Movie is the right table:
SELECT g.GenreID AS ‘Genre.GenreID’,
g.Genre, m.MovieID,
m.GenreID AS ‘Movie.GenreID’,
m.Movie
FROM Genre g
LEFT OUTER JOIN Movie m
ON m.GenreID = g.GenreID
ORDER BY g.Genre, m.Movie
As this code shows, the OUTER JOIN syntax is
similar to the INNER JOIN syntax, except that it
matters which table gets listed first in the FROM
clause. The Genre table must be listed first because it’s
the left table.
Figure 4 shows the results of this left outer join.
As you can see, the columns from the Genre table for
GenreID 7 contain actual data, whereas the columns
from the Movie table contain NULL values. Thus,
there is a GenreID 7 in the Genre table but not in the
Movie table.
Now let’s perform a right outer join on the Genre
and Movie tables to see whether there are any movies
that don’t have a matching genre. In this query, Genre
is still the left table and Movie is still the right table:
SELECT g.GenreID AS ‘Genre.GenreID’,
g.Genre, m.MovieID,
m.GenreID AS ‘Movie.GenreID’,
m.Movie
FROM Genre g
RIGHT OUTER JOIN Movie m ON m.GenreID = g.GenreID
ORDER BY g.Genre, m.Movie
As Figure 5, page 36, shows, in the first row, the Genre
table’s columns contain NULL values, whereas the
Movie table’s columns contain data. So, there is a GenreID
10 in the Movie table but not in the Genre table.
Finally, let’s perform a full outer join on these tables
to identify records in each table that have no corresponding
record in the other table. When performing a
full outer join, the concept of left or right table doesn’t
apply, so the table order doesn’t matter. The query
SELECT g.GenreID AS ‘Genre.GenreID’,
g.Genre, m.MovieID,
m.GenreID AS ‘Movie.GenreID’,
m.Movie
FROM Genre g
FULL OUTER JOIN Movie m
ON m.GenreID = g.GenreIDORDER BY g.Genre, m.Movie
produces the results in Figure 6. If you look at the
results closely, you’ll see all the movies with matching
genres, the genres with no matching movies, and
the movies with no matching genres. In essence, the full outer join gives you the results of an inner join,
left outer join, and right outer join in one handy
package.
Continue on Page 3
Prev. page
1
[2]
3
next page 
