如何在Oracle语法中使用双重条件进行LEFT JOIN? [英] How to do LEFT JOIN with double condition in Oracle syntax?

问题描述

我有2张桌子.

1)汽车表:

2)参数表:

我需要提取具有 owner 参数匹配且在 insurance 中没有差异的重复汽车(这必须是相同的，或者两者都不存在).

I need to fetch duplicate cars have owner params match and do NOT differ in insurance (that's it must be either the same or is absent for both).

我已使用 LEFT JOIN 以ANSI语法成功执行查询:

I'm successfully executing my query with LEFT JOIN in ANSI-syntax:

SELECT owner.name, owner.value, COALESCE (insur.name, 'insurance'), insur.value, count(*)
FROM car INNER JOIN param owner ON car.id = owner.car_id
LEFT JOIN param insur ON car.id = insur.car_id AND insur.name = 'insur'
WHERE owner.name = 'owner'
GROUP BY owner.name, owner.value, insur.name, insur.value
HAVING count(*) > 1

具有正确工作的ANSI语法的SQL提琴

但是，当我用Oracle语法中的(+)符号改写了该查询而不是 LEFT JOIN 时，我得到了不同的结果:

But when I rewrote this query with (+) sign from Oracle syntax instead LEFT JOIN I have different result:

SELECT owner.name, owner.value, COALESCE (insur.name, 'insurance'), insur.value, count(*)
FROM car,
     param owner,
     param insur
WHERE car.id = owner.car_id
  AND owner.name = 'owner'
  AND car.id (+) = insur.car_id -- key change
  AND insur.name = 'insur'
GROUP BY owner.name, owner.value, insur.name, insur.value
HAVING count(*) > 1

SQL提琴带有(+)而不是左联接的意外结果

此查询的结果为空.我不明白如何用Oracle语法重写它以得到相同的查询结果.

Result of this query is empty. I don't understand how to rewrite it in Oracle syntax to have the same result of queries.

推荐答案

那是

SQL> select owner.name, owner.value,
  2    coalesce (insur.name, 'insurance') in_name,
  3    insur.value, count(*)
  4  from car, param owner, param insur
  5  where car.id = owner.car_id
  6    and car.id  = insur.car_id (+)
  7    and insur.name (+) = 'insur'
  8    and owner.name = 'owner'
  9  group by owner.name, owner.value, insur.name, insur.value
 10  having count(*) > 1;

NAME     VALUE    IN_NAME              VALUE      COUNT(*)
-------- -------- -------------------- -------- ----------
owner    John     insurance                              2

SQL>

但是，为什么为什么要使用 old Oracle外连接语法?与ANSI连接相比，它仅具有缺点，而没有优点(至少，我没有想到).实际上，我知道一个-如果您在旧的Forms&中使用外部联接，报告6i(甚至更旧?我认为现在没有人使用这些版本)，他们的内置 PL/SQL引擎可能不会说 ANSI外部联接，所以您注定要使用旧的(+)外连接运算符.除此之外...不，不知道.

Though, why would you want to use the old Oracle outer-join syntax? When compared to ANSI joins, it has only drawbacks and no advantages (at least, I can't think of any). Actually, I know one - if you use outer join in old Forms & Reports 6i (or even older? I don't think that anyone uses those versions nowadays), their built-in PL/SQL engine might not speak ANSI outer joins so you're doomed to use the old (+) outer join operator. Other than that ... nope, no idea.

这篇关于如何在Oracle语法中使用双重条件进行LEFT JOIN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！