Using Inner Joins

Accessing and Changing Relational Data

Using Inner Joins

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 are 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 not-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, not-equal joins make sense only when used with a self-join. For example, this not-equal Transact-SQL join and self-join are 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)

Using Inner Joins

Accessing and Changing Relational Data