Oracle和Left Outer Join
Hibernate Sql Ran在Sql Developer中AND b4_.ACTIVE=1 AND B4_.STATUS='A' AND UB2_.VISIBLE=1 and bl3_.ACTIVE=1
select this_.ID as ID0_11_, this_.BIOGRAPHY as BIOGRAPHY0_11_, this_.DATECREATED as DATECREA3_0_11_, this_.EMAIL as EMAIL0_11_, this_.ENABLED as ENABLED0_11_, this_.FIRSTNAME as FIRSTNAME0_11_, this_.HIDECONNECTORS as HIDECONN7_0_11_, this_.HIDEEMAIL as HIDEEMAIL0_11_, this_.HIDENAME as HIDENAME0_11_, this_.LASTNAME as LASTNAME0_11_, this_.PASSWORD as PASSWORD0_11_, this_.SALT as SALT0_11_, this_.TITLE as TITLE0_11_, this_.USERNAME as USERNAME0_11_, this_.WARNINGS as WARNINGS0_11_, (SELECT COUNT(*) FROM Followers f WHERE f.followerid = this_.Id) as formula0_11_, assets6_.USERID as USERID13_, asset7_.ID as ASSETID13_, asset7_.ID as ID2_0_, asset7_.ACTIVE as ACTIVE2_0_, asset7_.DATECREATED as DATECREA3_2_0_, asset7_.DATEMODIFIED as DATEMODI4_2_0_, asset7_.DESCRIPTION as DESCRIPT5_2_0_, asset7_.FILENAME as FILENAME2_0_, asset7_.FILEPATH as FILEPATH2_0_, asset7_.TITLE as TITLE2_0_, asset7_.TYPE as TYPE2_0_, roles8_.USERID as USERID14_, role9_.ID as ROLEID14_, role9_.ID as ID1_1_, role9_.DISPLAYNAME as DISPLAYN2_1_1_, role9_.NAME as NAME1_1_, ub2_.USERID as USERID15_, ub2_.ID as ID15_, ub2_.ID as ID12_2_, ub2_.BADGELEVELID as BADGELEV5_12_2_, ub2_.DATECREATED as DATECREA2_12_2_, ub2_.ISMANUAL as ISMANUAL12_2_, ub2_.USERID as USERID12_2_, ub2_.VISIBLE as VISIBLE12_2_, bl3_.ID as ID9_3_, bl3_.ACTIVE as ACTIVE9_3_, bl3_.ASSETID as ASSETID9_3_, bl3_.BADGEID as BADGEID9_3_, bl3_.DATECREATED as DATECREA3_9_3_, bl3_.DATEMODIFIED as DATEMODI4_9_3_, bl3_.DESCRIPTION as DESCRIPT5_9_3_, bl3_.FILTERS as FILTERS9_3_, bl3_."ORDER" as ORDER7_9_3_, (SELECT COUNT(*) FROM USERBADGES ub WHERE ub.badgeLevelId = bl3_.Id) as formula1_3_, (bl3_."ORDER" - 1) as formula2_3_, asset12_.ID as ID2_4_, asset12_.ACTIVE as ACTIVE2_4_, asset12_.DATECREATED as DATECREA3_2_4_, asset12_.DATEMODIFIED as DATEMODI4_2_4_, asset12_.DESCRIPTION as DESCRIPT5_2_4_, asset12_.FILENAME as FILENAME2_4_, asset12_.FILEPATH as FILEPATH2_4_, asset12_.TITLE as TITLE2_4_, asset12_.TYPE as TYPE2_4_, b4_.ID as ID10_5_, b4_.ACTIVE as ACTIVE10_5_, b4_.DATECREATED as DATECREA3_10_5_, b4_.DATEMODIFIED as DATEMODI4_10_5_, b4_.DESCRIPTION as DESCRIPT5_10_5_, b4_.ENDDATE as ENDDATE10_5_, b4_.NAME as NAME10_5_, b4_.PUBLISHDETAILS as PUBLISHD8_10_5_, b4_.STARTDATE as STARTDATE10_5_, b4_.STATUS as STATUS10_5_, b4_.UPDATEOWNERID as UPDATEO11_10_5_, b4_.OWNERID as OWNERID10_5_, (SELECT COUNT(*) FROM BadgeLevels bl WHERE bl.badgeId = b4_.Id) as formula3_5_, (CASE WHEN (SELECT COUNT(*) FROM BadgeLevels bl WHERE bl.badgeId = b4_.Id) > 1 THEN 'Ladder' ELSE 'Single' END) as formula4_5_, user14_.ID as ID0_6_, user14_.BIOGRAPHY as BIOGRAPHY0_6_, user14_.DATECREATED as DATECREA3_0_6_, user14_.EMAIL as EMAIL0_6_, user14_.ENABLED as ENABLED0_6_, user14_.FIRSTNAME as FIRSTNAME0_6_, user14_.HIDECONNECTORS as HIDECONN7_0_6_, user14_.HIDEEMAIL as HIDEEMAIL0_6_, user14_.HIDENAME as HIDENAME0_6_, user14_.LASTNAME as LASTNAME0_6_, user14_.PASSWORD as PASSWORD0_6_, user14_.SALT as SALT0_6_, user14_.TITLE as TITLE0_6_, user14_.USERNAME as USERNAME0_6_, user14_.WARNINGS as WARNINGS0_6_, (SELECT COUNT(*) FROM Followers f WHERE f.followerid = user14_.Id) as formula0_6_, websites15_.USERID as USERID16_, website16_.ID as WEBSITEID16_, website16_.ID as ID6_7_, website16_.ACTIVE as ACTIVE6_7_, website16_.DATECREATED as DATECREA3_6_7_, website16_.DATEMODIFIED as DATEMODI4_6_7_, website16_.DESCRIPTION as DESCRIPT5_6_7_, website16_.NAME as NAME6_7_, website16_.URL as URL6_7_, uc1_.USERID as USERID17_, uc1_.ID as ID17_, uc1_.ID as ID17_8_, uc1_.ACTIVE as ACTIVE17_8_, uc1_.CONNECTORID as CONNECTO6_17_8_, uc1_.dateCreated as dateCrea3_17_8_, uc1_.dateModified as dateModi4_17_8_, uc1_.META as META17_8_, uc1_.USERID as USERID17_8_, connector18_.ID as ID18_9_, connector18_.ACTIVE as ACTIVE18_9_, connector18_.DATECREATED as DATECREA3_18_9_, connector18_.DISPLAYNAME as DISPLAYN4_18_9_, connector18_.NAME as NAME18_9_, user19_.ID as ID0_10_, user19_.BIOGRAPHY as BIOGRAPHY0_10_, user19_.DATECREATED as DATECREA3_0_10_, user19_.EMAIL as EMAIL0_10_, user19_.ENABLED as ENABLED0_10_, user19_.FIRSTNAME as FIRSTNAME0_10_, user19_.HIDECONNECTORS as HIDECONN7_0_10_, user19_.HIDEEMAIL as HIDEEMAIL0_10_, user19_.HIDENAME as HIDENAME0_10_, user19_.LASTNAME as LASTNAME0_10_, user19_.PASSWORD as PASSWORD0_10_, user19_.SALT as SALT0_10_, user19_.TITLE as TITLE0_10_, user19_.USERNAME as USERNAME0_10_, user19_.WARNINGS as WARNINGS0_10_, (SELECT COUNT(*) FROM Followers f WHERE f.followerid = user19_.Id) as formula0_10_ from 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 WHERE this_.ID=10100 and this_.ENABLED=1 AND UC1_.ACTIVE=1 AND UB2_.VISIBLE=1 and bl3_.ACTIVE=1 AND b4_.ACTIVE=1 AND B4_.STATUS='A'
这些用户确实回来了，但是对于左连接没有找到正确连接的表中返回的所有列，它们返回null。 由于ANSI nulls的工作原理 b4_.ACTIVE = 1对这些记录无效，因为null＆lt;> 1 尝试重构where块，如下所示：
解决此问题的另一种方法是向左连接添加先决条件。你可以像我在下面做的那样做，它将排除不包括坏徽章且仍然返回所有用户的ACTIVE＆lt;> 1的徽章。AND (b4_.ACTIVE=1 or b4_.ACTIVE is null) AND (B4_.STATUS='A' or B4_.STATUS is null) AND (UB2_.VISIBLE=1 or UB2_.VISIBLE is null) and (bl3_.ACTIVE=1 or bl3_.ACTIVE is null)
left outer join REWARD.BADGES b4_ on bl3_.BADGEID=b4_.ID AND b4_.ACTIVE=1