Reasons for using left join faster than direct use of where

Multiple tables using the LEFT join is simply querying all the data in the main table, and the other tables query only one of the tables that match the criteria, so the speed is very fast, and the multi-table uses where inline to isolate all of the table's data and then perform the match, so the speed is very slow.

Use the left JOIN to be aware of which table is the primary table, and if the primary table cannot be determined, which table is the most queried field, and which table is the primary table.

Examples are as follows:

Using the LEFT JOIN, the same amount of data, less than 1 seconds!

SELECT A.projectno,max (a.projectname) Projectname,max (A.projectmoney) Projectmoney,
Max (a.projectlimityear) Projectlimityear,max (a.monthcharge) Monthcharge,
Max (C.orgname) Orgname,max (d.businesstypename) Businesstypename,max (e.name) name,
Max (B.dicvalue) Dicvalue,
MIN (CONVERT (varchar), DATEADD ("Day", JBL.) Delaydays,jbl. Reporttime) as period,
Max (F.fiveleveltype) Fiveleveltype,max (a.projectinfoid) projectinfoid,
Max (G.flowrunid) Flowrunid
From
(Select Projectinfoid,projectno,status,orgid,typeid,useraid,isdelete,projectname,projectmoney,projectlimityear, Monthcharge from Jt_biz_projectinfo) a
Left JOIN
(select b.* from (the Select MAX (ID) ID, projectno from jt_biz_lecture Group by Projectno) A,
Jt_biz_lecture b where a.id=b.id) f
On A.projectno=f.projectno
Left JOIN
(select Dicid,isdelete,dicvalue,diccode from jt_base_dictionary) b
On A.status=b.dicid
Left JOIN
(select Projectno,delaydays,reporttime from Jt_biz_lecturetemp) JBL
On A.projectno=jbl.projectno
Left JOIN
(select Isdelete,orgname,orgid from jt_base_org) c
On A.orgid=c.orgid
Left JOIN
(select Id,businesstypename from D_businesstype) d
On A.typeid=d.id
Left JOIN
(select Userinfoid,isdelete,name,username from Jt_base_userinfo) E
On a.useraid=e.userinfoid
Left JOIN
(select Userid,postid,orgids from jt_base_post_userorg) j
On j.userid=e.userinfoid
Left JOIN
(select PostID from Jt_base_post) k
On J.postid=k.postid
Left JOIN
(select Flowrunid,projectid from Jt_flow_run) g
On A.projectinfoid=g.projectid
Left JOIN
(select Flowrunid from Jt_flow_run_prcs) h
On g.flowrunid = H.flowrunid
Left JOIN
(select Orgid,orgname from jt_base_org) l
On A.orgid=l.orgid
WHERE A.status in (82,83,84) and a.isdelete=0
and B.isdelete=0 and C.isdelete=0 and e.isdelete=0
and (L.orgid in (null) or e.username= ' CHENQF ') group by A.projectno;
--to speed up the comparison of query speed

Do not use the left JOIN, the same amount of data, the time is about 50 seconds look!
Select A.projectno,max (a.projectname) Projectname,max (A.projectmoney) Projectmoney,
Max (a.projectlimityear) Projectlimityear,max (a.monthcharge) Monthcharge,
Max (C.orgname) Orgname,max (d.businesstypename) Businesstypename,max (e.name) name,
Max (B.dicvalue) Dicvalue,
MIN (CONVERT (varchar), DATEADD ("Day", JBL.) Delaydays,jbl. Reporttime) as period,
Max (F.fiveleveltype) Fiveleveltype,max (a.projectinfoid) projectinfoid,
Max (G.flowrunid) Flowrunid
From (select Dicid,isdelete,dicvalue,diccode from jt_base_dictionary) b,
(select Isdelete,orgname,orgid from jt_base_org) C,
(select Userinfoid,isdelete,name,username from Jt_base_userinfo) e,
(select Id,businesstypename from D_businesstype) d,
(select Flowrunid,projectid from Jt_flow_run) G,
(select Flowrunid from Jt_flow_run_prcs) H,
(select Userid,postid,orgids from jt_base_post_userorg) J,
(select PostID from Jt_base_post) K,
(select Orgid,orgname from jt_base_org) L,
(select Projectno,delaydays,reporttime from Jt_biz_lecturetemp) JBL,
(Select Projectinfoid,projectno,status,orgid,typeid,useraid,isdelete,projectname,projectmoney,projectlimityear, Monthcharge from Jt_biz_projectinfo) a
Left Join
(select b.* from (the Select MAX (ID) ID, projectno from jt_biz_lecture Group by Projectno) A,
Jt_biz_lecture b where a.id=b.id) f
On A.projectno=f.projectno where A.status=b.dicid and A.status in (82,83,84)
and A.projectno=jbl.projectno and A.orgid=c.orgid and A.typeid=d.id
and A.useraid=e.userinfoid and J.userid=e.userinfoid and a.isdelete=0
and B.isdelete=0 and C.isdelete=0 and e.isdelete=0 and G.flowrunid = H.flowrunid
and (L.orgid in (null) or e.username= ' CHENQF ')
Group BY A.projectno;

Reasons for using left join faster than direct use of where