不使用左连接的左连接有3个表

I have to get all data from 3 tables with left join. I did that with and without using left join. But my teacher ask another solution that uses just inner join. I just couldn't find the solution please give some advice. Here are the tables:

我必须从3个具有左连接的表中获取所有数据。我使用并没有使用左连接。但是我的老师提出了另一个只用内连接的解决方案。我找不到解决办法，请您给我点化一下。这是表:

And the results should look like this:

结果应该是这样的:

With left join solution:

与左连接解决方案:

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

Without left join solution:

没有离开加入解决方案:

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

2 个解决方案

#1

Here is one approach which uses only INNER JOIN and does not use LEFT JOIN anywhere. It begins with your original query using INNER JOIN instead of LEFT JOIN, and then adds to that result set the pieces which are missing from taking the inner product of all the tables.

这里有一种方法只使用内部连接，不使用任何地方的左连接。它从使用内部连接(而不是左连接)的原始查询开始，然后将所有表的内积所缺少的部分添加到结果集。

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

Follow the link below for a working demo:

按照下面的链接进行演示:

SQLFiddle

#2

This makes no sense in "real life", but I understand it's meaningfulness in a SQL course. You have a good teacher.

这在“现实生活”中毫无意义，但我理解这在SQL课程中是有意义的。你有一个好老师。

Try like this

这样的尝试

SELECT * FROM
(   
    SELECT O.*,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.*,NULL,NULL,P.PART_CODE,P.PART_NAME 
    FROM ORDERS O
    INNER JOIN PART P ON P.PART_ID = O.PART_ID
    WHERE O.CUST_ID IS NULL

    UNION

    SELECT O.*,C.CUST_CODE,C.CUST_NAME,NULL, NULL
    FROM ORDERS O
    INNER JOIN CUSTOMER C ON C.CUST_ID = O.CUST_ID
    WHERE O.PART_ID IS NULL

    UNION

    SELECT O.*,NULL,NULL,NULL, NULL
    FROM ORDERS O
    WHERE O.PART_ID IS NULL AND O.CUST_ID IS NULL   

)   T

ORDER BY ORDER_ID