sql - Oracle and Left Outer Join -
i confused hibernate generated oracle sql. there 1 user in database, don't have badges, doing left outer join on everything. user should come everytime, regardless of them having badge. if remove these lines, pulls user. isn't left outer join suppose bring no matter what?
and b4_.active=1 , b4_.status='a' , ub2_.visible=1 , bl3_.active=1 hibernate sql ran in sql developer
select this_.id id0_11_, this_.biography biography0_11_, this_.datecreated datecrea3_0_11_, this_.email email0_11_, this_.enabled enabled0_11_, this_.firstname firstname0_11_, this_.hideconnectors hideconn7_0_11_, this_.hideemail hideemail0_11_, this_.hidename hidename0_11_, this_.lastname lastname0_11_, this_.password password0_11_, this_.salt salt0_11_, this_.title title0_11_, this_.username username0_11_, this_.warnings warnings0_11_, (select count(*) followers f f.followerid = this_.id) formula0_11_, assets6_.userid userid13_, asset7_.id assetid13_, asset7_.id id2_0_, asset7_.active active2_0_, asset7_.datecreated datecrea3_2_0_, asset7_.datemodified datemodi4_2_0_, asset7_.description descript5_2_0_, asset7_.filename filename2_0_, asset7_.filepath filepath2_0_, asset7_.title title2_0_, asset7_.type type2_0_, roles8_.userid userid14_, role9_.id roleid14_, role9_.id id1_1_, role9_.displayname displayn2_1_1_, role9_.name name1_1_, ub2_.userid userid15_, ub2_.id id15_, ub2_.id id12_2_, ub2_.badgelevelid badgelev5_12_2_, ub2_.datecreated datecrea2_12_2_, ub2_.ismanual ismanual12_2_, ub2_.userid userid12_2_, ub2_.visible visible12_2_, bl3_.id id9_3_, bl3_.active active9_3_, bl3_.assetid assetid9_3_, bl3_.badgeid badgeid9_3_, bl3_.datecreated datecrea3_9_3_, bl3_.datemodified datemodi4_9_3_, bl3_.description descript5_9_3_, bl3_.filters filters9_3_, bl3_."order" order7_9_3_, (select count(*) userbadges ub ub.badgelevelid = bl3_.id) formula1_3_, (bl3_."order" - 1) formula2_3_, asset12_.id id2_4_, asset12_.active active2_4_, asset12_.datecreated datecrea3_2_4_, asset12_.datemodified datemodi4_2_4_, asset12_.description descript5_2_4_, asset12_.filename filename2_4_, asset12_.filepath filepath2_4_, asset12_.title title2_4_, asset12_.type type2_4_, b4_.id id10_5_, b4_.active active10_5_, b4_.datecreated datecrea3_10_5_, b4_.datemodified datemodi4_10_5_, b4_.description descript5_10_5_, b4_.enddate enddate10_5_, b4_.name name10_5_, b4_.publishdetails publishd8_10_5_, b4_.startdate startdate10_5_, b4_.status status10_5_, b4_.updateownerid updateo11_10_5_, b4_.ownerid ownerid10_5_, (select count(*) badgelevels bl bl.badgeid = b4_.id) formula3_5_, (case when (select count(*) badgelevels bl bl.badgeid = b4_.id) > 1 'ladder' else 'single' end) formula4_5_, user14_.id id0_6_, user14_.biography biography0_6_, user14_.datecreated datecrea3_0_6_, user14_.email email0_6_, user14_.enabled enabled0_6_, user14_.firstname firstname0_6_, user14_.hideconnectors hideconn7_0_6_, user14_.hideemail hideemail0_6_, user14_.hidename hidename0_6_, user14_.lastname lastname0_6_, user14_.password password0_6_, user14_.salt salt0_6_, user14_.title title0_6_, user14_.username username0_6_, user14_.warnings warnings0_6_, (select count(*) followers f f.followerid = user14_.id) formula0_6_, websites15_.userid userid16_, website16_.id websiteid16_, website16_.id id6_7_, website16_.active active6_7_, website16_.datecreated datecrea3_6_7_, website16_.datemodified datemodi4_6_7_, website16_.description descript5_6_7_, website16_.name name6_7_, website16_.url url6_7_, uc1_.userid userid17_, uc1_.id id17_, uc1_.id id17_8_, uc1_.active active17_8_, uc1_.connectorid connecto6_17_8_, uc1_.datecreated datecrea3_17_8_, uc1_.datemodified datemodi4_17_8_, uc1_.meta meta17_8_, uc1_.userid userid17_8_, connector18_.id id18_9_, connector18_.active active18_9_, connector18_.datecreated datecrea3_18_9_, connector18_.displayname displayn4_18_9_, connector18_.name name18_9_, user19_.id id0_10_, user19_.biography biography0_10_, user19_.datecreated datecrea3_0_10_, user19_.email email0_10_, user19_.enabled enabled0_10_, user19_.firstname firstname0_10_, user19_.hideconnectors hideconn7_0_10_, user19_.hideemail hideemail0_10_, user19_.hidename hidename0_10_, user19_.lastname lastname0_10_, user19_.password password0_10_, user19_.salt salt0_10_, user19_.title title0_10_, user19_.username username0_10_, user19_.warnings warnings0_10_, (select count(*) followers f f.followerid = user19_.id) formula0_10_ reward.users this_ left outer join userassets assets6_ on this_.id=assets6_.userid left outer join reward.assets asset7_ on assets6_.assetid=asset7_.id left outer join userroles roles8_ on this_.id=roles8_.userid left outer join reward.roles role9_ on roles8_.roleid=role9_.id left outer join reward.userbadges ub2_ on this_.id=ub2_.userid left outer join reward.badgelevels bl3_ on ub2_.badgelevelid=bl3_.id left outer join reward.assets asset12_ on bl3_.assetid=asset12_.id left outer join reward.badges b4_ on bl3_.badgeid=b4_.id left outer join reward.users user14_ on ub2_.userid=user14_.id left outer join userwebsites websites15_ on user14_.id=websites15_.userid left outer join reward.websites website16_ on websites15_.websiteid=website16_.id left outer join reward.userconnectors uc1_ on this_.id=uc1_.userid left outer join reward.connectors connector18_ on uc1_.connectorid=connector18_.id left outer join reward.users user19_ on uc1_.userid=user19_.id this_.id=10100 , this_.enabled=1 , uc1_.active=1 , ub2_.visible=1 , bl3_.active=1 , b4_.active=1 , b4_.status='a'
those users come come null columns returned in tables left join didn't find proper join.
due how ansi nulls work b4_.active=1 invalid these records because null <> 1
try restructuring block follows:
and (b4_.active=1 or b4_.active null) , (b4_.status='a' or b4_.status null) , (ub2_.visible=1 or ub2_.visible null) , (bl3_.active=1 or bl3_.active null) another way tackle add prerequisites left joins. can did below , exclude badges active <> 1 excluding bad badges , still return users.
left outer join reward.badges b4_ on bl3_.badgeid=b4_.id , b4_.active=1
Comments
Post a Comment