inner join , left join ,right join ,full join区别
1. Inner Join (内联接)
此方式返回满足第一个(顶端)输入与第二个(底端)输入联接的每一行, 即显示两表联接条件满足的记录;
例: 以SQL 2000 中的pubs表为例;
USE pubs
SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city = p.city
ORDER BY a.au_lname DESC
结果: 两个表中的所有列,但只返回联接条件满足的记录;
2. Left Outer Join 或 Left Join (左外联接)
显示左边表的所有记录,并显示联接表的所有字段满足条件的记录;
例: 以SQL 2000 中的pubs表为例;
USE pubs
SELECT * FROM authors AS a LEFT OUTER JOIN publishers AS p
ON a.city = p.city ORDER BY p.pub_name ASC
运行结果:
显示authors 表的所有记录, 并显示publishers 表的所有列的满足条件的记录;
此联接方式的结果正好与Right Outer Join 相反;
返回的记录数据为左表的记录数;
3. Right Outer Join 或 Right Join (右外联接)
显示右边表的所有记录,并显示左边表的所有列满足条件的记录;
例:以SQL 2000 中的pubs 表为例;
USE pubs
SELECT * FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city ORDER BY p.pub_name ASC
4. Full Outer Join 或 Full Join (全外联接)
显示联接两个表的所有列和记录;
例: 以SQL 2000中的pubs表为例;
USE pubs
SELECT * FROM authors AS a FULL OUTER JOIN publishers p
ON a.city = p.city ORDER BY p.pub_name ASC
转载出处：http://blog.csdn.net/fcjg/archive/2008/05/20/2463949.aspx