Talk about join, left JOIN, right join, full join-version 2 in Oracle

--1.left Join left table is the primary table, left table returns all data, and the right table only returns data that matches the left table.
Select T1.fpdm,t1.fphm, T1.ZJR,T1.ZJSJ,T1.ZJJX,T1.ZJJE,T1.ZFLX,T1.ZFID,T2.FPDM,T2.FPHM,T2.ZFLX from YW_ZJFPJL t1
Left join XXDZMX T2 on t2.fpdm| | t2.fphm=t1.fpdm| | T1.fphm

Select T1.fpdm,t1.fphm, T1.ZJR,T1.ZJSJ,T1.ZJJX,T1.ZJJE,T1.ZFLX,T1.ZFID,T2.FPDM,T2.FPHM,T2.ZFLX from YW_ZJFPJL T1, XXDZMX T2 where T2.FPHM=T1.FPHM (+)

--2.right Join right table is the main table, the right table returns all data, and the left table only returns data that matches the right table.
Select T1.fpdm,t1.fphm, T1.ZJR,T1.ZJSJ,T1.ZJJX,T1.ZJJE,T1.ZFLX,T1.ZFID,T2.FPDM,T2.FPHM,T2.ZFLX from YW_ZJFPJL t1
Right join XXDZMX T2 on t2.fpdm| | t2.fphm=t1.fpdm| | T1.fphm

--3.inner join returns only two table-matched data
Select T1.fpdm,t1.fphm, T1.zjr,t1.zjsj,t1.zjjx,t1.zjje,t1.zflx,t1.zfid, T2.FPHM,T2.FPDM,T2.ZFLX from YW_ZJFPJL t1
Inner join XXDZMX T2 on t2.fphm| | T2.FPDM = t1.fphm| | T1.fpdm
Where T2.FPHM is not null and t2.zflx= ' 0 ' and t1.zflx= ' 0 ' and To_char (T1.ZJSJ, ' yyyy-mm ') = ' 2016-10 ' and substr (T2.DZYF, 0, 4) | | ' -' | | SUBSTR (T2.DZYF, 5, 2) = ' 2016-10 ';

--4.full Join
--4.1 all result set 106
Select T1.fpdm,t1.fphm, T1.ZJR,T1.ZJSJ,T1.ZJJX,T1.ZJJE,T1.ZFLX,T1.ZFID,T2.FPDM,T2.FPHM,T2.ZFLX from YW_ZJFPJL t1
Full join XXDZMX T2 on T2.FPDM=T1.FPDM and T2.FPHM=T1.FPHM

--4.2 right, there's no 10-1=9 on the left.
Select T1.fpdm,t1.fphm, t1.zjr,t1.zjsj,t1.zjjx,t1.zjje,t1.zflx,t1.zfid,t2.fphm,t2.fpdm,t2.zflx,t2.dzyf from Yw_ ZJFPJL T1
Full join XXDZMX T2 on t2.fphm| | T2.FPDM = t1.fphm| | T1.fpdm
Where T2.FPHM is not null and T2.ZFLX =0 and substr (t2.dzyf,1, 4) | | -' | | substr (t2.dzyf,5,2) = ' 2016-10 '

--4.3 left, there's no 97-1=96 on the right.
Select T1.fpdm,t1.fphm, T1.ZJR,T1.ZJSJ,T1.ZJJX,T1.ZJJE,T1.ZFLX,T1.ZFID,T2.FPHM,T2.FPDM,T2.ZFLX from YW_ZJFPJL t1
Full join XXDZMX T2 on t2.fphm| | T2.FPDM = t1.fphm| | T1.fpdm
Where (t1.zflx= ' 0 ' and To_char (T1.ZJSJ, ' yyyy-mm ') = ' 2016-10 ');

--4.4 left has right no right there's left no (97-1) + (10-1) =105
Select T1.fpdm,t1.fphm, t1.zjr,t1.zjsj,t1.zjjx,t1.zjje,t1.zflx,t1.zfid,t2.fphm,t2.fpdm,t2.zflx,t2.dzyf from Yw_ ZJFPJL T1
Full join XXDZMX T2 on t2.fphm| | T2.FPDM = t1.fphm| | T1.fpdm
Where (T2.FPHM is not null and T2.ZFLX = ' 0 ' and substr (t2.dzyf,1, 4) | | -' | | substr (t2.dzyf,5,2) = ' 2016-10 ') or (t1.zflx= ' 0 ' and To_char (T1.ZJSJ, ' yyyy-mm ') = ' 2016-10 ');

Talk about join, left JOIN, right join, full join-version 2 in Oracle