Использование Natural Join с операцией Where

Question

У меня есть запрос, который я использовал в phpMyAdmin, и он работал отлично, однако я перенес свою базу данных на другой сервер, и теперь я использую SQL*Plus для выполнения своих запросов. Запрос теперь генерирует

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, но оба не работают.

Answer 1

Часть 1

При естественном объединении столбцы, которые были "естественным образом объединены", теряют псевдонимы таблиц, например:

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

Здесь обе стороны естественного объединения разделяют столбец CardId, поэтому вы не можете ссылаться на псевдоним таблицы для этого столбца, например:

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

Но, очевидно, это не имеет смысла, так как естественное соединение означает, что CardId знак равно CardId по определению, так что выше должно быть просто:

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

Часть 2.

Это естественное объединение во внутреннем запросе:

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

Здесь два столбца списки (CardId) а также (Id) не имеют общих столбцов, что означает, что естественному соединению нечего объединить - что обычно приводит к декартовому объединению. Тем не менее, предложение where эффективно выполняет внутреннее соединение, так как Costs.CardId = rc.Id, Поэтому, чтобы сделать код более понятным, я бы предпочел просто использовать внутреннее соединение:

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

Часть 3

Природные объединения обычно не одобряются, поскольку они зависят от того, какие столбцы выбраны, поэтому, если разработчик добавляет столбец в список выбора, но не замечает, что он использует естественное объединение, это может привести к неожиданным побочным эффектам. Обычно рекомендуется явным образом объединять таблицы, например:

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;

Вы также можете упростить внутреннее соединение:

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 удаляет дубликаты, поэтому приведенный выше запрос логически эквивалентен:

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

Я отмечаю, что запрос с GROUP BY не имеет никаких агрегатов, поэтому это не будет работать в Oracle. Я думаю, что этот запрос эквивалентен:

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

Который подсчитывает количество повторяющихся блоков из запроса, который возвращает отдельный набор блоков! - что означает, что этот запрос эквивалентен:

SELECT DISTINCT Block FROM Contains;

Я подозреваю, что между тем, как PHP выполняет этот запрос, и тем, как он будет работать в Oracle, есть некоторые логические различия, поэтому приведенное выше упрощение, вероятно, неверно.

Answer 2

Поскольку вы выполняли NATURAL Join, вы должны удалить явный столбец-квалификатор. Пожалуйста, попробуйте revmocve

ГДЕ Costs.CardId = rc.Id И ManaCardId IN

Затраты и стоимость

WHERE Contains.CardId = tmp.CardId)bn

Содержит и тмп

В любом случае вы можете переписать этот SQL без Natural Joins.