Database Reference
In-Depth Information
The basic syntax for an inner join is as follows:
select
<select list>
from
left-table INNER JOIN right-table
ON
<join specification>
Notice that
INNER JOIN
is a binary operation, so it has two operands,
left-table
and
right-table
,
which may be base tables or anything that can be queried (for example, a table produced by a subquery
or by another join). The
ON
keyword begins the join specification, which can contain anything that could
be used in a
WHERE
clause.
Table Aliasing
Table aliasing is a technique used to assign a short nickname to a table or each individual table needed
in any SQL query. Though you can use full table name, repeating the table name again and again in a
query is a cumbersome process.
Hence, table aliasing makes it very easy when you have to specify column names that either may
exist in multiple tables or you want to use different columns from a particular table; therefore, it
becomes very important to specify
<Table Name>.<Column Name>
.
Refer to the following query, in which we are aliasing
Production.Product
as PP and
Production.ProductReview
to PPR. Next, when it comes to use columns from these tables, we use the
defined aliases.
Also, remember these aliases are temporary, and their life span is until the query is executed. After a
query execution, you can't reuse the same alias for any other query. Hence, the scope of an alias is within
the defined query and life span is until the query is executed.
Try It: Writing an Inner Join
Let's retrieve a list of products, the product IDs, and their
ReviewerName
,
Comments
, and
Rating
entries.
Open a New Query window in SQL Server Management Studio (remember to make AdventureWorks
your query context). Enter the following query, and click Execute. You should see the results shown in
Figure 5-21.
select PP.ProductID, PP.Name, PPR.ReviewerName, PPR.Comments, PPR.Rating
from Production.Product PP inner join Production.ProductReview PPR
on PP.ProductID = PPR.ProductID
