mysql 按条件join_MySQL join – 根据条件选择表

我有三个表table1,table2和table2. table1.parent_id是table2或table3中记录的id,具体取决于table1.parent_type的值.现在我想将table1与table2和table3连接起来,具体取决于table1.parent_type的值.

这可以使用UNION完成.但还有其他方法吗？

这是我目前的查询

(SELECT c.*, a.title

FROM table1 c

LEFT OUTER JOIN table2 a

ON c.parent_id = a.id

WHERE c.parent_type = 0)

UNION (SELECT c.*, p.title

FROM table1 c

LEFT OUTER JOIN table3 p

ON c.parent_id = p.id

WHERE c.parent_type = 1)

ORDER BY id DESC

LIMIT 10

更新：这是另一种方法(From Dark Falcon的回复)

SELECT c.*, IF(c.parent_type = 0, a.title, p.title) as title FROM table1 c

LEFT OUTER JOIN table2 a ON c.parent_id = a.id AND c.parent_type = 0

LEFT OUTER JOIN table3 p ON c.parent_id = p.id AND c.parent_type = 1

WHERE a.id IS NOT NULL OR p.id IS NOT NULL ORDER BY id DESC LIMIT 10;

更新2：我使用查询分析器分析了查询.对于我的所有测试运行,多表连接速度提高了100多倍.