MySQL改写子查询成Join

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

有时用别的方式而不是子查询可以获得更高的性能 :

For example:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

改写:

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

查询:

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);

SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

改写:

SELECT table1.*

  FROM table1 LEFT JOIN table2 ON table1.id=table2.id

  WHERE table2.id IS NULL;

LEFT [OUTER] JOIN .