在 SQL-92 标准中,内联接可在 FROM 或 WHERE 子句中指定。这是 WHERE 子句中唯一一种 SQL-92 支持的联接类型。WHERE 子句中指定的内联接称为旧式内联接。
下面的 Transact-SQL 查询是内联接的一个示例:
USE pubs
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
ORDER BY a.au_lname DESC
此内联接称为相等联接。它返回两个表中的所有列,但只返回在联接列中具有相等值的行。
下面是结果集:
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
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
下面是结果集:
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
下面的 Transact-SQL 示例是一个大于 (>) 联接,可用于查找住在 Massachusetts 之后(按字母顺序排列)的州的 New Moon 作家,Massachusetts 是 New Moon Books 的所在地。
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
下面是结果集:
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
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
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