这是一种可接受的加入方式（左外连接表）吗？

问题描述：

  create table tasks (taskno int, customerno int); 
      insert into tasks values (1, 100); 
      commit; 
      insert into tasks values (2, 200); 
      commit; 
      insert into tasks values (3, 300); 
      commit; 
      select * from tasks; 

      create table items (taskno int, accountno int); 
      commit; 
      insert into items values (1, 1000); 
      commit; 
      insert into items values (2, 2000); 
      commit; 
      select * from items; 

      create table accounts (accountno int, customerno int); 
      commit; 
      insert into accounts values (1000, 100); 
      commit; 
      insert into accounts values (1100, 100); 
      commit; 
      insert into accounts values (2000, 200); 
      commit; 
      insert into accounts values (3000, 300); 
      commit; 
      select * from accounts;

我想基于一个accountno从任务表中的taskno。任务表只有一个叫做customerno的东西。此customerno可以与多个accountno关联（将customerno视为父母，并将accountno视为小孩）。所以，如果你看看我们的设置，如果我通过在accountno 1000或者1100都将在该查询返回taskno 1 -

  select a.taskno 
      from tasks a, accounts c 
      where a.customerno = c.customerno 
      and c.accountno = 1000 -- but will return taskno 1 also for 1100

我想要一些更精细的细节莫过于此。所以我发现了另一个表'Items'，它有taskno和accountno。所以，如果我把它添加到查询，它会返回正确的taskno 1 accountno 1000而不是1100

  select a.taskno 
      from tasks a, items b, accounts c 
      where a.taskno = b.taskno 
      and a.customerno = c.customerno 
      and c.accountno = b.accountno 
      and c.accountno = 1000 -- nothing returned for 1100

这是一切都很好，但项目表并不总是可靠的。它可以说只有在任务表中找到的任务的90％。所以当任务未在项目表中发现这种情况下，我想从任务表，像accountno 3000（这意味着我将不得不customerno去，不会有accountno的粒度级别加入。但它是确定）。但是当在这个项目中找到这个accountno时，我希望它被使用，因为它有accountno，这给了我没有与确切accountno关联的taskno。因此，我将左外部联接用于包含任务的项目。

这工作perfectly-

  select a.taskno 
      from tasks a, items b, accounts c 
      where a.taskno = b.taskno(+) 
      and a.customerno = c.customerno 
      and c.accountno = nvl(b.accountno, c.accountno) 
      and c.accountno = 3000 -- will return taskno 3 


      select a.taskno 
      from tasks a, items b, accounts c 
      where a.taskno = b.taskno(+) 
      and a.customerno = c.customerno 
      and c.accountno = nvl(b.accountno, c.accountno) 
      and c.accountno = 1000 --returns 1 and nothing returned for 1100

我的问题是我有构造正确的查询这里 - 特别是我在哪里联用NVL项目以帐户的一部分？这是预期的方式吗？或者这是一个奇怪的回合吗？

好，你将会在这里告诉你的第一件事是：使用ANSI连接语法！ – sstan

即使Oracle建议停止使用专有的'（+）'在where子句中使用隐式外连接，并使用明确的'LEFT JOIN'（或'RIGHT JOIN'）运算符。 –

我不明白你为什么要加入'items'表，如果你只从'tasks'表中进行选择，并且通过'accounts'表字段进行过滤，并且两个表都可以连接。为什么你需要在查询中包含'items'？除非你也从'items'中选择信息，但是你没有显示。 – sstan

答

就像你说的，你的查询做的工作，而且是相当高明。但很难阅读和理解，部分原因是因为使用了连接语法。使用ANSI加入翻译，我们得到：

select t.taskno 
    from accounts a 
    join tasks t 
    on t.customerno = a.customerno 
    left join items i 
    on i.taskno = t.taskno 
where a.accountno = 1100 
    and a.accountno = nvl(i.accountno, a.accountno)

我仍然觉得目的不是很清楚。

就个人而言，我会重写查询，移动左边的逻辑not exists条款，而不是里面加入。在我看来，它更好地表达了意图，而且它的工作原理也是如此。

在ANSI连接语法：

select t.taskno 
    from accounts a 
    join tasks t 
    on t.customerno = a.customerno 
where a.accountno = 1100 
    and not exists (select null 
        from items i 
        where i.taskno = t.taskno 
         and i.accountno <> a.accountno)

遗留同一查询连接语法，如果它可以帮助你更好地理解（但尽量从这个语法，如果可能的话移开）：

select t.taskno 
    from accounts a, tasks t 
where a.accountno = 1100 
    and t.customerno = a.customerno 
    and not exists (select null 
        from items i 
        where i.taskno = t.taskno 
         and i.accountno <> a.accountno)

这是一种可接受的加入方式（左外连接表）吗？

相关推荐