mysql - 工会运营商的替代

我必须从3个表中选择数据。我在第一个表和第二个表之间使用INNER JOIN从第二个表中获取另一个字段。但是如果不匹配，我必须将其替换为另一个1st table字段。我正在使用下面的查询，但是处理时间太长了。

SELECT tba.field, tba.field1, tba.field2 AS test FROM tablea tba
   WHERE tba.field NOT IN (SELECT ta.field FROM tablea ta INNER JOIN tableb tb ON ta.field = tb.field)
UNION
SELECT ta.field, ta.field1, tb.field2 AS test FROM tablea ta
     INNER JOIN tableb tb ON ta.field = tb.field
UNION
SELECT tc.field, tc.field1, tc.field2 AS test FROM tablec tc

SQL fiddle

最佳答案

这样的事情就足够了：

SELECT tba.field, tba.field1, COALESCE(tb.field2, tba.field2) AS test
FROM tablea tba
LEFT OUTER JOIN tableb tb ON  tba.field = tb.field
UNION
SELECT tc.field, tc.field1, tc.field2 AS test FROM tablec tc