内连接，外连接，左连接，右连接，全连接，内连接，交叉连接，自连接

内连接，外连接，左连接，右连接，全连接，内连接，交叉连接，自连接 (一) 内连接 内连接，inner join，join 查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的 列值。内连接分三种: 1.等值连接 2.不等值连接 3.自然连接 (二) 外连接 外连接，返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左 外连接或左连接))、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。 左连接或left join，left outer join 返回包括左表中的所有和右表中联结字段相等的记录; 右连接或right join ，right outer join 返回包括右表中的所有记录和左表中联结字段相等的记录; (三) 交叉连接 交叉连接不带WHERE 子句， 它返回被连接的两个表所有数据行的笛卡尔积，返回到 结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查 询条件的数据行数。 SQL--JOIN之完全用法 外联接。外联接可以是左向外联接、右向外联接或完整外部联接。 在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定: LEFT JOIN 或 LEFT OUTER JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 RIGHT JOIN 或 RIGHT OUTER JOIN。 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。 FULL JOIN 或 FULL OUTER JOIN。 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。 仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。 Microsoft® SQL Server™ 2000 对在 FROM 子句中指定的外联接使用以下 SQL-92 关键字: LEFT OUTER JOIN 或 LEFT JOIN RIGHT OUTER JOIN 或 RIGHT JOIN FULL OUTER JOIN 或 FULL JOIN SQL Server 支持 SQL-92 外联接语法，以及在 WHERE 子句中使用 *= 和 =* 运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义，而旧式 Transact-SQL 外联接有时会产生歧义，因此建议使用 SQL-92 语法。 使用左向外联接 假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。 若要在结果中包括所有的作者，而不管出版商是否住在同一个城市，请使用 SQL-92 左向外联接。下面是 Transact-SQL 左向外联接的查询和结果: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 下面是结果集: au_fname au_lname pub_name -------------------- ------------------------------ ----------------- Reginald Blotchet-Halls NULL Michel DeFrance NULL Innes del Castillo NULL Ann Dull NULL Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Michael O'Leary NULL Sylvia Panteley NULL Albert Ringer NULL Anne Ringer NULL Meander Smith NULL Dean Straight NULL Dirk Stringer NULL Johnson White NULL Akiko Yokomoto NULL Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems (23 row(s) affected) 不管是否与 publishers 表中的 city 列匹配，LEFT OUTER JOIN 均会在结果中包含 authors 表的所有行。注意:结果中所列的大多数作者都没有相匹配的数据,因此，这些行的 pub_name 列包含空值。 使用右向外联接 假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。SQL-92 右向外联接运算符 RIGHT OUTER JOIN 指明:不管第一个表中是否有匹配的数据，结果将包含第二个表中的所有行。 若要在结果中包括所有的出版商，而不管城市中是否还有出版商居住，请使用 SQL-92 右向外联接。下面是 Transact-SQL 右向外联接的查询和结果: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 下面是结果集: au_fname au_lname pub_name -------------------- ------------------------ -------------------- Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems NULL NULL Binnet & Hardley NULL NULL Five Lakes Publishing NULL NULL GGG&G NULL NULL Lucerne Publishing NULL NULL New Moon Books NULL NULL Ramona Publishers NULL NULL Scootney Books (9 row(s) affected) 使用谓词(如将联接与常量比较)可以进一步限制外联接。下例包含相同的右向外联接， 但消除销售量低于 50 本的书籍的书名: USE pubs SELECT s.stor_id, s.qty, t.title FROM sales s RIGHT OUTER JOIN titles t ON s.title_id = t.title_id AND s.qty > 50 ORDER BY s.stor_id ASC 下面是结果集: stor_id qty title ------- ------ --------------------------------------------------------- (null) (null) But Is It User Friendly? (null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior Variations (null) (null) Cooking with Computers: Surreptitious Balance Sheets (null) (null) Emotional Security: A New Algorithm (null) (null) Fifty Years in Buckingham Palace Kitchens 7066 75 Is Anger the Enemy? (null) (null) Life Without Fear (null) (null) Net Etiquette (null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean (null) (null) Prolonged Data Deprivation: Four Case Studies (null) (null) Secrets of Silicon Valley (null) (null) Silicon Valley Gastronomic Treats (null) (null) Straight Talk About Computers (null) (null) Sushi, Anyone? (null) (null) The Busy Executive's Database Guide (null) (null) The Gourmet Microwave (null) (null) The Psychology of Computer Cooking (null) (null) You Can Combat Computer Stress! (18 row(s) affected) 有关谓词的更多信息，请参见 WHERE。 使用完整外部联接 若要通过在联接结果中包括不匹配的行保留不匹配信息，请使用完整外部联接。 Microsoft® SQL Server™ 2000 提供完整外部联接运算符 FULL OUTER JOIN，不管另一个表是否有匹配的值，此运算符都包括两个表中的所有行。 假设在 city 列上联接 authors 表和 publishers 表。结果只显示在出版商所 在城市居住的作者(本例中为 Abraham Bennet 和 Cheryl Carson)。SQL-92 FULL OUTER JOIN 运算符指明:不管表中是否有匹配的数据，结果将包括两个表中 的所有行。 若要在结果中包括所有作者和出版商，而不管城市中是否有出版商或者出版商是否住在同 一个城市，请使用完整外部联接。下面是 Transact-SQL 完整外部联接的查询和结果: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 下面是结果集: au_fname au_lname pub_name -------------------- ---------------------------- -------------------- Reginald Blotchet-Halls NULL Michel DeFrance NULL Innes del Castillo NULL Ann Dull NULL Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Michael O'Leary NULL Sylvia Panteley NULL Albert Ringer NULL Anne Ringer NULL Meander Smith NULL Dean Straight NULL Dirk Stringer NULL Johnson White NULL Akiko Yokomoto NULL Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems NULL NULL Binnet & Hardley NULL NULL Five Lakes Publishing NULL NULL GGG&G NULL NULL Lucerne Publishing NULL NULL New Moon Books NULL NULL Ramona Publishers NULL NULL Scootney Books (30 row(s) affected) 联接条件可在 FROM 或 WHERE 子句中指定，建议在 FROM 子句中指定联接条件。WHERE 和 HAVING 子句也可以包含搜索条件，以进一步筛选联接条件所选的行。 联接可分为以下几类: 内联接(典型的联接运算，使用像 = 或 <> 之类的比较运算符)。包括相等联接和自然联接。 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students 和 courses 表中学生标识号相同的所有行。 外联接。外联接可以是左向外联接、右向外联接或完整外部联接。 在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定: LEFT JOIN 或 LEFT OUTER JOIN。 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 RIGHT JOIN 或 RIGHT OUTER JOIN。 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没 有匹配行，则将为左表返回空值。 FULL JOIN 或 FULL OUTER JOIN。 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个 表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。 交叉联接。 交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作 笛卡尔积。 例如，下面的内联接检索与某个出版商居住在相同州和城市的作者: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a INNER JOIN publishers AS p ON a.city = p.city AND a.state = p.state ORDER BY a.au_lname ASC, a.au_fname ASC FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定;但是，用左或 右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更 多信息，请参见使用外联接。 例子: a表 id name b表 id job parent_id 1 张3 1 23 1 2 李四 2 34 2 3 王武 3 34 4 a.id同parent_id 存在关系 内连接 select a.*,b.* from a inner join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 左连接 select a.*,b.* from a left join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 3 王武 null 右连接 select a.*,b.* from a right join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 完全连接 select a.*,b.* from a full join b on a.id=b.parent_id 结果是 1 张3 1 23 1 2 李四 2 34 2 null 3 34 4 3 王武 null 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。 连接可以在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数据类型列进行直接连接，但可以对这三种列进行间接连接。 下面结合案例说明: 假设有如下表: 一个为投票主表，一个为投票者信息表,记录投票人IP及对应投票类型，左右连接实际说是我们联合查询的结果以哪个表为准, 1:如右接连 right join 或 right outer join: 右向外联接:右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。 我们以右边voter表为准，则左表(voteMaster)中的记录只有当其ID在右边(voter)中存在时才会显示出来，如上图，左边中ID为3.4.5.6因为这些ID右表中没有相应记录，所以没有显示～ 2:因此我们自然能理解左连接 left join 或者 left outer join 左向外联接:左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 可见，现在右边中ID在中存在时才会显示，当右边中没有相应数据时则用NULL代替～ 3:全连接 full join 或者 full outer join,为二个表中的数据都出来，这里演示效果与上一样～ 完整外部联接:完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。 4:内连接 inner join 或者 join; 内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种: 1、等值连接:在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出 被连接表中的所有列，包括其中的重复列。 2、不等连接: 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。 3、自然连接:在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。 它为返回字段ID同时存在于表voteMaster 和 voter中的记录 5:交叉连接(完全连接)cross join 不带 where 条件的 没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。(table1和table2交叉连接产生6*3=18条记录) 等价select vm.id,vm.voteTitle,vt.ip from voteMaster as vm,voter as vt