sql2000学习笔记之select连接查询

本篇主要介绍，使用select语句查询多个表中的数据，并将查询结果放在同一个结果集中。

1、需要一张表，包含：产品名称和其供应商公司的名称

分析：此问题牵涉到两张表，products和suppliers。对于这样典型的连接查询，技巧是必须找到两张表的公共列，从而推导出两张表的关系。

语法：

select 列名,列名[,列名...]

from 表名 inner join 表名

on 表名.引用列名 = 表名.引用列名

答案：

select p.productname,s.companyname//这里的列名指定了来源，以免混淆。来源使用了别名，在from中定义

from products p inner join suppliers s

on p.supplierid=s.supplierid

supplierid是两张表的公共列，两张表由此建立了关联。查询分析器在查询时，会先检索products中的第一列，读取其supplierid列的值，然后到suppliers表中检索supplierid，找到相等的，则输出对应的companyname的值到结果集中。其后查询雷同。

这样的连接查询称为"内联接"，这种方式得出的结果集中，全部是两张表中匹配的值，不匹配的会被删除。举个例子，如products某行的supplierid为null，则在suppliers表中找不到相对应的companyname，这种情况下，这一行的产品名称就不会出现在结果集中。这种情况如果要显示所有产品名称，必须使用"外联结"，稍后论述。

2、需要一张表，包含：作者与发行商同处一个城市的发行商信息。也就是结果集中的发行商，他们所在的城市，必须有某个作者居住。

分析：这个问题包括两张表，publishers和authors，无疑，两张表的公共列为city，因为结果集的条件就是同一城市，再之，必须使用内联接，从而不显示城市不一致的结果。

答案：

select p.pub_name,a.au_lname,a.au_fname

from publishers p inner join authors a

on p.city=a.city

3、需要一张表，包括：每笔交易的交易号，发生时间和交易商品的名称

分析：这个问题首先需要orders表和products表，但是，这两张表并没有公共列。显然，这时候需要order details表的支持。这张表中，含有orderid(与orders表相关)和productid(和products表关联)，所以order details可以作为orders表和products表之间的桥梁。

答案：

select o.orderid,o.orderdate,p.productname

from orders o inner join [order details] od

on o.orderid=od.orderid

inner join products p

on od.productid=p.productid

以上代码，首先将orders和order details关联，通过orderid列，查询分析器可以找到orders中第一行的orderid在order details表中的位置。接着让order details和products关联，通过前面检索的orderid找到对应的productid，最后在products表中，可以检索到相对应的productname，输出到结果集。接下来的查询与第一行的查询雷同。这和我们的思维方式相符合。

以上三个问题是内联接的查询，解决问题的关键点在于：找出公共列

下面来看几个外联结的问题。

4、需要一张表，包含：所有的客户名称，以及他们的交易记录。无论客户是否交易过，都要显示。

分析：首先找出公共列coustomerid。其次，"无论客户是否交易过，都要显示"这是典型的外联结查询方式，因为如果客户未交易过，则交易记录表中，一定没有与这个客户对应的记录，所以使用内联接是无法满足要求的。简单的说，外联结查询方式保留了不匹配的结果。

语法：

select 列名,列名[,列名...]

from 表名

left/right outer join 表名

on 表名.引用列名=表名.引用列名

说明：left表示from后的表的指定列值将全部显示，right表示outer join后的表的指定列值将全部显示。

答案：

select c.companyname,o.orderid

from customers c

left outer join orders o

on c.customerid=o.customerid

order by o.orderid//便于看到没有交易的客户

5、需要一张表，包含：出版商名称，与出版商同在一个城市的作者id

分析：牵涉到两张表，authors和publishers，公共列为city。使用外联结，需列出所有出版商的名称，如所在城市没有作者，则作者id列显示null。

答案：

select p.pub_name,a.au_id

from publishers p

left outer join authors a

on p.city=a.city

order by a.au_id desc//以便看出同城的作者

6、需要一张表，包含：每名员工的信息，和其直接上司的信息

分析：这个问题只涉及一张表employees，但牵涉到此表中的两列：employeeid和reportsto，其中reportsto值的意义是此员工向谁汇报工作。对于这种问题，要将一张表当做是两张表处理，这种检索方式叫做"内联接"。

答案：

select w.employeeid,m.employeeid

from employees w

inner join employees m

on w.resportsto=m.employeeid

以上黑体的部分是关键，这里同样是employees表，但我们将它声明为两个别名，w代表worker，m代表master，也就是看做两张表，将这两张表做内连接，检索的条件是w.resportsto=m.employeeid。例如，查询第一条记录时，查询分析器先找到w表中employeeid和其对应的resportsto，然后再在m表中搜索等于resportsto的employeeid，找到的话，就输出到结果集。这里w和m其实是同一张表。

到此为止，我们所解决的问题，全部是列拼接的，也就是用某张表的某列和某张表的某列组合成结果集。下面看一个问题，牵涉到行的拼接。

7、需要一张表，包含：员工的姓名、所在城市、邮编和客户的公司名称、城市、邮编

分析：这题所需要的数据集，必须使用纵向联接，因为他们的字段类型都相同。如：员工姓名=公司名称，城市=城市，邮编=邮编。对于纵向联接，需要使用union关键字。

语法：

select 列名[,列名...] from 表名

union [all]

select 列名[,列名...] from 表名

默认的，union产生的结果集，将删除重复行。如果要保留重复行，可使用all关键字。另外，结果集中的列名，与第一张表的列名相同。

答案：

select [e.lastname,e.firstname] name,e.city,e.postalcode from employees e

union

select c.companyname,c.city,c.postalcode from customers