JOIN 语法中的 MySQL 相关子查询

MySQL correlated subquery in JOIN syntax

提问by Taylor Gerring

I would like to provide a WHERE condition on an inner query by specifying innertable.id = outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' in 'where clause'". Is this type of query possible?

我想通过指定innertable.id = externaltable.id 为内部查询提供WHERE 条件。但是，MySQL (5.0.45) 报告“'where 子句'中的未知列'outertable.id'”。这种类型的查询可能吗？

The inner query is pivoting rows to columns using a GROUP BY. This could be entirely be performed in the outer query, but would possibly incur additional overhead due to the extra joins.

内部查询使用 GROUP BY 将行转换为列。这可以完全在外部查询中执行，但可能会由于额外的连接而产生额外的开销。

Alternatively, I can leave off the WHERE condition in the inner query and instead specify an ON outertable.id = innerquery.id, but it would then fetch the entire inner query rowset to join again the outer, which is inefficient.

或者，我可以不使用内部查询中的 WHERE 条件，而是指定 ON externaltable.id = innerquery.id，但它随后会获取整个内部查询行集以再次连接外部查询，这是低效的。

The actual SQL appears below:

实际的 SQL 如下所示：

select t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email, tp.subject, tp.contents, a.PhoneNumber, a.Location, a.Extension, a.BusinessUnit, a.Department
from swtickets t
inner join swticketposts tp on t.ticketid = tp.ticketid
inner join swusers u on t.userid = u.userid
left join
  (
  select
  cfv.typeid,
  min(case cfv.customfieldid when 1 then cfv.fieldvalue end) as 'PhoneNumber',
  min(case cfv.customfieldid when 3 then cfv.fieldvalue end) as 'Location',
  min(case cfv.customfieldid when 5 then cfv.fieldvalue end) as 'Extension',
  min(case cfv.customfieldid when 8 then cfv.fieldvalue end) as 'BusinessUnit',
  min(case cfv.customfieldid when 9 then cfv.fieldvalue end) as 'Department'
  from swcustomfieldvalues cfv
  where cfv.typeid = t.ticketid
  group by cfv.typeid
  ) as a on 1 = 1
where t.ticketid = 2458;

回答by Bill Karwin

The answer to your question is no, it is not possible to reference correlation names as you are doing. The derived table is produced by your inner query before the outer query starts evaluating joins. So the correlation names like t, tp, and uare not available to the inner query.

您的问题的答案是否定的，无法像您一样引用相关名称。派生表是在外部查询开始评估连接之前由内部查询生成的。因此，诸如t、tp和 之类的相关名称u不可用于内部查询。

To solve this, I'd recommend using the same constant integer value in the inner query, and then join the derived table in the outer query using a real condition instead of 1=1.

为了解决这个问题，我建议在内部查询中使用相同的常量整数值，然后在外部查询中使用真实条件而不是1=1.

SELECT t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email,
  tp.subject, tp.contents, a.PhoneNumber, a.Location, a.Extension,
  a.BusinessUnit, a.Department
FROM swtickets t
 INNER JOIN swticketposts tp ON (t.ticketid = tp.ticketid)
 INNER JOIN swusers u ON (t.userid = u.userid)
 LEFT OUTER JOIN (
  SELECT cfv.typeid,
    MIN(CASE cfv.customfieldid WHEN 1 THEN cfv.fieldvalue END) AS 'PhoneNumber',
    MIN(CASE cfv.customfieldid WHEN 3 THEN cfv.fieldvalue END) AS 'Location',
    MIN(CASE cfv.customfieldid WHEN 5 THEN cfv.fieldvalue END) AS 'Extension',
    MIN(CASE cfv.customfieldid WHEN 8 THEN cfv.fieldvalue END) AS 'BusinessUnit',
    MIN(CASE cfv.customfieldid WHEN 9 THEN cfv.fieldvalue END) AS 'Department'
  FROM swcustomfieldvalues cfv
  WHERE cfv.typeid = 2458
  GROUP BY cfv.typeid
  ) AS a ON (a.typeid = t.ticketid)
WHERE t.ticketid = 2458;

回答by Bill Karwin

You're using the Entity-Attribute-Value design, and there's ultimately no way to make this scalable if you try to generate conventional result sets. Don't try to do this in one query.

您正在使用实体-属性-值设计，如果您尝试生成传统的结果集，则最终无法使其具有可扩展性。不要尝试在一个查询中执行此操作。

Instead, query your normalized tables first:

相反，首先查询您的规范化表：

SELECT t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email, 
  tp.subject, tp.contents
FROM swtickets t
 INNER JOIN swticketposts tp ON (t.ticketid = tp.ticketid)
 INNER JOIN swusers u ON (t.userid = u.userid)
WHERE t.ticketid = 2458;

Then query your custom fields, with the result on multiple rows of the result set:

然后查询您的自定义字段，结果集的多行：

SELECT cfv.customfieldid, cfv.fieldvalue
FROM swcustomfieldvalues cfv
WHERE cfv.typeid = 2458;

You'll get multiple rows in the result set, one row for each custom field:

您将在结果集中获得多行，每个自定义字段占一行：

+---------------+--------------+
| customfieldid | fieldvalue   |
+---------------+--------------+
|             1 | 415-555-1234 |
|             3 | Third office |
|             5 | 123          |
|             8 | Support      |
|             9 | Engineering  |
+---------------+--------------+

You then need to write application code to map the result-set fields to the application object fields, in a loop.

然后，您需要编写应用程序代码，以循环方式将结果集字段映射到应用程序对象字段。

Using an Entity-Attribute-Value table in this way is more scalable both in terms of performance and code maintenance.

以这种方式使用实体-属性-值表在性能和代码维护方面都更具可扩展性。

回答by Tom H

I would write it with multiple joins. When you say that it "would possibly incur additional overhead" that tells me that you haven't tested it to be sure. If you have decent indexes the joins should be pretty trivial.

我会用多个连接来编写它。当你说它“可能会产生额外的开销”时，这告诉我你还没有测试过它。如果您有不错的索引，则连接应该非常简单。

This also shows just one of the pitfalls of the generic "hold everything" table design pattern.

这也仅显示了通用“容纳一切”表设计模式的缺陷之一。

回答by AJM

My suggestion was going to be what you ruled out on the grounds of efficiency. E.g. leaving out the where clause and using a join (as per t.ticketid = a.ticketid)

我的建议将是你以效率为由排除的。例如，省略 where 子句并使用连接（根据 t.ticketid = a.ticketid）

Have you been able to prove your thoughts on inefficiency by some concrete examples? I know what you are saying but whatever method you use every row in the outer query is being joined to every row in the inner query so depending on the execution plan it may not be as inefficient as you suspect?

你是否能够通过一些具体的例子来证明你对低效率的看法？我知道您在说什么，但是无论您使用外部查询中的每一行的任何方法都连接到内部查询中的每一行，因此根据执行计划，它可能不像您怀疑的那样低效？

回答by chaos

I imagine the problem is 'cfv.typeid = t.ticketid' then? My thinking about that would be that, while MySQL supports correlated subqueries, what you're trying to do seems like it could fail in a join because the 'inner' query isn't really 'inside' the rest of the query like it is in a WHERE clause. But it looks like you could just take the where clause out of the subquery and make your join condition on a.typeid = t.ticketid.

我想问题是'cfv.typeid = t.ticketid'呢？我的想法是，虽然 MySQL 支持相关子查询，但您尝试做的事情似乎可能会在连接中失败，因为“内部”查询并不是真正的“内部”查询的其余部分，就像它是在 WHERE 子句中。但看起来您可以从子查询中取出 where 子句，并在 a.typeid = t.ticketid 上设置连接条件。