sql中join的几种常见用法总结……

Question

常见的几种join的方法，如：left...join
right...join...
inner...join...
还有一些左外连接，左外连接等等各种连接
都快迷糊了，
到底有什么区别啊？以下面这两个表为例：
表A记录如下：
aID               aNum
1                  a20050111
2                  a20050112
3                  a20050113
4                  a20050114
5                  a20050115表B记录如下:
bID               bName
1                   2006032401
2                  2006032402
3                  2006032403
4                  2006032404
8                  2006032408
希望高手可以帮忙详细说说？谢谢！！非常感谢！！

Answer 1

http://hi.baidu.com/ericden/blog/item/7976878fd60517ff503d9200.html
SQL JOIN 之完全用法

Answer 2

JOIN连接组合两个表中的字段记录，包括三种：  
INNER JOIN运算式：连接组合两个表中的字段记录。 LEFT JOIN运算式：连接组合两个表中的字段记录，并将包含了LEFT JOIN左边表中的全部记录。 RIGHT JOIN运算式：连接组合两个表中的字段记录，并将包含了RIGHT JOIN右边表中的全部记录。 
INNER JOIN设定两个表相关连的运算式，以连接组合两个表中的字段记录。 INNER JOIN语法如下： FROM 表1 INNER JOIN 表2 ON 表1.字段1 比较运算子 表2.字段2 两个表连接的字段，譬如 [表1.字段1=表2.字段2]，必须具有相同的字段类型，但是字段名称不需要相同。 例如，自动编号字段类型可以连接Long 的字段类型，但是单精整数字段类型不能连接双精整数的字段类型。 比较运算子可为=、<、>、<=、>=、或<>。 JOIN连接的字段，不可以包含MEMO字段类型或OLE对象类型，否则会发生错误。 在一个JOIN表达式中，可以连结多个ON子句： SELECT fields 
FROM 表1 INNER JOIN 表2 
ON 表1.字段1 比较运算子 表2.字段1 AND 
ON 表1.字段2 比较运算子 表2.字段2) OR 
ON 表1.字段3 比较运算子 表2.字段3) JOIN表达式中，可以为巢状式： SELECT fields 
FROM 表1 INNER JOIN 
(表2 INNER JOIN [( ]表3 
[INNER JOIN [( ] 表x [INNER JOIN ...)] 
ON 表3.字段3 比较运算子 表x.字段x)] 
ON 表2.字段2 比较运算子 表3.字段3) 
ON 表1.字段1 比较运算子 表2.字段2 在一个INNER JOIN中，可以包括巢状式的LEFT JOIN或RIGHT JOIN，但是在一个LEFT JOIN或RIGHT JOIN中不能包括巢状式的INNER JOIN。 

Answer 3

来个非主流的解释：left join 以left join前面的表为主表，把left join后面的表符合on条件的内容加到主表中，如果没有符合on条件的内容，则显示为NULLright join 以right join后面的表为主表，把right join前面的表符合on条件的内容加到主表中，如果没有符合on条件的内容，则显示为NULLinner join 就是返回两个表中符合on条件的记录，若不满足条件则不返回

Answer 4

http://blog.csdn.net/cugwei/archive/2005/12/06/544705.aspx
这一篇比较全

Answer 5

连接查询
　　 
通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。 
　　 
在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。 
　　 
连接可以在Select 语句的FROM子句或Where子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与Where子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。 
　　 
SQL-92标准所定义的FROM子句的连接语法格式为： 　　 
　　FROM join_table join_type join_table 
　　[ON (join_condition)] 
其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。 
　　 
join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。 
　　 
交叉连接(CROSS JOIN)没有Where 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 
　　 
连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。 
　　 
无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如： 　　 
　　Select p1.pub_id,p2.pub_id,p1.pr_info 
　　FROM pub_info AS p1 INNER JOIN pub_info AS p2 
　　ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info) 
　　 (一)内连接 
　　 
内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种： 
　　 
1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。 
　　 
2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、 <=、 <、!>、! <和 <>。 
　　 
3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。 
　　 
例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社： 　　 
　　Select * 
　　FROM authors AS a INNER JOIN publishers AS p 
　　ON a.city=p.city 
又如使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)： 　　 
　　Select a.*,p.pub_id,p.pub_name,p.country 
　　FROM authors AS a INNER JOIN publishers AS p 
　　ON a.city=p.city 
(二)外连接 
　　 
内连接时，返回查询结果集合中的仅是符合查询条件( Where 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。如下面使用左外连接将论坛内容和作者信息连接起来： 　　 
　　Select a.*,b.* FROM luntan LEFT JOIN usertable as b 
　　ON a.username=b.username 
　　 
下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市： 　　 
　　Select a.*,b.* 
　　FROM city as a FULL OUTER JOIN user as b 
　　ON a.username=b.username 
　　 
(三)交叉连接 
　　 
交叉连接不带Where 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等于6*8=48行。 　　 
　　Select type,pub_name 
　　FROM titles CROSS JOIN publishers 
　　ORDER BY type 《数据库的连接查询》 连接的结果是从两个或两个以上的表的组合中挑选出符合连接条件的数据，如果数据无法满足连接条件则将其丢弃。通常称这种方 法为内部连接（InnerJoin）。在内部连接中，参与连接的表的地位是平等的。与内部连接相对的方式称为外部连接（Outer Join） 。在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列，符合连接条件的数据将直接返回到结果集中 ，对那些不符合连接条件的列，将被填上NULL 值后再返回到结果集中（对BIT 类型的列，由于BIT 数据类型不允许NULL 值，因此 将会被填上0 值再返回到结果中）。     外部连接分为左外部连接（Left Outer Join）和右外部连接（Right Outer Join）两种。以主表所在的方向区分外部连接，主 表在左边，则称为左外部连接，主表在右边，则称为右外部连接。 
通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过 连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。 为不同实体创建新的表，尔后通过连接进行查询。 连接可以在Select 语句的FROM子句或Where子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与Where子句中的搜索 条件区分开来。所以，在Transact-SQL中推荐使用这种方法。 SQL-92标准所定义的FROM子句的连接语法格式为： FROM join_table join_type join_table [ON (join_condition)] 其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。 join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据 的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等 连接三种。 外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或 FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或 两个表(全外连接时)中所有符合搜索条件的数据行。 交叉连接(CROSS JOIN)没有Where 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查 询条件的数据行数乘以第二个表中符合查询条件的数据行数。 连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。 无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接 
《数据库多表连接查询详解》 通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。 连接可以在Select 语句的FROM子句或Where子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与Where子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。 SQL-92标准所定义的FROM子句的连接语法格式为： FROM join_table join_type join_table [ON (join_condition)] 其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。 join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。 外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。 交叉连接(CROSS JOIN)没有Where 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。 
无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。 (一)内连接 内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种： 1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。 2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、 <=、 <、!>、! <和 <>。 3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。 例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社： Select * FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city 
又如使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)： Select a.*,p.pub_id,p.pub_name,p.country FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city 
(二)外连接 内连接时，返回查询结果集合中的仅是符合查询条件( Where 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。 外联接可以是左向外联接、右向外联接或完整外部联接。 
在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定：LEFT JOIN 或 LEFT OUTER JOIN；RIGHT JOIN 或 RIGHT OUTER JOIN；FULL JOIN 或 FULL OUTER JOIN。 （1）左向外联接：左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 （2）右向外联接：右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。 （3）完整外部联接：完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。 仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 Where 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。 如下面使用左外连接将论坛内容和作者信息连接起来： Select a.*,b.* FROM luntan LEFT JOIN usertable as b ON a.username=b.username 
下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市： Select a.*,b.* FROM city as a FULL OUTER JOIN user as b ON a.username=b.username 
(三)交叉连接 交叉连接不带Where 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等 于6*8=48行。 Select type,pub_name FROM titles CROSS JOIN publishers orDER BY type  

Answer 6

declare @表A table (aID int,aNum varchar(9))
insert into @表A
select 1,'a20050111' union all
select 2,'a20050112' union all
select 3,'a20050113' union all
select 4,'a20050114' union all
select 5,'a20050115'
declare @表B table (bID int,bName varchar(12))
insert into @表B
select 1,'2006032401' union all
select 2,'2006032402' union all
select 3,'2006032403' union all
select 4,'2006032404' union all
select 8,'2006032408'--左联等价于 left outer join (一个表的所有行和另一个表满足条件的行)
select * from @表A a left join @表B b on a.aID=b.bID;
  
/*
aID         aNum      bID         bName
----------- --------- ----------- ------------
1           a20050111 1           2006032401
2           a20050112 2           2006032402
3           a20050113 3           2006032403
4           a20050114 4           2006032404
5           a20050115 NULL        NULL
*/--右联等价于 right outer join
select * from @表A a right join @表B b on a.aID=b.bID;/*
aID         aNum      bID         bName
----------- --------- ----------- ------------
1           a20050111 1           2006032401
2           a20050112 2           2006032402
3           a20050113 3           2006032403
4           a20050114 4           2006032404
NULL        NULL      8           2006032408
*/--笛卡尔乘积
select * from @表A a cross join @表B b;/*
aID         aNum      bID         bName
----------- --------- ----------- ------------
1           a20050111 1           2006032401
2           a20050112 1           2006032401
3           a20050113 1           2006032401
4           a20050114 1           2006032401
5           a20050115 1           2006032401
1           a20050111 2           2006032402
2           a20050112 2           2006032402
3           a20050113 2           2006032402
4           a20050114 2           2006032402
5           a20050115 2           2006032402
1           a20050111 3           2006032403
2           a20050112 3           2006032403
3           a20050113 3           2006032403
4           a20050114 3           2006032403
5           a20050115 3           2006032403
1           a20050111 4           2006032404
2           a20050112 4           2006032404
3           a20050113 4           2006032404
4           a20050114 4           2006032404
5           a20050115 4           2006032404
1           a20050111 8           2006032408
2           a20050112 8           2006032408
3           a20050113 8           2006032408
4           a20050114 8           2006032408
5           a20050115 8           2006032408
*/--内联等价于 inner join
select * from @表A a join @表B b on a.aID=b.bID;/*
aID         aNum      bID         bName
----------- --------- ----------- ------------
1           a20050111 1           2006032401
2           a20050112 2           2006032402
3           a20050113 3           2006032403
4           a20050114 4           2006032404
*/--外联
select * from @表A a full outer join @表B b on a.aID=b.bID;/*
aID         aNum      bID         bName
----------- --------- ----------- ------------
1           a20050111 1           2006032401
2           a20050112 2           2006032402
3           a20050113 3           2006032403
4           a20050114 4           2006032404
5           a20050115 NULL        NULL
NULL        NULL      8           2006032408
*/

Answer 7

maco_wang这位仁兄写得很细啊，LZ看看连接后的结果有什么变化吧