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

Source: Internet
Author: User


--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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.