SQL - 理解'JOIN'语法，顺序

Question

我的任务之一是将Access SQL转换为Oracle SQL。 我在MS Access中运行了查询，以下是关系表、查询SQL和查询结果： ACCESS QUERY SQL

SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
FROM PUBLISHER INNER JOIN (BRANCH INNER JOIN (BOOK INNER JOIN INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE) ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM) ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
WHERE (((BOOK.TYPE)='FIC') AND ((BRANCH.BRANCH_NAME)='Henry on the Hill'))
ORDER BY PUBLISHER.PUBLISHER_NAME;

我认为 MS SQL 可能在 Oracle 中也适用，因此我尝试了一下：

SQL> SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
  2  FROM PUBLISHER INNER JOIN (BRANCH INNER JOIN (BOOK INNER JOIN INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE) ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM) ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
  3  WHERE (((BOOK.TYPE)='FIC') AND ((BRANCH.BRANCH_NAME)='Henry on the Hill'))
  4  ORDER BY PUBLISHER.PUBLISHER_NAME;

no rows selected

很明显那并没有生效。所以，我在我的Oracle 11g中进行了剪切/粘贴/编辑操作，以下是代码：

SQL> SELECT PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE,
  2   INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
  3  FROM PUBLISHER
  4   inner join BOOK  ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
  5  inner join INVENTORY on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
  6  inner join BRANCH on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
  7   where BOOK.BOOK_TYPE = 'FIC'
  8   and BRANCH.BRANCH_NAME = 'Henry on the Hill';
inner join INVENTORY on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM
                                               *
ERROR at line 5:
ORA-00904: "BRANCH"."BRANCH_NUM": invalid identifier

那么我为什么会得到这个错误： ERROR at line 5: ORA-00904: "BRANCH"."BRANCH_NUM": 无效的标识符

连接顺序有影响吗？ 如您所见，我是按顺序从表到表进行连接的。

语法顺序有关系吗？例如：

inner join BRANCH on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM 还是 inner join BRANCH on BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM

这有关系吗？

Oracle SQL中的连接格式正确吗？顺序正确吗？

如果是，为什么它不能运行？

---

顺便说一句，这是一门在线课程，虽然讲师非常细心，但我从阅读其他人的帖子和提出自己的问题中学到了很多。谢谢大家！

- Brian Wilson

Answer 1

连接的顺序很重要，例如查看这个答案。您无法访问在条件之前未引入的表别名，例如您不能在第5行访问BRANCH，因为它只在下一个连接中引入。

MS Access代码引入了很多括号来限制连接顺序。只需对其应用格式，并查看结果：

SELECT
  PUBLISHER.PUBLISHER_CODE,
  PUBLISHER.PUBLISHER_NAME,
  BOOK.TITLE,
  BOOK.TYPE,
  INVENTORY.BRANCH_NUM,
  BRANCH.BRANCH_NAME
FROM
  PUBLISHER
  INNER JOIN (
    BRANCH INNER JOIN (
      BOOK INNER JOIN INVENTORY
        ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
      )
      ON BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
    )
    ON PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
WHERE
  (
    (
      (BOOK.TYPE)='FIC'
    )
    AND
    (
      (BRANCH.BRANCH_NAME)='Henry on the Hill'
    )
  )
ORDER BY
  PUBLISHER.PUBLISHER_NAME

去除不必要的括号后，它看起来像下面这样：

SELECT
  PUBLISHER.PUBLISHER_CODE,
  PUBLISHER.PUBLISHER_NAME,
  BOOK.TITLE,
  BOOK.TYPE,
  INVENTORY.BRANCH_NUM,
  BRANCH.BRANCH_NAME
FROM
  PUBLISHER
  INNER JOIN BRANCH
  INNER JOIN BOOK
  INNER JOIN INVENTORY
             ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
                AND
                BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
                AND
                PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
WHERE
  BOOK.TYPE='FIC'
  AND
  (BRANCH.BRANCH_NAME = 'Henry on the Hill')
ORDER BY
  PUBLISHER.PUBLISHER_NAME

看起来更好了，但是只要将ANSI语法转换为普通查询就可以避免排序问题：
警告：ANSI语法爱好者，请不要阅读本回答的其余部分 :-)

SELECT
  PUBLISHER.PUBLISHER_CODE,
  PUBLISHER.PUBLISHER_NAME,
  BOOK.TITLE,
  BOOK.TYPE,
  INVENTORY.BRANCH_NUM,
  BRANCH.BRANCH_NAME
FROM
  PUBLISHER,
  BRANCH,
  BOOK,
  INVENTORY
WHERE
  BOOK.TYPE='FIC'
  AND
  (BRANCH.BRANCH_NAME = 'Henry on the Hill')
  AND
  PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
  AND
  BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
  AND
  BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
ORDER BY
  PUBLISHER.PUBLISHER_NAME

采用这种变体，条件的顺序并不重要，所有条件都放在一起，因此可以按逻辑顺序重新组织它：

SELECT
  PUBLISHER.PUBLISHER_CODE,
  PUBLISHER.PUBLISHER_NAME,
  BOOK.TITLE,
  BOOK.TYPE,
  INVENTORY.BRANCH_NUM,
  BRANCH.BRANCH_NAME
FROM
  BRANCH,
  INVENTORY,
  BOOK,
  PUBLISHER
WHERE
  (BRANCH.BRANCH_NAME = 'Henry on the Hill') -- start from most restrictive 
                                             -- condition (concrete branch)  
  AND
  INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM   -- get all inventory from this branch
  AND
  BOOK.BOOK_CODE = INVENTORY.BOOK_CODE       -- access book specification 
                                             -- corresponding to inventory
  AND    
  BOOK.TYPE = 'FIC'                          -- of specific type 
  AND
  PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE  -- and finally find 
                                                  -- all publishers of that books
ORDER BY
  PUBLISHER.PUBLISHER_NAME

因此，在最后一种变体中，可以将查询逻辑以人类可读的格式复制。
请注意，在查询文本中表的顺序（至少在Oracle中，如果您没有使用某些特殊提示）不会影响实际的查询执行计划，因为优化器根据需要自行更改它。因此，ANSI变体在大多数情况下只是引入了语法限制，而没有真正的帮助。

- ThinkJet

---

你的说法“因此，在大多数情况下，ANSI变体只是引入了语法限制，而没有真正的帮助”听起来像是你已经证明了什么，但就我所知，你并没有。在我看来，ANSI查询语法在可读性和清晰度方面要好得多，更具有自我记录性（例如连接类型），并且更能防止连接错误（例如缺少连接），而你的方法永远无法捕捉到这些错误。除此之外，我发现你的格式更难阅读，并且有一些冗余的注释，比如“对应库存”，而在ANSI语法中则非常清楚。 - D'Arcy Rittich

@RedFilter 看起来你在回答的中间漏掉了一个警告 :) 因为不同的人有不同的偏好，所以 在我看来 是两种观点的最佳资格。 - ThinkJet

Answer 2

您原始的查询将INVENTORY连接在一起，如下：

inner join INVENTORY ON BOOK.BOOK_CODE = INVENTORY.BOOK_CODE

Try this one:

select PUBLISHER.PUBLISHER_CODE, PUBLISHER.PUBLISHER_NAME, BOOK.TITLE, BOOK.TYPE, INVENTORY.BRANCH_NUM, BRANCH.BRANCH_NAME
from PUBLISHER
inner join BOOK on PUBLISHER.PUBLISHER_CODE = BOOK.PUBLISHER_CODE
inner join INVENTORY on BOOK.BOOK_CODE = INVENTORY.BOOK_CODE
inner join BRANCH on BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM
where BOOK.TYPE = 'FIC'
    and BRANCH.BRANCH_NAME = 'Henry on the Hill'
order by PUBLISHER.PUBLISHER_NAME;

- D'Arcy Rittich

---

我仍然得到“未选择行”，这对我来说没有意义。 - Brian Wilson

如果您提供一些样本数据和模式，我可以帮助您进一步解决问题。SQL Fiddle使这变得容易。 - D'Arcy Rittich