left join多条件查询

SELECT
cust_id,
cust_name,
cust_user_id,
cust_create_id,
b.dict_item_name cust_source,
cust_linkman,
cust_phone,
cust_mobile,
cust_createtime
FROM
customer a
LEFT JOIN base_dict b ON a.cust_source = b.dict_id 
AND b.dict_type_code='002'

或者

SELECT
        cust_id,
        cust_name,
        cust_user_id,
        cust_create_id,
        b.dict_item_name cust_source，
        cust_linkman,
        cust_phone,
        cust_mobile,
        cust_createtime
        FROM
        customer a
        LEFT JOIN (
        SELECT
        dict_id,
        dict_item_name
        FROM
        base_dict
        WHERE
        dict_type_code = '002'
        ) b ON a.cust_source = b.dict_id

左连接查询会执行 from 后面的表的所有数据，left join后面的表的数据只会显示 on后面表达式成立的数据。

单条件left join，可以用where嵌套代替

SELECT c.cid,c.title,c.tags,k.cname
FROM t_contents c
LEFT JOIN t_category k on c.categories = k.kid

 

 mybatis的left join查询：

<resultMap id="BaseResultMap" type="com.shankai.bean.TbItemParam">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="item_cat_id" jdbcType="BIGINT" property="itemCatId" />
    <result column="created" jdbcType="TIMESTAMP" property="created" />
    <result column="updated" jdbcType="TIMESTAMP" property="updated" />
  </resultMap>
 
  <resultMap extends="BaseResultMap" id="ResultMapWithBLOBs" type="com.shankai.bean.TbItemParam">
    <result column="param_data" jdbcType="LONGVARCHAR" property="paramData" />------当有LONGVARCHAR类型时，使用两个resultMap

   <result column="name" property="itemCatName"/>------另一个表中的name字段

 </resultMap>
<select id="selectParam" resultMap="ResultMapWithBLOBs"> 
SELECT tp.id,tp.item_cat_id,tp.param_data,tp.created,tp.updated,tc.name
FROM tb_item_param tp 
LEFT JOIN tb_item_cat tc 
ON tp.item_cat_id=tc.id 

</select>