sql - 左连接不使用 3 个表的左连接

Question

我必须使用 left join 从 3 个表中获取所有数据。无论是否使用 left join，我都是这样做的。但是我的老师问了另一个只使用 inner join 的解决方案。我只是找不到解决方案，请提供一些建议。以下是表格:

结果应该是这样的:

用left join解决方案:

SELECT O.*,C.CUST_CODE,C.CUST_NAME,P.PART_CODE,P.PART_NAME 
FROM ORDERS O
LEFT OUTER JOIN PART P ON P.PART_ID = O.PART_ID
LEFT OUTER JOIN CUSTOMER C ON C.CUST_ID = O.CUST_ID

没有left join解决方案:

SELECT O.*,
(SELECT C.CUST_CODE FROM CUSTOMER C WHERE C.CUST_ID=O.CUST_ID) AS CUST_CODE,
(SELECT C.CUST_NAME FROM CUSTOMER C WHERE C.CUST_ID=O.CUST_ID) AS CUST_NAME,
(SELECT P.PART_CODE FROM PART P WHERE P.PART_ID = O.PART_ID ) AS PART_CODE,
(SELECT P.PART_NAME FROM PART P WHERE P.PART_ID = O.PART_ID ) AS PART_NAME
FROM ORDERS O

Answer 1

这是一种只使用 INNER JOIN 并且不在任何地方使用 LEFT JOIN 的方法。它从使用 INNER JOIN 而不是 LEFT JOIN 的原始查询开始，然后将所有表的内积中缺少的部分添加到该结果集中。

SELECT t.* FROM
(
    SELECT O.ORDER_ID, O.ORDER_DATE, C.CUST_CODE, C.CUST_NAME, P.PART_CODE, P.PART_NAME 
    FROM ORDERS O
    INNER JOIN PART P
        ON P.PART_ID = O.PART_ID
    INNER JOIN CUSTOMER C
        ON C.CUST_ID = O.CUST_ID
    UNION
    SELECT O.ORDER_ID, O.ORDER_DATE, NULL AS CUST_CODE, NULL AS CUST_NAME, P.PART_CODE,
        P.PART_NAME 
    FROM ORDERS O
    INNER JOIN PART P
        ON P.PART_ID = O.PART_ID
    WHERE O.CUST_ID NOT IN (SELECT C.CUST_ID FROM CUSTOMER C)
        OR O.CUST_ID IS NULL
    UNION
    SELECT O.ORDER_ID, O.ORDER_DATE, C.CUST_CODE, C.CUST_NAME, NULL AS PART_CODE,
        NULL AS PART_NAME 
    FROM ORDERS O
    INNER JOIN CUSTOMER C
        ON C.CUST_ID = O.CUST_ID
    WHERE O.PART_ID NOT IN (SELECT P.PART_ID FROM PART P)
        OR O.PART_ID IS NULL
    UNION
    SELECT O.ORDER_ID, O.ORDER_DATE, NULL AS CUST_CODE, NULL AS CUST_NAME,
        NULL AS PART_CODE, NULL AS PART_NAME
    FROM ORDERS O
    WHERE (O.CUST_ID NOT IN (SELECT C.CUST_ID FROM CUSTOMER C) AND
           O.PART_ID NOT IN (SELECT P.PART_ID FROM PART P)) OR
           (O.CUST_ID IS NULL AND O.PART_ID IS NULL)
) t
ORDER BY t.ORDER_ID ASC

点击下面的链接获取工作演示:

SQLFiddle