复杂的查询

表一:  

CREATE TABLE [dbo].[tb_contact](
 [id] [int] NULL,
 [RoleId] [int] NULL,
 [Address] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [Tel] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [EmployeeId] [int] NULL,

 [IsDel] [bit] NULL,

 [IsStop] [bit] NULL,

[InWorkDate] [datetime] NULL
) ON [PRIMARY]  

表二:  

 CREATE TABLE [dbo].[tb_employee](
 [id] [int] NULL,
 [UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [Age] [int] NULL,
 [RoleId] [int] NULL
) ON [PRIMARY]
表三:

CREATE TABLE [dbo].[tb_role](
 [id] [int] NULL,
 [RoleName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]  

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

select * from tb_employee e inner join tb_role r on e.RoleId = r.id join tb_contact c on e.id = c.EmployeeId

这是从一个表里查询了两次

注意from哪个表  哪个表的信息就在前面
其中e和r,c分别是表的别名，这里的别名和列的别名不同，不用写as
最后说一点inner join 是默认的连接类型   inner 关键字是可选的  

二:outer join
先看例子
select * from tb_employee e left outer join tb_contact c on e.id = c.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 id,username from tb_employee e union all select id,roleName from tb_role r
1：select 列表中字段的数量必须相同
2：字段的数据类型必须隐士兼容
3：返回的结果集的标头取自第一个查询
4：默认返回方式是distinct，union  alll返回全部的集合  

五：子查询返回单个值

先看例子：
declare @eid int
select @eid=min(id) from tb_employee
select distinct * from tb_employee e join tb_contact c on e.id = c.EmployeeId where e.id = @eid;

这是完全可行的，但是我们可以用另一种形式：
select distinct * from tb_employee e inner join tb_contact c on e.id = c.EmployeeId where e.id = (select min(id) from tb_employee);

select * from tb_employee e1 join tb_contact c on e1.id = c.EmployeeId where e1.InWorkDate = (select min(InWorkDate) from tb_employee);

---返回最早入职时间的员工信息
这就是子查询 
六：子查询返回多个值
接着看例子（子查询里返回多个值）

user TestDB
select * from tb_employee e join tb_contact c on e.id = c.EmployeeId where e.id in (select id from tb_employee where IsDel = 'true');  

select * from tb_employee e join tb_contact c on e.id = c.EmployeeId where e.IsDel = 'true';

再看一个例子（用子查询找出孤立的记录）
select * from tb_employee e join tb_contact c on e.id = c.EmployeeId where e.id not in (select id from tb_employee where IsDel = 'true');

select * from tb_employee e join tb_contact c on e.id = c.EmployeeId where e.IsDel <> 'true';
这个例子写的有点牵强
但是这里注意  not in 子查询得到的字段  不能有null直 存在，如果有  那么整个句子将返回空
细心的人大概看出来了，前面提到的两个子查询的例子几乎都可以用内联（join）的方式替换掉
出于性能上的考虑，我们应该首先选择联结的解决方案  而不是子查询