mysql 相关子查询

-- 错误的子查询示例
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

-- 优化后的连接操作示例
SELECT table1.* FROM table1 JOIN table2 ON table1.id = table2.id;

-- 错误的子查询示例
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 WHERE status = 'active');

-- 优化后的连接操作示例
SELECT table1.* FROM table1 JOIN table2 ON table1.id = table2.id WHERE table2.status = 'active';

-- 错误的深层嵌套子查询示例
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 WHERE status IN (SELECT status FROM table3 WHERE type = 'A'));

-- 优化后的临时表示例
CREATE TEMPORARY TABLE temp_table AS SELECT status FROM table3 WHERE type = 'A';
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 WHERE status IN (SELECT status FROM temp_table));
DROP TEMPORARY TABLE temp_table;