MySQL - 将 LEFT JOIN 替换为 NOT IN

Question

在三个表之间使用两个 LEFT JOIN 的正确 MySQL 查询是什么？

SELECT COUNT(1) FROM TABLE1 WHERE T1_ID NOT IN ( 
SELECT T2.T2_ID FROM TABLE2 T2 LEFT JOIN 
TABLE3 T3 ON T2.T2_ID=T3.T3_ID WHERE T3.T3_ID IS NULL )

类似

SELECT COUNT(1) FROM TABLE1 T1 LEFT JOIN TABLE2 T2 ON T1.T1_ID=T2.T2_ID 
LEFT JOIN TABLE3 T3 ON T2.T2_ID=T3.T3_ID WHERE T2.T2_ID IS NULL AND 
T3.T3_ID IS NULL

Answer 1

为了性能，我会做类似的事情

SELECT 
  COUNT(1) 
FROM 
  TABLE1 

  LEFT JOIN (
    SELECT 
      T2.T2_ID id
    FROM
      TABLE2 T2 

      LEFT JOIN TABLE3 T3 ON 
        T2.T2_ID=T3.T3_ID 
    WHERE 
      T3.T3_ID IS NULL
    ) t1 ON
    t1.id = table1.t1_id
WHERE
  t1 is null;