将自然连接与Where操作一起使用 [英] Using Natural Join with Where operation

问题描述

我有一个查询，该查询已在phpMyAdmin中使用，并且运行良好，但是我将数据库迁移到其他服务器上，现在使用SQL * Plus执行查询.查询现在正在生成

I have a query that I've been using in phpMyAdmin and it's been working perfectly however I migrated my database over to different server and I am now using SQL*Plus to perform my queries. The query is now generating

ERROR at line 10:
ORA-25155: column used in NATURAL join cannot have qualifier

这是我的查询:

SELECT Block FROM ( 
   SELECT CardId, Block
   FROM Contains
   GROUP BY Block
   UNION
   SELECT CardId, Block
   FROM Contains
   NATURAL JOIN
      (SELECT CardId
       FROM Costs
       NATURAL JOIN
          (SELECT Id
           FROM Card
           WHERE RarityId IN (SELECT Id FROM Rarity WHERE RarityType='Legendary')
          ) rc
       WHERE Costs.CardId = rc.Id
       AND ManaCardId IN (SELECT Id FROM ManaCard WHERE ManaColor='Red')
      ) tmp
   WHERE Contains.CardId = tmp.CardId
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

由于不喜欢我的限定词，有没有其他方法可以在没有自然联接的情况下获取查询?我已经尝试过对Join和Inner Join使用相同的功能，但是都无法正常工作.

Since it is not liking my qualifiers is there a different way to get the query without natural join? I've tried using the same thing with Join and Inner Join but both are not working.

推荐答案

第1部分.

自然联接时，自然联接"的列将失去表别名，例如:

When you natural join, the columns that have been "naturally joined" lose their table aliases, so for example:

SELECT CardId, Block
FROM Contains
NATURAL JOIN
   (SELECT CardId FROM ...
   ) tmp
WHERE Contains.CardId = tmp.CardId

此处，自然联接的两侧共享一个列CardId，因此您不能引用该列的表别名，例如:

Here, both sides of the natural join share a column CardId, so you cannot refer to the table alias for this column, e.g.:

SELECT CardId, Block
FROM Contains
NATURAL JOIN
   (SELECT CardId FROM ...
   ) tmp
WHERE CardId = CardId

但是显然这没有任何意义，因为自然连接意味着按照定义CardId = CardId，因此上述内容应该很简单:

But obviously this makes no sense, since the natural join means that CardId = CardId by definition, so the above should be simply:

SELECT CardId, Block
FROM Contains
NATURAL JOIN
   (SELECT CardId FROM ...
   ) tmp

第2部分.

内部查询中的自然连接:

This natural join in the inner query:

SELECT CardId
FROM Costs
NATURAL JOIN
   (SELECT Id FROM ...
   ) rc
WHERE Costs.CardId = rc.Id
AND ManaCardId IN (...)

在这里，两个列列表(CardId)和(Id)没有共同的列，这意味着自然联接没有要联接的部分-通常会导致笛卡尔联接.但是，由于Costs.CardId = rc.Id，无论如何，where子句仍然有效地进行了内部联接.因此，为了使代码更清晰，我宁愿仅使用内部联接:

Here, the two column lists (CardId) and (Id) have no columns in common, which means the natural join has nothing to join - which would normally result in a cartesian join. However, the where clause effectively does an inner join anyway because of Costs.CardId = rc.Id. So, just to make the code clearer, I would prefer to just use an inner join:

SELECT CardId
FROM Costs
JOIN
   (SELECT Id FROM ...
   ) rc
WHERE Costs.CardId = rc.Id
AND ManaCardId IN (...)

第3部分.

自然联接通常不受欢迎，因为它们取决于选择的列-因此，如果开发人员将一列添加到选择列表中，但没有注意到它使用的是自然联接，则可能会有意外的副作用.通常，最好明确地连接表，例如:

Natural joins are generally frowned upon because they are dependent on which columns are selected - so if a developer adds a column to a select list but doesn't notice it's using a naturaly join, it may have unexpected side effects. It is generally good practice to join tables explicitly, e.g.:

SELECT Block FROM ( 
   SELECT CardId, Block
   FROM Contains
   GROUP BY Block
   UNION
   SELECT CardId, Block
   FROM Contains
   JOIN
      (SELECT CardId
       FROM Costs
       JOIN
          (SELECT Id
           FROM Card
           WHERE RarityId IN (SELECT Id FROM Rarity WHERE RarityType='Legendary')
          ) rc
       ON Costs.CardId = rc.Id
       WHERE ManaCardId IN (SELECT Id FROM ManaCard WHERE ManaColor='Red')
      ) tmp
   ON Contains.CardId = tmp.CardId
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

您还可以简化最里面的联接:

You can also simplify the innermost join:

SELECT Block FROM ( 
   SELECT CardId, Block
   FROM Contains
   GROUP BY Block
   UNION
   SELECT CardId, Block
   FROM Contains
   JOIN
      (SELECT CardId
       FROM Costs
       JOIN Card rc
       ON Costs.CardId = rc.Id
       WHERE Costs.ManaCardId IN (SELECT Id FROM ManaCard WHERE ManaColor='Red')
       AND rc.RarityId IN (SELECT Id FROM Rarity WHERE RarityType='Legendary')
      ) tmp
   ON Contains.CardId = tmp.CardId
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

现在，查看此查询，我注意到您正在对Contains表上的两个查询进行联合-第二个查询是这些行的子集.根据定义，第二个查询返回的所有行都包含在第一个查询中，并且UNION消除重复项，因此上述查询在逻辑上等效于:

Now, looking at this query I notice that you're UNIONing two queries on the Contains table - the second query being a subset of those rows. By definition, all the rows returned by the 2nd query are included in the 1st query, and UNION eliminates duplicates, so the above query is logically equivalent to:

SELECT Block FROM ( 
   SELECT CardId, Block
   FROM Contains
   GROUP BY Block
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

我注意到带有GROUP BY的查询没有任何聚合，因此这在Oracle中不起作用.我认为此查询等效于:

I note that the query with the GROUP BY doesn't have any aggregates, so this will not work in Oracle. I think this query is equivalent to:

SELECT Block FROM ( 
   SELECT DISTINCT Block
   FROM Contains
) bn
GROUP BY Block
HAVING COUNT(*) < 2;

从返回一个不同的块集的查询中计算出重复块的数量！ -这意味着该查询等同于:

Which counts the number of duplicate blocks from a query that returns a distinct set of Blocks! - which means this query is equivalent to:

SELECT DISTINCT Block FROM Contains;

我怀疑PHP运行此查询的方式与其在Oracle中的工作方式之间存在逻辑上的差异-因此上述简化可能是错误的.

I suspect there are some logical differences between the way that PHP runs this query vs. how it will work in Oracle - so the above simplification is probably wrong.

这篇关于将自然连接与Where操作一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！