Database Reference
In-Depth Information
It specifies a right outer join of the Production.Product and Production.ProductReview tables.
It also specifies the criteria for joining the primary key ProductID of the Product table with the
foreign key ProductId of the ProductReview table.
on PP.ProductID = PPR.ProductID
The right outer join on ProductID produces a table composed of five columns: ProductID, Name,
ReviewerName, Comments, and Rating. All the data is retrieved from rows in
Production.ProductReview, which is the right table, and matching and unmatching data from
Production.ProductReview where their ProductID columns have the matching or even unmatching
values.
Other Joins
The SQL standard also provides for
FULL OUTER JOIN
,
UNION JOIN
, and
CROSS JOIN
(and even
NATURAL
JOIN
, basically an inner join using equality predicates), but these are much less used and beyond the
scope of this topic. We won't provide examples, but this section contains a brief summary of them.
A
FULL OUTER JOIN
is like a combination of both the
LEFT
and
RIGHT OUTER
joins. All rows from both
tables will be retrieved, even if they have no related rows in the other table.
A
UNION JOIN
is unlike outer joins in that it doesn't match rows. Instead, it creates a table that has all
the rows from both tables. For two tables, it's equivalent to the following query:
select
*
from
table1
union all
select
*
from
table2
The tables must have the same number of columns, and the data types of corresponding columns
must be compatible (able to hold the same types of data).
A
CROSS JOIN
combines all rows from both tables. It doesn't provide for a join specification, since
this would be irrelevant. It produces a table with all columns from both tables and as many rows as the
product of the number of rows in each table. The result is also known as a
Cartesian product,
since that's
the mathematical term for associating each element (row) of one set (table) with all elements of another
set. For example, if there are five rows and five columns in table A and ten rows and three columns in
table B, the cross join of A and B would produce a table with fifty rows and eight columns. Not only is
this join operation virtually inapplicable to any real-world query, but it's also a potentially very
expensive process for even small real-world databases. (Imagine using it for production tables with
thousands or even millions of rows.)
Summary
In this chapter, we covered how to construct database queries using SQL features such as range, list, I
S
NULL
operators, aggregate functions,
DATETIME
functions,
GROUP BY
clauses, joins, and pattern matching.
In the next chapter, you will learn about creating stored procedures.
