In SQL, the use of left join (left Outer Join) and inner join is the opposite of right join (right Outer Join.

Source: Internet
Author: User

The following is a 137-row SQL statement. If you understand this, I want to know about left join and inner join. (My personal opinion only)

The following is a piece of code:

Select num1, num2, num3, num4, num5, num6, num7, num8, num9, num10, area. areacode, area. areaname, num11

From
(Select area_code areacode, area_name areaname, Fa. grade_path grade, Fa. ID
From fk_area fa
Where fa. father_id = (select ID from fk_area where area_code = 330203008000) and fa. is_deleted = 0) Area

Left join
(Select MC. zfjcs num1, MC. area_id areaid
From jd_mbkh_config MC
Where MC. is_deleted = 0
And MC. Year = '000000') map1 on map1.areaid = area. ID
 
Left join
(Select count (SC. ID) num2, info. fouth_area as areacode
From jd_safe_check SC
Inner join jd_company C on C. ID = SC. par_id2
Inner join fk_user_info info on info. ID = SC. user_id
Where SC. is_deleted = 0
And SC. father_id is null
And C. is_deleted = 0
And C. is_recycle = 0
And SC. check_time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And SC. check_time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss ')
Group by info. fouth_area) MAP2 on map2.areacode = area. areacode

Left join
(Select count (sc2.id) num3, info. fouth_area as areacode from jd_safe_check SC2 inner join jd_company C on C. ID = sc2.par _ Id2
Inner join fk_user_info info on info. ID = sc2.user _ id
Where sc2.is _ deleted = 0
And sc2.father _ id is not null
And C. is_deleted = 0
And C. is_recycle = 0
And sc2.check _ time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And sc2.check _ time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss ')
Group by info. fouth_area) map3 on map3.areacode = area. areacode

Left join
(Select count (sc3.id) num4, info. fouth_area as areacode from jd_safe_check SC3
Inner join jd_company C on C. ID = sc3.par _ Id2
Inner join fk_user_info info on info. ID = sc3.user _ id
Where not exists
(Select 1 from jd_hidden_trouble HT where sc3.id = HT. par_id2)
And sc3.is _ deleted = 0
And sc3.father _ id is null
And C. is_deleted = 0
And C. is_recycle = 0
And sc3.check _ time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And sc3.check _ time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss ')
Group by info. fouth_area) map4 on map4.areacode = area. areacode

Left join
(Select count (HT. ID) num5, info. fouth_area as areacode from jd_hidden_trouble HT
Inner join fk_user_info info on info. ID = Ht. user_id
Inner join jd_safe_check Che on che. ID = Ht. par_id2
Inner join jd_company C on C. ID = Ht. par_id
Where Ht. is_deleted = 0
And Ht. government_time is not null
And (HT. trouble_type = 'trouble _ type_common'
And C. is_deleted = 0 and C. is_recycle = 0
And Che. check_time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And Che. check_time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss '))
Group by info. fouth_area) map5 on map5.areacode = area. areacode

Left join
(Select count (ht2.id) num6, info. fouth_area as areacode from jd_hidden_trouble ht2
Inner join fk_user_info info on info. ID = ht2.user _ id
Inner join jd_safe_check Che on che. ID = ht2.par _ Id2
Inner join jd_company C on C. ID = ht2.par _ id
Where ht2.is _ deleted = 0
And ht2.government _ time is null
And (ht2.trouble _ type = 'trouble _ type_common'
And C. is_deleted = 0 and C. is_recycle = 0
And Che. check_time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And Che. check_time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss '))
Group by info. fouth_area) map6 on map6.areacode = area. areacode

Left join
(Select count (HT. ID) num7, info. fouth_area as areacode from jd_hidden_trouble HT
Inner join fk_user_info info on info. ID = Ht. user_id
Inner join jd_safe_check Che on che. ID = Ht. par_id2
Inner join jd_company C on C. ID = Ht. par_id
Where Ht. is_deleted = 0
And Ht. government_time is not null
And (HT. trouble_type = 'trouble _ type_great'
And C. is_deleted = 0 and C. is_recycle = 0
And Che. check_time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And Che. check_time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss '))
Group by info. fouth_area) map7 on map7.areacode = area. areacode

Left join
(Select count (ht2.id) num8, info. fouth_area as areacode from jd_hidden_trouble ht2
Inner join fk_user_info info on info. ID = ht2.user _ id
Inner join jd_safe_check Che on che. ID = ht2.par _ Id2
Inner join jd_company C on C. ID = ht2.par _ id
Where ht2.is _ deleted = 0
And ht2.government _ time is null
And (ht2.trouble _ type = 'trouble _ type_great'
And C. is_deleted = 0 and C. is_recycle = 0
And Che. check_time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And Che. check_time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss '))
Group by info. fouth_area) map8 on map8.areacode = area. areacode

Left join
(Select count (HT. ID) num9, info. fouth_area as areacode from jd_hidden_trouble HT
Inner join fk_user_info info on info. ID = Ht. user_id
Inner join jd_safe_check Che on che. ID = Ht. par_id2
Inner join jd_company C on C. ID = Ht. par_id
Where Ht. is_deleted = 0
And Ht. government_time is not null
And (HT. trouble_type = 'trouble _ type_other'
And C. is_deleted = 0 and C. is_recycle = 0
And Che. check_time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And Che. check_time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss '))
Group by info. fouth_area) map9 on map9.areacode = area. areacode

Left join
(Select count (ht2.id) num10, info. fouth_area as areacode from jd_hidden_trouble ht2
Inner join fk_user_info info on info. ID = ht2.user _ id
Inner join jd_safe_check Che on che. ID = ht2.par _ Id2
Inner join jd_company C on C. ID = ht2.par _ id
Where ht2.is _ deleted = 0
And ht2.government _ time is null
And (ht2.trouble _ type = 'trouble _ type_other'
And C. is_deleted = 0 and C. is_recycle = 0
And Che. check_time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And Che. check_time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss '))
Group by info. fouth_area) map10 on map10.areacode = area. areacode

Left join
(Select count (C. ID) num11, fouth_area as areacode from jd_company C
Where C. is_deleted = 0
And C. is_recycle = 0
And C. create_time> = to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss ')
And C. create_time <= to_date ('2017-12-31 23:59:59 ', 'yyyy-mm-dd hh24: MI: ss ')
Group by C. fouth_area) map11 on map11.areacode = area. areacode
Order by grade

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.