SQL exists使用

实际使用：

 <!--  通过查询条件查询营销活动审核列表-->
  <select id="findByParamsCampaign" parameterType="HashMap" resultType="java.util.HashMap">
  	select 
    	a.id as id,
    	a.code as code,
    	a.name as name,
    	a.classify as classify,
    	a.type as type,
    	a.shape as shape,
    	a.audit_status as auditStatus,
    	a.audit_remark as auditRemark,
    	a.time_end as timeEnd,
    	a.time_start as timeStart,
    	a.create_org as createOrg,
    	a.create_time as createTime,
    	a.create_user as createUser
    from 
    	crm_promotion a
    <where>
        delete_flag = 0
        <!-- 活动名称 -->
		<if test="name != null and name != ''">
			and a.name like CONCAT( '%',#{name,jdbcType=VARCHAR},'%' )
		</if>
		<!-- 活动编号 -->
		<if test="code != null and code !=''">
			and a.code like CONCAT( '%',#{code,jdbcType=VARCHAR},'%' )
		</if>
		  <!-- 活动类型 -->
		<if test="type != null and type != ''">
			and a.type = #{type,jdbcType=VARCHAR}
		</if>
		<!-- 参与方式 -->
		<if test="shape != null and shape !=''">
			and a.shape = #{shape,jdbcType=VARCHAR}
		</if>
		<!-- 活动开始时间 -->
		<if test="startTime != null and startTime != ''">
           	and a.time_start <![CDATA[>=]]> #{startTime,jdbcType=TIMESTAMP}
        </if>
		<!-- 活动结束时间 -->
        <if test="endTime != null and endTime != ''">
           	and a.time_end <![CDATA[<=]]> #{endTime,jdbcType=TIMESTAMP}
        </if>
		<!-- 创建开始时间 -->
		<if test="cStartTime != null and cStartTime != ''">
           	and a.create_time <![CDATA[>=]]> #{cStartTime,jdbcType=TIMESTAMP}
        </if>
		<!-- 创建结束时间 -->
        <if test="cEndTime != null and cEndTime != ''">
           	and a.create_time <![CDATA[<=]]> #{cEndTime,jdbcType=TIMESTAMP}
        </if>
        <!-- 创建人 -->
		<if test="createUser != null and createUser !=''">
			and a.create_user like CONCAT( '%',#{createUser,jdbcType=DECIMAL},'%' )
		</if>

<if test="channelId != null and channelId != ''">
				and exists (
					select 1
					from crm_promotion_channel b
					<where>
					b.promotion_id = a.id
						<if test="channelId != null">
							and b.channel_id = #{channelId,jdbcType=VARCHAR}
						</if>
					</where>
				)
			</if>
//如果副表有多个条件<if
			test="realName != null and realName != '') or (mobile != null and mobile != '')">
			and EXISTS
			(SELECT c.supplier_id from sup_supplier_contacts c
			WHERE c.supplier_id = a.id
			<if test="realName !=null and realName != ''">
				and c.real_name like
				concat('%',#{realName,jdbcType=VARCHAR},'%')
			</if>
			<if test="mobile !=null and mobile != ''">
				and c.mobile = #{mobile,jdbcType=VARCHAR}
			</if>
			)
		</if>
		
	</where>
	order by a.create_time DESC
  </select>

exists 方式查询 比 in 方式查询效率高，但in 可读性较好。建议尽可能使用exists方式，避免使用子查询，除非in 的参数为数值列表。