LEFT OUTER JOIN EQUIVALENT

Question

I have a tables contains null values. In ORDER table i have 2 null in PART_ID section and 2 null values in CUSTOMER_ID.

And i have that kind of query:

SELECT O.ORDER_ID , O.ORDER_DATE , O.CUST_ID, O.QUANTITY ,O.PART_ID ,
       C.CUST_NAME, C.CUST_CODE, P.PART_NAME, P.PART_CODE
FROM [ORDER] 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 

So here is my question. How can i do it without using outer join ? I tried too many things including where not exists or this ;

SELECT *
FROM [ORDER] O ,CUSTOMER C, PART P
WHERE C.CUST_ID = (
  SELECT CUST_ID FROM CUSTOMER C WHERE O.CUST_ID = C.CUST_ID
) AND P.PART_ID = (SELECT PART_ID FROM PART P WHERE O.PART_ID = P.PART_ID) 

but i couldn't find solution. If there is a solution how it will be ?

(Note: this is homework.)

I have that kind of table :

and left outer join gives that :

the hw said do it without using outer join and get same table as left outer join gives. But like a said i coulnd't. I'm also using MSSQL.

Answer 1

Outer join produces super-set over inner join. Indeed, from Wikipedia: A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table.

So to model left outer join using inner join one could use UNION of inner join SELECT between same tables with same join condition and another SELECT from 1st table that returns all rows without a match from the right table (I reduced your case to a single left join):

SELECT O.ORDER_ID , O.ORDER_DATE , O.CUST_ID, O.QUANTITY ,O.PART_ID ,
       P.PART_NAME, P.PART_CODE
FROM [ORDER] O JOIN PART P ON P.PART_ID = O.PART_ID
UNION
SELECT O.ORDER_ID , O.ORDER_DATE , O.CUST_ID, O.QUANTITY ,O.PART_ID ,
       NULL, NULL 
FROM [ORDER] O
WHERE NOT EXISTS (SELECT 'found' FROM PART P WHERE P.PART_ID = O.PART_ID)