UNION operations combine results from multiple tables while eliminating duplicate records:

<resultMap id="TransactionResult" type="com.example.Transaction">
  <id column="txn_id" property="id"/>
  <result column="order_ref" property="orderReference"/>
  <result column="channel" property="paymentChannel"/>
  <result column="amount" property="amount"/>
  <result column="date" property="transactionDate"/>
  <result column="type" property="transactionType"/>
  <result column="issue" property="errorType"/>
</resultMap>

<sql id="filters">
  <if test="txnType != null">
    AND type = #{txnType}
  </if>
  <if test="channelId != null">
    AND channel = #{channelId}
  </if>
</sql>

<select id="fetchTransactions" resultMap="TransactionResult">
  SELECT order_ref, date, amount, channel, type, 'no_match' AS issue
  FROM payment_transactions
  WHERE 1=1 <include refid="filters"/>
  UNION
  SELECT order_ref, date, amount, channel, type, 'missing' AS issue
  FROM bill_details
  WHERE 1=1 <include refid="filters"/>
  ORDER BY date DESC
  LIMIT #{limit} OFFSET #{offset}
</select>

Use UNION ALL to include all records without duplicate removal:

<select id="aggregateTransactions" resultType="map">
  SELECT SUM(amount) AS total, COUNT(1) AS count FROM (
    SELECT amount FROM payment_transactions
    WHERE channel = #{channelId} AND date BETWEEN #{start} AND #{end}
    UNION ALL
    SELECT amount FROM bill_details
    WHERE channel = #{channelId} AND date BETWEEN #{start} AND #{end}
  ) combined
</select>

Iterate through collections using foreach for IN clauses:

<update id="markAsVerified">
  UPDATE payment_transactions t1, bill_details t2
  SET t1.verified = 1, t2.verified = 1
  WHERE t1.order_ref = t2.order_ref
  AND t2.order_ref IN
  <foreach item="ref" collection="references" open="(" separator="," close=")">
    #{ref}
  </foreach>
  AND t1.date BETWEEN #{startDate} AND #{endDate}
</update>

Batch insert operations using list iteration:

<insert id="bulkInsertDetails">
  INSERT INTO bill_details (file_id, order_ref, external_id, type, channel, amount, date)
  VALUES
  <foreach collection="items" item="detail" separator=",">
    (#{detail.fileId}, #{detail.orderRef}, #{detail.externalId}, #{detail.type}, 
     #{detail.channel}, #{detail.amount}, #{detail.date})
  </foreach>
</insert>