mysql - SQL - 理解 'JOIN' 语法、顺序

Question

我的任务之一是将访问 SQL 转换为 Oracle SQL。我在 MS access 中运行查询，这里是关系表、查询 SQL 和查询结果:

访问查询 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

为什么我会得到这个: 第 5 行的错误: ORA-00904: "BRANCH"."BRANCH_NUM": 无效标识符

连接顺序会影响什么吗？如您所见，我确实按顺序从一张 table 走到另一张 table 。

语法顺序重要吗？例如:

inner join BRANCH on INVENTORY.BRANCH_NUM = BRANCH.BRANCH_NUM或在 BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM 上内部加入 BRANCH

这重要吗？

Oracle SQL 中的连接格式是否正确，顺序是否正确？

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

---

旁注，这是在线类(class)，虽然讲师很细心，但我在这里通过阅读其他帖子并提出自己的问题学到了很多东西。谢谢大家!

Answer 1

连接顺序很重要，例如 check this answer .您不能访问在条件之前未引入的表别名，例如您无法在第 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 变体只是在没有真正帮助的情况下引入语法限制。