甲骨文和左外连接 [英] Oracle and Left Outer Join
本文介绍了甲骨文和左外连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我对这个由Hibernate生成的Oracle Sql感到困惑.数据库中只有一个用户，但是他们没有任何徽章，但是我正在对所有内容进行左外部联接.因此，用户应该每次都回来，无论他们是否拥有徽章.如果我删除这些行，那么它将拉回用户.难道不是要离开外部参加者，无论如何都要带回某人?
AND b4_.ACTIVE=1
AND B4_.STATUS='A'
AND UB2_.VISIBLE=1
and bl3_.ACTIVE=1
在SQL Developer中休眠Sql Ran
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空值是如何工作的 b4_.ACTIVE = 1将对这些记录无效，因为null<> 1
尝试按如下所示重组您的where块:
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)
解决此问题的另一种方法是将先决条件添加到左联接中.您可以像我在下面所做的那样进行操作，它将排除徽章，其中ACTIVE<> 1排除不正确的徽章，并且仍然返回所有用户.
left outer join
REWARD.BADGES b4_
on bl3_.BADGEID=b4_.ID
AND b4_.ACTIVE=1
I am confused about this Hibernate generated Oracle Sql. There is one user in the database, but they don't have any badges, but I am doing a left outer join on everything. So the user should come back everytime, regardless of them having a badge. If I remove these lines, then it pulls back the user. Isn't left outer join suppose to bring someone back no matter what?
AND b4_.ACTIVE=1
AND B4_.STATUS='A'
AND UB2_.VISIBLE=1
and bl3_.ACTIVE=1
Hibernate Sql Ran In Sql Developer
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'
解决方案
Those users do come back but they come back as null for all the columns returned in the tables where the left join didn't find a proper join.
Due to how ANSI nulls work b4_.ACTIVE=1 will be invalid for these records because null <> 1
Try restructuring your where block as follows:
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)
Another way to tackle this is add the prerequisites to your left joins. You can do like I did below and it will exclude the badges where ACTIVE <> 1 excluding bad badges and still return all users.
left outer join
REWARD.BADGES b4_
on bl3_.BADGEID=b4_.ID
AND b4_.ACTIVE=1
这篇关于甲骨文和左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
