inner join,left join用法
来源:互联网 发布:文华财经看盘软件 编辑:程序博客网 时间:2024/05/21 14:46
项目中用到的内连接,外连接查询,自己总结下吧,书上说的太笼统;
表结构如果是一对一,用inner join ;如果是一对多,按照需求选择inner join,left join;
举个例子吧,比较下 他们的区别:
输入 sql语句: select id, amount from cd_financing_account where user_id=2762; 语句 1
select id,bid_amount,financing_account_id from cd_bid where bid_user_id=2762 ; 语句2
查询出来的两张表反映:
1 cd_bid 是cd_financing_account的映射,cd_bid是 cd_financing_account的子集(他们是一对多的关系)
项目需求:先有 cd_financing_account,后有 cd_bid(也就是 cd_bid基于前者)
2 cd_financing_account里面的主键“id”392,表cd_bid没有;
如果我们用 inner join 查询 ,id=392的查询不出来;
select a.id as financing_account_id ,a.amount,b.id as bid_id,b.bid_amount
from cd_financing_account a inner join cd_bid b on a.id=b.financing_account_id where a.user_id=2762;
查询结果:
其实这样的查询结果跟 :select a.id as financing_account_id ,a.amount,b.id as bid_id,b.bid_amount
from cd_financing_account a left join cd_bid b on a.id=b.financing_account_id where a.user_id=2762 and b.bid_id is not null;
select a.id as financing_account_id ,a.amount,b.id as bid_id,b.bid_amount
from cd_bid b left join cd_financing_account a on a.id=b.financing_account_id where a.user_id=2762 ;
如果我们用 left join查询 :
select a.id as financing_account_id ,a.amount,b.id as bid_id,b.bid_amount
from cd_financing_account a left join cd_bid b on a.id=b.financing_account_id where a.user_id=2762;
查询结果:
以上就是两者的区别:试项目需求而决定;
- inner join,left join用法
- Left join , Right Join, Inner Join 用法
- JOIN用法 INNER JOIN/LEFT JOIN/RIGHT JOIN/OUTER JOIN
- left join,right join,inner join,outer join的用法
- mysql left join,right join ,cross join inner join 用法
- SQL语句中Left join,right join,inner join用法
- mysql left join,right join,inner join用法举例
- SQL语句中Left join,right join,inner join用法
- inner join,left join,right join 三者的用法
- mysql left join,right join,inner join用法分析
- mysql left join,right join,inner join用法分析
- left join,right join,inner join用法分析
- mysql left join,right join,inner join用法分析
- sql中 inner join, left join, right join 用法
- oracle中left join,right join,inner join用法
- 简单记录mysql left join,right join,inner join用法
- mysql left join,right join,inner join用法分析
- mysql中left join,right join,inner join用法
- 一致性hash
- 深入研究Java类加载机制
- 一个简单的WPF MVVM实例
- centos中php5.2 fpm升级到php5.3
- POJ1852 Ant
- inner join,left join用法
- 自己动手开发音乐播放器《七》使用滑块实现音量和播放进度
- java欢迎你
- 【示例】《Java程序设计》第二周博文:第二周 计算圆面积
- JSON 入门指南
- 虚拟机下fedora无法上网的解决方法
- xp驱动和Win7驱动的区别
- 黑马程序员——IO流中的练习
- 程序中执行shell命令