left join 条件不生效或者出现两表无法彼此外部连接

在我们进行left join左外连接查询时，例如：

select *
from TM_CHILDREN_INFO d
         left join TM_PERSON_INFO i
                on d.PERSON_ID = i.PERSON_ID
               and d.PERSON_ID is not null

查询的结果 and d.PERSON_ID is not null条件没有生效 原因是：ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行，如果你在on条件中去过滤条件是不会生效的

想要条件生效可以用 inner join 或者把条件写在where后面

select d.PERSON_ID
from TM_CHILDREN_INFO d
         inner join TM_PERSON_INFO i
                on d.PERSON_ID = i.PERSON_ID
               and d.PERSON_ID is not null
               
或者

select d.PERSON_ID
from TM_CHILDREN_INFO d
         left join TM_PERSON_INFO i
                on d.PERSON_ID = i.PERSON_ID
where d.PERSON_ID is not null