复杂一点的查询

一:inner join
inner join   是在做排除，如果任一行在两个表中不匹配，则注定将从最终的结果中排除掉

例子1：select * from employee e inner join employee m on e.managerid = m.employeeid
这是从一个表里查询了两次
得到的一行记录将包括两个employee的信息  前面的是经理  后面的是打工的
注意from哪个表  哪个表的信息就在前面
其中e和m分别是表的别名，这里的别名和列的别名不同，不用写as

例子2   select  e.employeeid,  ce.firstname,  m.employeeid as managerid,  cm.firstname as managerfirst  
from employee e inner join  employee m on e.managerid = m.employeeid
inner join contact ce on e.contactid = ce.contactid
inner join contact cm on m.contactid = cm.contactid
第一个联接是把一个表与他自身进行连接  这个叫自引用(注意表的别名)
第二个连接得到经理的名字
第三个连接得到雇员的名字 
看似很复杂的连接  其实很简单
最后说一点inner join 是默认的连接类型   inner 关键字是可选的

二:outer join
先看例子
select e.employeeid,   m.employeeid  as managerid from employee e
left outer join employee m  on e.managerid = m.employeeid
无论左侧表(e)中的行是否与右侧表中的行相匹配都要显示
如果左侧表中的行在右侧表中找不到相匹配的数据,  那么右侧表的数据为null

right outer join 也类似   outer是可以忽略的

三：full  join  与  cross  join
这两个其实都不必多说
full  join  是  left join  和  right join 的结合
full  join将包含位于连接两侧的表的所有行
不存在的行就用null补齐

cross join  没有on操作符
得到的是两测表中所有行的  笛卡儿积
就是把两册的行排列组合一下
一般不会存在null的行
这是相当罕见的用法
只有科学家或者来搞样本数据的才会用到这个用法

四：union
union更像是从一个查询直接向另一个查询进行的数据追加（差别还是有的）
join更像是水平的合并数据（添加更多的列），union是垂直的合并数据（添加更多的行）
先看例子：select col2 from table1 union  all select col4 from table2
1：select 列表中字段的数量必须相同
2：字段的数据类型必须隐士兼容
3：返回的结果集的标头取自第一个查询
4：默认返回方式是distinct，union  alll返回全部的集合

五：子查询返回单个值
先看例子：
declare @hits int
select  @hits = min(hitnum) from articles
select  distinct A.title from articles A join users U on A.uid = U.id where A.hitnum = @hits
这是完全可行的，但是我们可以用另一种形式：
select distinct A.title from articles A join users U on U.id = A.uid where A.hitnum = (select min(hitnum) from articles )
这就是子查询

六：子查询返回多个值
接着看例子（子查询里返回多个值）
use  database
select  A.title from articles A join users U on A.uid = U.id where A.id in (select id from articles where istop = 'true')

再看一个例子（用子查询找出孤立的记录）
select  A.title from article A join users U on A.uid = U.id where A.id not in (select id from articles where istop = 'true')
这个例子写的有点牵强
但是这里注意  not in 子查询得到的字段  不能有null直 存在，如果有  那么整个句子将返回空

细心的人大概看出来了，前面提到的两个子查询的例子几乎都可以用内联（join）的方式替换掉
出于性能上的考虑，我们应该首先选择联结的解决方案  而不是子查询

七：any  some  和  all
any与some功能上是相同的，推荐用some
any与some一般都是和比较运算符一起使用的(>=  <=  <>  !> 等  )
>some (1,2,3)意味着大于1    >some就是大于最小的一个值
=some和in  起的作用是一样的
not in (a,b,c)展开为  <>a and  <>b and <>c
<>some (a,b,c)展开为  <>a or <>b  or <> c
>all (1,2,3)意味着大于3

八：where子句中的相关子查询（外部查询和内部查询）
先看个例子

use adventureworks
drop table #MinOrderdates

select CustomerID, min(OrderDate)as orderdate
into #MinOrderdates
from Sales.SalesOrderHeader
group by CustomerID
order by CustomerID

select o.CustomerID,o.SalesOrderID,o.OrderDate
from Sales.SalesOrderHeader o
join #MinOrderdates t
on o.CustomerID = t.CustomerID
and o.OrderDate = t.orderdate
order by o.CustomerID

drop table #MinOrderdates

每个顾客在系统中的第一张定单的orderid 和orderdate
用到了临时表，执行了两次查询
看下面的例子

select o1.CustomerID,o1.SalesOrderID,o1.OrderDate
from Sales.SalesOrderHeader o1
where o1.OrderDate =
(
select min(o2.OrderDate)
from Sales.SalesOrderHeader o2
where o2.CustomerID = o1.CustomerID
)
order by CustomerID

执行了一次查询
注意内部查询对外部查询有一个显示的引用  o2.CustomerID = o1.CustomerID
当然外部查询也可以引用内部查询中的列
第二个例子

select c.LastName,
(
select min(OrderDate) 
from Sales.SalesOrderHeader o
where o.ContactID = c.ContactID
) as orderdate
from Person.Contact c 

 

九：派生表

先看例子：
 订购过某种特定产品的顾客列表

use AdventureWorks;
select c.FirstName,c.LastName
from Person.Contact As c
join Sales.SalesOrderHeader as soh
on c.ContactID = soh.ContactID
join Sales.SalesOrderDetail as sod
on soh.SalesOrderID = sod.SalesOrderID
join Production.Product as p
on sod.ProductID =p.ProductID
where p.Name = 'Minipump'

查找顾客的名字以及首次订购的日期

90行受影响
现在我们想查找即订购过Minipump又订购过AWC Logo Cap的顾客
如果最后是
where p.Name = 'Minipump' or p.Name = 'AWC Logo Cap'
2412行受影响
这样做是错误的
这样得到的结果是订购过Minipump和订购过AWC Logo Cap的顾客
下面来看我们的解决方法 

use AdventureWorks
select distinct c.FirstName,c.LastName
from Person.Contact As c
join 
(
select ContactID from Sales.SalesOrderHeader As soh
join Sales.SalesOrderDetail as sod
    on soh.SalesOrderID = sod.SalesOrderID
join Production.Product As p
    on sod.ProductID = p.ProductID
    where p.Name = 'Minipump' 
) pumps
on c.ContactID = pumps.ContactID
join 
(
select ContactID from Sales.SalesOrderHeader as soh
join Sales.SalesOrderDetail as sod
    on soh.SalesOrderID = sod.SalesOrderID
join Production.Product as p
    on sod.ProductID = p.ProductID
    where p.Name = 'AWC Logo Cap' 
) caps
on c.ContactID = caps.ContactID

把select出的内容当成一个表用
这就是派生表