sql - 隐蔽左连接到简单连接

Question

我的 LEFT JOIN 子查询需要更长的时间。我如何将其转换为 JOIN？

SELECT  * FROM "TABLE_A" 
LEFT OUTER JOIN TABLE_B ON TABLE_A.mainid = TABLE_B.secondID       
LEFT JOIN (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
           SELECT secondID                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
           FROM  TABLE_C                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
           WHERE thirdID = 21                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
          ) Z ON TABLE_A.mainid = Z.secondID 
WHERE "TABLE_A"."TRUEID" = 9 
   AND ((TABLE_A.USERNO IN (23)) 

标签： sqljoinsubquery

Answer 1

首先，更简洁地编写查询：

SELECT *
FROM "TABLE_A" a LEFT JOIN
     TABLE_B b
     ON a.mainid = b.secondID LEFT JOIN    
     TABLE_C c
      ON a.mainid = c.secondID AND c.thirdID = 21 
WHERE a."TRUEID" = 9 AND a.USERNO IN (23);

然后考虑索引：

• Table_A(TRUEID, USERNO, mainid)
• Table_B(secondID)
• Table_C(secondID, thirdID)