MySQL:JOIN ON SELECT UNION 的条件

Question

我的 MySQL 查询的想法是创建某种类型的表的并集，例如供应商(例如零售商、批发商等)。找到供应商后，我想选择分配给订单的供应商的名称和地址。

我有一个加入:

SELECT o.*, v.*
FROM order o
INNER JOIN (
    SELECT 'retailer' AS type, r.id AS type_id, r.name, r.address FROM retailer AS r
    UNION ALL
    SELECT 'wholesaler' AS type, w.id AS type_id, w.name, w.address FROM wholesaler AS w
) AS v ON CONVERT(v.type AS utf8) = o.vendor_type AND CONVERT(v.type_id AS utf8) = o.vendor_type_id

问题 1:您认为这是最好的方法吗？

a) A vendor table would not be any better unless there is a name and address field to select in the vendor table; this seems redundant since the name and address are already in the vendor-specific table (e.g. retailer table)

b) Maybe a LEFT JOIN on each vendor-specific table would be best, though the NULL values everywhere would get kind of ugly. Performance-wise, yes, this is probably the best option. The UNION seems a bit bulky. Thoughts?

问题 2:我必须在 JOIN 条件上使用 CONVERT()，其中字段来自 UNION。 UNION 字段是 COERCIBLE，所有其他字段都是 latin_swedish。您认为在条件中使用 CONVERT() 会减慢我的 JOIN 速度吗？

Answer 1

以下内容可能更有效，因为它可以利用 retailer(id) 和 wholesaler(id) 上的索引:

SELECT o.*, coalesce(r.name, w.name) as name, coalesce(r.address, w.address) as address
FROM order o left outer join
     retailer r
     on o.type_id = r.id and o.type = 'retailer' left outer join
     wholesaler w
     on o.type_id = w.id and o.type = 'wholesaler';