Reputation: 61
I have read that JOIN is preferred over IN clause, so I am trying get rid of IN from my query
SELECT MSG.MSG_ID
FROM F1_MSG_OUT MSG
WHERE MSG.MSG_ID IN (
SELECT MSG_ID
FROM F1_MSG_OUT_ID MSGID2
WHERE MSGID2.MSG_ID = MSG.MSG_ID
AND TRIM(MSGID2._ID_TYPE_FLG) = :F6
AND TRIM(MSGID2._ID_VALUE) = :F3
)
AND MSG.MSG_ID IN (
SELECT MSG_ID
FROM F1_MSG_OUT_ID MSGID3
WHERE MSGID3.MSG_ID = MSG.MSG_ID
AND TRIM(MSGID3._ID_TYPE_FLG) = :F5
AND TRIM(MSGID3._ID_VALUE) = :F4
)
Upvotes: 0
Views: 169
Reputation: 857
You can try something like this
SELECT MSG.MSG_ID
FROM F1_MSG_OUT MSG
WHERE EXISTS ( SELECT MSGID2.MSG_ID
FROM F1_MSG_OUT_ID MSGID2
WHERE MSGID2.MSG_ID = MSG.MSG_ID
AND TRIM(MSGID2._ID_TYPE_FLG) = :F6
AND TRIM(MSGID2._ID_VALUE) = :F3
)
AND EXISTS ( SELECT MSGID3.MSG_ID
FROM F1_MSG_OUT_ID MSGID3
WHERE MSGID3.MSG_ID = MSG.MSG_ID
AND TRIM(MSGID3._ID_TYPE_FLG) = :F5
AND TRIM(MSGID3._ID_VALUE) = :F4
)
Upvotes: 1
Reputation: 15905
You already have answer for joining. You can also use exists
in place of in
line below
SELECT MSG.MSG_ID
FROM F1_MSG_OUT MSG
WHERE exists (
SELECT 1
FROM F1_MSG_OUT_ID MSGID2
WHERE MSGID2.MSG_ID = MSG.MSG_ID
AND TRIM(MSGID2._ID_TYPE_FLG) = :F6
AND TRIM(MSGID2._ID_VALUE) = :F3
)
AND exists IN (
SELECT 1
FROM F1_MSG_OUT_ID MSGID3
WHERE MSGID3.MSG_ID = MSG.MSG_ID
AND TRIM(MSGID3._ID_TYPE_FLG) = :F5
AND TRIM(MSGID3._ID_VALUE) = :F4
)
Upvotes: 1
Reputation: 143163
Looks like
select msg.msg_id
from f1_msg_out msg join f1_msg_out_id msgid2 on msgid2.msg_id = msg.msg_id
join f1_msg_out_id msgid3 on msgid3.msg_id = msg.msg_id
where trim(msgid2._id_type_flg) = :f6
and trim(msgid2._id_value) = :f3
and trim(msgid3._id_type_flg) = :f5
and trim(msgid3._id_value) = :f4;
Upvotes: 3