SQL多表连接查询

sql语法：inner join on, left join on, right join on详细使用方法。

 

1.理论

只要两个表的公共字段有匹配值，就将这两个表中的记录组合起来。

个人理解：以一个共同的字段求两个表中符合要求的交集，并将每个表符合要求的记录以共同的字段为牵引合并起来。

语法

select * FROM table1 INNER JOIN table2 ON table1 . field1compopr table2 . field2

INNER JOIN 操作包含以下部分：

 

部分

说明

table1, table2

要组合其中的记录的表的名称。

field1，field2

要联接的字段的名称。如果它们不是数字，则这些字段的数据类型必须相同，并且包含同类数据，但是，它们不必具有相同的名称。

compopr

任何关系比较运算符：“=”、“<”、“>”、“<=”、“>=”或者“<>”。

     

说明

可以在任何 FROM 子句中使用 INNER JOIN 操作。这是最常用的联接类型。只要两个表的公共字段上存在相匹配的值，Inner 联接就会组合这些表中的记录。

可以将 INNER JOIN 用于 Departments 及 Employees 表，以选择出每个部门的所有雇员。而要选择所有部分（即使某些部门中并没有被分配雇员）或者所有雇员（即使某些雇员没有分配到任何部门），则可以通过 LEFT JOIN 或者 RIGHT JOIN 操作来创建外部联接。

如果试图联接包含备注或 OLE 对象数据的字段，将发生错误。

可以联接任何两个相似类型的数字字段。例如，可以联接自动编号和长整型字段，因为它们均是相似类型。然而，不能联接单精度型和双精度型类型字段。

下例展示了如何通过 CategoryID 字段联接 Categories 和 Products 表：

SELECT CategoryName, ProductName

FROM Categories INNER JOIN Products

ON Categories.CategoryID = Products.CategoryID;

在前面的示例中，CategoryID 是被联接字段，但是它不包含在查询输出中，因为它不包含在 SELECT 语句中。若要包含被联接字段，请在 SELECT 语句中包含该字段名，在本例中是指 Categories.CategoryID。

也可以在 JOIN 语句中链接多个 ON 子句，请使用如下语法：

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2 OR
ON table1.field3 compopr table2.field3;

也可以通过如下语法嵌套 JOIN 语句：

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

LEFT JOIN 或 RIGHT JOIN 可以嵌套在 INNER JOIN 之中，但是 INNER JOIN 不能嵌套于 LEFT JOIN 或 RIGHT JOIN 之中。

2.操作实例

表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

实验如下:
1.left join

sql语句如下:
select * from A
left join 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
（所影响的行数为 5 行）

结果说明:
              left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.

2.right join
sql语句如下:
select * from A
right join 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
（所影响的行数为 5 行）
结果说明:
        仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join
sql语句如下:
select * from A
innerjoin 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

结果说明:
        很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.   还有就是inner join 可以结合where语句来使用 如：    select * from Ainnerjoin B on A.aID = B.bID where b.bname='2006032401' 这样的话 就只会放回一条数据了

个人总结：将多张表安装以上三种规则连接成一张表

 

 

SQL教程:5.4 多表联接查询

时间:2011-07-22 11:42来源:网页学习网 作者:网页学习网 点击: 125次

前面我们讲述过的所有查询，都是基于单个数据库表的查询，本节介绍牵涉到多个表的数据查询。

一、多表联接查询的分类

在上面介绍的学员内部测试成绩查询中，我们每次显示的都是学员的编号信息，因为该表中只存储了学员的编号。实际上最好显示学员的姓名，而姓名却存储在学员信息表中，像这种需要从多个表中选择或者比较数据项的情况，就需要使用到多表联接查询。

对比：我们前面提到过：表和表之间的“关系”是“关系型数据库”的重要特点，显然，同时从两个甚至多个表中取得数据是很重要的。

多表联接查询实际上是通过各个表之间共同列的关联性来查询数据的，它是关系数据库查询最主要的特征。

联接可分为以下内联接、外联接及交叉联接等类型。

1．内联接

内联接是最典型、最常用的联接查询，它根据表中共同的列来进行匹配，特别是两个表存在主外键关系时通常会使用到内联接查询。

内联接查询通常会使用像“=”或“◇”之类的比较运算符来判断两列数据项是否相等，上面所说的根据学员编号信息来判断出学员姓名的联接就是一种内联接。

内联接使用Inner Join关键字来进行表之间的关联。

2．外联接

外联接可以是左外联接、右外联接或完整外联接。

(1) 左外联接：Left Join或Left Outer Join。

左外联接的结果集包括Left Join子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列均为空值。

(2) 右外联接：Right Join或Right Outer Join。

右外联接是左外联接的反向联接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

(3) 完整外联接：Full Join或Full Outer Join。

完整外联接返回左表和右表中的所有行，当某行在另一个表中没有匹配行时，则另一个表的选择列包含空值，如果表之间有匹配行，则整个结果集行包含基表的数据值。

3．交叉联接

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行再一一组合，相当于两个表“相乘”。

二、内联接查询

内联接查询可以通过两种方式实现。

1．在Where子句中指定联接条件

例如查询学员姓名和成绩的SQL：

折叠展开SQL 代码复制内容到剪贴板

1. SELECT Students.SName, Score.CourseID, Score.Score
2. FROM Students,Score
3. WHERE Students.SCode = Score.StudentID

上面这种形式的查询，相当于From后面紧跟了两个表名，然后在字段列表中用“表名.列名”来区分列，再在Where条件子句中加以判断，要求学员编号信息相等。

2．在From子句中使用Join…On

上面的查询也可以通过以下的Join…On子句来实现：

折叠展开SQL 代码复制内容到剪贴板

1.  SELECT S.SName,C.CourseID,C.Score

2.  From Score AS C

3.  INNER JOIN Students AS S

4.  ON C.StudentID = S.SCode

在上面的内联接查询中，使用As语句来指定表的“别名”，这是为了提高查询语句的可读性。

经验：如果查询的列名在用到的两个或多个表中不重复，则对这一列的引用不必用表名来限定。

在NorthWind数据库中，以下的SQL语句：

折叠展开SQL 代码复制内容到剪贴板

1.  SELECT ProductID, Suppliers.SupplierID, CompanyName

2.  FROM Suppliers INNER JOIN Products

3.  ON (Suppliers.SupplierID = Products.SupplierID)

4.  WHERE UnitPrice > $10 AND CompanyName LIKE 'F%'

查询将返回某公司所提供的一组产品和供应商信息，该公司名以F字母开头，并且产品价格在10美元以上。

经验：SQL Sever执行这种Inner Join联接的速度跟执行第一种“在Where子句中指定联接条件”查询的速度是不一样的。

内联接查询通常不仅仅联接两个表，有时候还会牵涉到三个表或者更多的表。例如除了学员信息表、学员成绩表之外，还存在课程名称表，上面的查询不仅仅要显示学员姓名、分数，而且要通过课程编号来显示课程名称表中对应课程的名称，可以使用以下的三表联接查询的SQL语句来实现：

折叠展开SQL 代码复制内容到剪贴板

1.  SELECT S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩

2.  FROM Students AS S

3.  INNER JOIN Score AS C ON (S.SCode = C.StudentID)

4.  INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)

注意以上的数据分别来自三个不同的数据表。

三、外联接查询

通过上面的例子可以看出：内联接的结果是从两个或两个以上的表的组合中挑选出符合联接条件的数据。如果数据无法满足联接条件则将其丢弃。在内部联接中，参与联接的表的地位是平等的。

与内部联接相对的方式称为外部联接。在外部联接中参与联接的表有主从之分，以主表的每行数据去匹配从表的数据列，符合联接条件的数据将直接返回到结果集中；对那些不符合联接条件的列，将被填上Null值（空值）后再返回到结果集中。WANGYEXX.COM

1．左外联接查询

例如要统计所有学员的考试情况，要求显示所有参加考试学员的每次考试分数，没有参加考试的学员也要显示出来。这时候，以学员信息表为主表、学员成绩表为从表的左外联接查询：

折叠展开SQL 代码复制内容到剪贴板

1.  SELECT S.SName,C.CourseID,C.Score

2.  From Students AS S

3.  LEFT JOIN Score AS C

4.  ON C.StudentID = S.SCode

查询的结果可能有部分学员没有出现在成绩表上，对应的科目和成绩以Null（空值）填充，查询结果如图1所示。

2．右外联接查询

右外联接查询与左外联接查询类似，只不过要包含右表中所有匹配的行。如果右表中有的项在左表中没有对应的项，则以Null值来填充。例如，在Pubs数据库中，在Titles和Publishers表之间的右向外联接将包括所有的出版商，在Titles表中没有书名的（很久没出书了）也被列出。

 

折叠展开SQL 代码复制内容到剪贴板

1.  SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name

2.  FROM titles

3.  RIGHT OUTER JOIN Publishers

4.  ON Titles.Pub_id = Publishers.Pub_id