2011-10-12 16:30:00
An inner join is a join in which the values in the columns being joined are compared using a comparison operator.
In the SQL-92 standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that SQL-92 supports in the WHERE clause. Inner joins specified in the WHERE clause is known as old-style inner joins.
This Transact-SQL query is an example of an inner join:
USE pubs SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city = p.city ORDER BY a.au_lname DESC
This inner join is known as an equi-join. It returns all the columns in both tables and returns only the rows for which there is an equal value in the join column.
Here is the result set:
au_id au_lname au_fname phone address city ----------- -------- -------- ------------ --------------- -------- 238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley 409-56-7008 Bennet Abraham 415 658-9932 6223 Bateman St. Berkeley state zip contract pub_id pub_name city state country ----- ----- -------- ------ --------------------- -------- ----- ------- CA 94705 1 1389 Algodata Infosystems Berkeley CA USA CA 94705 1 1389 Algodata Infosystems Berkeley CA USA (2 row(s) affected)
In the result set, the city column appears twice. Because there is no point in repeating the same information, one of these two identical columns can be eliminated by changing the select list. The result is called a natural join. You can restate the preceding Transact-SQL query to form a natural join. For example:
USE pubs
SELECT p.pub_id,
p.pub_name,
p.state, a.*
FROM publishers p
INNER JOIN authors a
ON p.city = a.city
ORDER BY a.au_lname ASC,
a.au_fname ASC
Here is the result set:
pub_id pub_name state au_id au_lname au_fname ------ --------------- -------- ----------- -------- -------- 1389 Algodata Infosystems CA 409-56-7008 Bennet Abraham 1389 Algodata Infosystems CA 238-95-7766 Carson Cheryl phone address city state zip contract --------------- ------------- -------- ----- ----- --------- 415 658-9932 6223 Bateman St. Berkeley CA 94705 1 415 548-7723 589 Darwin Ln. Berkeley CA 94705 1 (2 row(s) affected)
In this example, publishers.city does not appear in the results.
Joins Using Operators Other Than Equal
You can also join values in two columns that are not equal. The same operators and predicates used for inner joins can be used for non-equal joins. For more information about the available operators and predicates that can be used in joins, see Using Operators in Expressions and WHERE.
This Transact-SQL example is of a greater-than (>) join which finds New Moon authors who live in states that come alphabetically after Massachusetts, where New Moon Books is located.
USE pubs
SELECT p.pub_name, p.state,
a.au_lname,
a.au_fname, a.state
FROM publishers p
INNER JOIN authors a
ON a.state > p.state
WHERE p.pub_name = 'New Moon Books'
ORDER BY au_lname ASC,
au_fname ASC
Here is the result set:
pub_name state au_lname au_fname state ---------------- ------- -------------------- -------------------- ----- New Moon Books MA Blotchet-Halls Reginald OR New Moon Books MA del Castillo Innes MI New Moon Books MA Greene Morningstar TN New Moon Books MA Panteley Sylvia MD New Moon Books MA Ringer Albert UT New Moon Books MA Ringer Anne UT (6 row(s) affected)
Joins Using the Not-equal Operator
The not-equal join (<>) is rarely used. As a general rule, non-equal joins make sense only when used with a self-join. For example, this not-equal Transact-SQL join and self-join is used to find the categories with two or more inexpensive (less than $15) books of different prices:
USE pubs SELECT DISTINCT t1.type, t1.price FROM titles t1 INNER JOIN titles t2 ON t1.type = t2.type AND t1.price <> t2.price WHERE t1.price < $15 AND t2.price < $15
Note The expression NOT column_name = column_name is equivalent to column_name <> column_name.
This Transact-SQL example uses a not-equal join combined with a self-join to find all rows in the titleauthor table in which two or more rows have the same title_id but different au_id numbers (that is, books with more than one author):
USE pubs
SELECT DISTINCT t1.au_id,
t1.title_id
FROM titleauthor t1
INNER JOIN titleauthor t2
ON t1.title_id = t2.title_id
WHERE t1.au_id <> t2.au_id
ORDER BY t1.au_id
Here is the result set:
au_id title_id ----------- -------- 213-46-8915 BU1032 267-41-2394 BU1111 267-41-2394 TC7777 409-56-7008 BU1032 427-17-2319 PC8888 472-27-2349 TC7777 672-71-3249 TC7777 722-51-5454 MC3021 724-80-9391 BU1111 724-80-9391 PS1372 756-30-7391 PS1372 846-92-7186 PC8888 899-46-2035 MC3021 899-46-2035 PS2091 998-72-3567 PS2091 (15 row(s) affected)
