left join on where
2016-06-02left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录，直接说实例。
1、select e.ename , d.dname from emp e left join dept d on e.empno=7369 and e.deptno=d.deptno;
2、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
3、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)
4、right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
5、实例
cid=$1
SELECT a.object_id,'"'||a.create_time ctime,a.creator,a.object_before,a.object_after,b.name FROM CRM_LOGGER PARTITION(PT_${cid}) a left join crm_contact PARTITION(PT_${cid}) b on a.object_id=b.id where a.OBJECT_NAME='Contact' AND a.CREATOR='btq' AND a.OBJECT_AFTER LIKE '%"mobile":null%' OR a.OBJECT_AFTER LIKE '%"phone":null%' order by b.create_time desc;
分类：数据库 | 标签： |