sql - 如何将其翻译成 postgresql？

Question

总的来说，我对 sql 还比较陌生，我正在尝试使用一个非常大的数据库。现在，在网站的示例中，有这个查询

SELECT m.chembl_id AS compound_chembl_id,   
s.canonical_smiles,   
r.compound_key,   
NVL(TO_CHAR(d.pubmed_id),d.doi) AS pubmed_id_or_doi,   
a.description                   AS assay_description,   act.standard_type,   
act.standard_relation,   
act.standard_value,   
act.standard_units,   
act.activity_comment 
FROM compound_structures s,   
molecule_dictionary m,   
compound_records r,   
docs d,   
activities act,   
assays a,   
target_dictionary t 
WHERE s.molregno (+) = m.molregno 
AND m.molregno       = r.molregno 
AND r.record_id      = act.record_id 
AND r.doc_id         = d.doc_id 
AND act.assay_id     = a.assay_id 
AND a.tid            = t.tid 
AND t.chembl_id      = 'CHEMBL1827';

因为这个 (+) 和这个 NVL，我假设它是 Oracle。我正在研究 pgadmin4，在做了一些研究之后我尝试翻译它，但都出现了错误。我的尝试示例和下面给出的错误

SELECT m.chembl_id AS compound_chembl_id,   
s.canonical_smiles,   
r.compound_key,   
COALESCE(CAST(d.pubmed_id AS varchar),d.doi)    AS pubmed_id_or_doi,   
a.description                                   AS assay_description,   
act.standard_type,   
act.standard_relation,   
act.standard_value,   
act.standard_units,   
act.activity_comment 
FROM compound_structures s,   
compound_records r,   
docs d,   
activities act,   
assays a,   
target_dictionary t
LEFT OUTER JOIN molecule_dictionary m ON s.molregno = m.molregno
WHERE m.molregno      = r.molregno 
AND r.record_id      = act.record_id 
AND r.doc_id         = d.doc_id 
AND act.assay_id     = a.assay_id 
AND a.tid            = t.tid 
AND t.chembl_id      = 'CHEMBL1827';

ERROR:  invalid reference to FROM-clause entry for table "s"
LINE 16: LEFT OUTER JOIN molecule_dictionary m ON s.molregno = m.molr...
                                              ^
HINT:  There is an entry for table "s", but it cannot be referenced from this part of the 
query.
SQL state: 42P01
Character: 492

有人可以帮我吗？

Answer 1

不要混合古老的隐式连接和显式 JOIN 运算符:

SELECT m.chembl_id AS compound_chembl_id,   
       s.canonical_smiles,   
       r.compound_key,   
       COALESCE(CAST(d.pubmed_id AS varchar),d.doi)  AS pubmed_id_or_doi,   
       a.description                   AS assay_description,   
       act.standard_type,   
       act.standard_relation,   
       act.standard_value,   
       act.standard_units,   
       act.activity_comment 
FROM compound_structures s
  LEFT JOIN molecule_dictionary m ON s.molregno = m.molregno 
  JOIN compound_records r         ON m.molregno = r.molregno 
  JOIN docs d                     ON r.doc_id = d.doc_id 
  JOIN activities act             ON r.record_id = act.record_id 
  JOIN assays a                   ON act.assay_id = a.assay_id 
  JOIN target_dictionary t        ON a.tid = t.tid 
WHERE t.chembl_id      = 'CHEMBL1827';

我希望我的外连接方向是正确的——我已经几十年没有使用 Oracle 语法了(请注意，即使是 Oracle 也建议停止使用它)。

但下表中的内连接有效地将外连接变回内连接——我认为 Oracle 语法不会改变(意思是:我认为在原始查询中尝试外连接是错误的首先)。所以也许您可以将 LEFT JOIN 简化为 JOIN。我不确定外连接在 Oracle 查询中是否真的有意义。