高级查询和表连接

积累来的sql的高级查询和表连接,认为很有用

--高级查询和表连接
if object_id(N'vvtest')is not null
drop table vvtest
create table vvtest (姓名 varchar(50),性别 varchar(2),年龄 int)
insert vvtest
select 'aa','男','23' union all
select 'bb','男','26' union all
select 'cc','女','23' union all
select 'dd','男','23' 
create table vvtest1 (姓名 varchar(50),性别 varchar(2),年龄 int)
insert vvtest1
select 'aa','男','23' union all
select 'bb','男','26' union all
select 'cc1','女','23' union all
select 'dd1','男','23' union all
select 'ee1','男','23'
 
select * from vvtest
select * from vvtest1 order by 姓名 desc

---union,except,intersect 的高级查询
select * from vvtest union select * from vvtest1(两表所有数据,去重复)
select * from vvtest except select * from vvtest1(前表有,后表没有的记录)
select * from vvtest intersect select * from vvtest1(前表和后表中都有记录)
---表连接
select * from vvtest a inner join vvtest1 b on a.姓名=b.姓名(符合连接条件的所有记录)
select * from vvtest a left join vvtest1 b on a.姓名=b.姓名(前表所有行,后表无匹配的列为null)
select * from vvtest a right join vvtest1 b on a.姓名=b.姓名(后表所有行,前表无匹配的列为null)
select * from vvtest a full join vvtest1 b on a.姓名=b.姓名(前表后表的所有行,无匹配的为null)