项目中用到的内连接,外连接查询,自己总结下吧,书上说的太笼统;
表结构如果是一对一,用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;
查询结果:
以上就是两者的区别:试项目需求而决定;