mssql联合查询
时间:2022-03-14 03:47
table1表结构(用户名,密码)
userid | username | password | userid | score | level |
1 | user1 | pwd1 | 1 | 20 | 3 |
第二:左联(left outer join)显示左表中的所有行
select * from table1 t1 left outer join table2 t2 on t1.userid=t2.userid
运行结果:
userid | username | password | userid | score | level |
1 | user1 | pwd1 | 1 | 20 | 3 |
2 | user2 | pwd2 | null | null | null |
第三:右联(right outer join)显示右表中的所有行
select * from table1 t1 right outer join table2 t2 on t1.userid=t2.userid
运行结果:
userid | username | password | userid | score | level |
1 | user1 | pwd1 | 1 | 20 | 3 |
null | null | null | 3 | 50 | 6 |
第四:全联(full outer join)显示两边表中所有行
select * from table1 t1 full outer join table2 t2 on t1.userid=t2.userid
运行结果:
userid | username | password | userid | score | level |
1 | user1 | pwd1 | 1 | 20 | 3 |
2 | user2 | pwd2 | null | null | null |
null | null | null | 3 | 50 | 6 |
总结,关于联合查询,本人已测试过.效率的确比较高,4种联合方式如果可以灵活使用,基本上复杂的语句结构也会简单起来.这4种方式是:
Inner join left outer join right outer join full outer join