SQL内连接两个查询报错求助：连接oca表与关联子查询

你的SQL报错是因为第二个查询未用括号包裹为子查询，且JOIN语法结构不符合标准SQL规范。以下是两种正确写法：

写法一：两个子查询直接关联 #

SELECT *
FROM (
    SELECT *
    FROM edl_current.core_product_master_piprod AS hdr
    INNER JOIN edl_current.core_product_master_prodcmpntasscn AS cpt
        ON hdr.prod_id = cpt.prod_id
    WHERE hdr.pin_17 IS NOT NULL
        AND cpt.comp_typ_cd = "ED"
) AS PI
INNER JOIN (
    SELECT *
    FROM edl_views.oca_orders_opt_cd_ex AS oca
) AS oca
    ON oca.pin_17 = PI.pin_17

写法二：以oca表为主表关联子查询PI #

SELECT *
FROM edl_views.oca_orders_opt_cd_ex AS oca
INNER JOIN (
    SELECT hdr.pin_17, hdr.prod_id, cpt.*
    FROM edl_current.core_product_master_piprod AS hdr
    INNER JOIN edl_current.core_product_master_prodcmpntasscn AS cpt
        ON hdr.prod_id = cpt.prod_id
    WHERE hdr.pin_17 IS NOT NULL
        AND cpt.comp_typ_cd = "ED"
) AS PI
    ON oca.pin_17 = PI.pin_17

额外提示 #

• 尽量避免使用SELECT *，明确指定所需字段能提升查询效率，还能避免多表同名字段引发的冲突
• 子查询PI中的hdr.pin_17在关联时直接写PI.pin_17即可，无需加hdr.前缀

内容的提问来源于stack exchange，提问作者DataScienceDave