final String sqlString = "SELECT rt.currentId as contentId, rt.draftprevver as latestVersionid, rt.RELATIONID as relationId, " +
"CASE WHEN lt.LASTMODDATE < rt.touchdate THEN rt.CONTENT_STATUS ELSE lt.CONTENT_STATUS END as contentStatus, rt.touchdate as lastModifiedDate " +
"FROM CONTENT lt " +
"JOIN ( " +
"SELECT ucr.LASTMODDATE as touchdate, ucr.RELATIONID, c.LASTMODDATE, c.CONTENTID, " +
"c.TITLE, c.CONTENT_STATUS, coalesce(c.PREVVER, c.CONTENTID) as currentId, c.PREVVER as draftprevver " +
"FROM CONTENT c " +
"LEFT JOIN USERCONTENT_RELATION ucr " +
"ON c.CONTENTID = ucr.TARGETCONTENTID " +
"WHERE ucr.RELATIONNAME = 'touched' " +
"AND ucr.LASTMODIFIER = :userKey " +
" and c.CONTENTTYPE in (:contentTypes) " +
" and c.CONTENT_STATUS in (:contentStatuses) " +
") rt " +
"on lt.CONTENTID = rt.currentId " +
"WHERE lt.CONTENTID in (:contentIds)";

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: lt
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
partitions: NULL
type: index
possible_keys: NULL
key: c_si_ct_pv_cs_cd_idx
key_len: 1559
ref: NULL
rows: 5356462
filtered: 4.00
Extra: Using where; Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ucr
partitions: NULL
type: ref
possible_keys: u2c_relation_unique,relation_u2c_targetcontent_idx
key: relation_u2c_targetcontent_idx
key_len: 8
ref: confluencedb.c.CONTENTID
rows: 1
filtered: 4.92
Extra: Using where
3 rows in set, 1 warning (0.00 sec)

and (c.PREVVER in (:contentIds) or c.CONTENTID in (:contentIds))