CODEWITHSUNDEEP
sqloracle-databaseplsql
tulu matinee
tulu matinee

Reputation: 61

Replace IN clause with JOIN in Oracle

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

Answers (3)

Javaluca
Javaluca

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

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

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

Littlefoot
Littlefoot

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

Related Questions