Full outer joins are especially useful for tracking
down data integrity problems. For example, from the results in Figure 6, you can
see that “To Kill a Mocking
Nerd” in the Movie table has
an invalid GenreID. If you
change this movie’s Genre-
ID from 10 to 7 in the Movie
table and rerun the full outer
join query, this data anomoly
disappears.
The CROSS JOIN Clause
A cross join produces a Cartesian product of two tables,
which means that every row in the first table is joined to
every row in the second table. You can read more about
Cartesian products (and the mathematics behind them)
at en.wikipedia.org/wiki/Cartesian_product.
Because of the exponential nature of the results, the
cross join is commonly used to generate large volumes of
test data from relatively small
tables. For example, the Genre
table has only 7 rows and the
Movie table has only 13 rows,
but an unfiltered cross join
would produce a result set
containing 91 rows.
The cross join is also
commonly used because it’s
easy to implement. You don’t
need to include the ON keyword
in cross joins, and the
order in which the tables are listed doesn’t matter.
For example, let’s perform a cross join of the Movie
and Genre tables. To limit the result set, let’s include a
WHERE clause so that only comedies and thrillers are
included. The query would look like
SELECT g.Genre, m.Movie
FROM Movie m
CROSS JOIN Genre g
WHERE Genre = ‘Comedy’
OR Genre = ‘Thriller’
ORDER BY Genre, Movie
As Figure 7 shows, every Genre record is connected to
every Movie record.
JOIN In on the Fun
As you can see, it’s easy to use the INNER JOIN,
LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL
OUTER JOIN, and CROSS JOIN clauses. Now that
you know how to perform inner joins, outer joins, and
cross joins, I hope that you’ll use your newfound join
skills to create some sample reports. Just try to have
fun and keep the cross joins to a minimum, especially
when dealing with tables with thousands of rows or
more. (Your DBA will appreciate it.)
End of Article
Prev. page
1
2
[3]
next page -->
