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. Berkeleystate 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
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. Berkeleystate 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
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右:
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--完全
select * from @a,@b
select * from @a Aa join @b Bb on Aa.a=Bb.a
但只返回在联接列中具有左边值的行。
RIGHT OUTER JOIN 或 RIGHT JOIN
但只返回在联接列中具有由边值的行。FULL OUTER JOIN 或 FULL JOIN
包括不匹配的行保留不匹配信息