在单个查询中使用多个联接

Question

我试图修改以前的查询以删除where部分并使用join，但在执行此操作时遇到了错误。我曾尝试阅读Oracle文档，但没有找到任何修复错误的方法。

上一个查询：

SELECT DISTINCT 
CLI.IDCLI, CLI.NOMCLI,CLI.PRENOMCLI
FROM
LIVRAISON LIV, LIVRAISON LIV1, CLIENT CLI, COMMANDE COM, CLIENT CLI1, COMMANDE COM1
WHERE 
CLI.IDCLI = COM.IDCLI
AND COM.IDCOM = LIV.IDCOM
AND CLI1.IDCLI = COM1.IDCLI
AND COM1.IDCOM = LIV1.IDCOM
AND LIV.DATELIV = LIV1.DATELIV
AND LIV.IDLIV = LIV1.IDLIV
AND CLI1.NOMCLI = 'MARTIN'
AND CLI.IDCLI != CLI1.IDCLI
ORDER BY 
CLI.IDCLI ASC;

新建查询&错误：

SQL> SELECT DISTINCT CLI.IDCLI, CLI.NOMCLI,CLI.PRENOMCLI
2  FROM LIVRAISON LIV      NATURAL JOIN LIVRAISON LIV1
3                          INNER JOIN COMMANDE COM       ON COM.IDCOM=LIV.IDCOM,
4        LIV1              INNER JOIN COMMANDE COM1      ON LIV1.IDCOM=COM1.IDCOM,
5        CLIENT CLI        INNER JOIN COM                ON CLI.IDCLI=COM.IDCOM,
6        COM1              INNER JOIN CLIENT CLI1        ON CLI1.IDCLI=COM1.IDCLI
7  WHERE CLI1.NOMCLI='MARTIN'
8  AND CLI.IDCLI!= CLI1.IDCLI
9  ORDER BY CLI.IDCLI ASC;
COM1              INNER JOIN CLIENT CLI1        ON CLI1.IDCLI=COM1.IDCLI
*
ERROR at line 6:
ORA-00942: table or view does not exist

Answer 1

避免自然加入，除非你是一位经验丰富的加入魔法专家。

不要把旧的逗号语法和联接语法混在一起。

使用唯一的表别名。

SELECT DISTINCT
CLI.IDCLI, 
CLI.NOMCLI, 
CLI.PRENOMCLI
FROM LIVRAISON LIV
JOIN LIVRAISON LIV2 ON LIV2.DATELIV = LIV.DATELIV
AND LIV2.IDLIV   = LIV.IDLIV
JOIN COMMANDE  COM  ON COM.IDCOM    = LIV.IDCOM
JOIN CLIENT    CLI  ON CLI.IDCLI    = COM.IDCLI
JOIN COMMANDE  COM2 ON COM2.IDCOM   = LIV2.IDCOM
JOIN CLIENT    CLI2 ON CLI2.IDCLI   = COM2.IDCLI
WHERE CLI2.NOMCLI = 'MARTIN'
AND CLI.IDCLI  != CLI2.IDCLI
ORDER BY 
CLI.IDCLI ASC;