0

Whats the right MySQL query with two LEFT JOINs between three tables?

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 )

Something like 

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
Murali Mopuru
  • 6,086
  • 5
  • 33
  • 51

1 Answers1

0

For performance I would do something like

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;
Ryan-Neal Mes
  • 6,003
  • 7
  • 52
  • 77
  • Thks Ryan. I've already considered above query which gave good performance benefit. I'm looking for query without sub queries which gives even more performance benefit. – Murali Mopuru Apr 17 '14 at 17:10