Issue with the Full Outer join

[Former Member]

Hello Gurus,

This morning one of my colleagues came up to me with a strange issue on full outer join, which is something like this

Table1(T1): CUSTOMER

Table2(T2): SALES_ORDER

FULL OUTER JOIN QUERY:

in the above result set we are missing a value of the "CustomerID" column in the 6th tuple from the SALES_ORDER table which is 'C5'.

Can someone please let me know why this is happening?

Thanks in advance

Regards,

Krishna

[former_member182302]

Hi Krishna,

You can also use COALESCE to get the desired result.

You may want to go through the below blogs:

Regards,

Krishna Tangudu

[Former Member]

Thanks Krishna, will go ahead and give it a try.

Regards,

Krishna

[Former Member]

Full outer join will give NULL values for Table1(T1): CUSTOMER if we did not get data in Table2(T2): SALES_ORDER for that customer id.In simple way, you are getting NULL values for Customer Id = C4 because table2 does not have rows with customer id C4.

Same thing for Table2(T2): SALES_ORDER,you are getting NULL values for customer ID-5.

If you want all customer id should be display in final table then use inner join.Because inner join does not contains null values.And parent table should have all customer id.

Note:You can make foreign key relationship with Table1 and Table2 tables.So,at last both tables have same customer id.and then use inner join.You will never get null values.

[Former Member]

Pradip,

Full outer join will give NULL values for Table1(T1): CUSTOMER if we did not get data in Table2(T2): SALES_ORDER for that customer id.In simple way, you are getting NULL values for Customer Id = C4 because table2 does not have rows with customer id C4.

=>If you are talking about all the records from T1 and the matching records from T2 then you are talking about the "LEFT JOIN" and not the "FULL OUTER"

If you want all customer id should be display in final table then use inner join.Because inner join does not contains null values.And parent table should have all customer id.

=>All the customerid's from both the tables will not be retrieved using the "INNER JOIN" "Only the matching records will be retrieved"

I wonder how you can achieve the functionality of a full outer with a left join, and how you must have retrieved all the tuples using the inner join.

B R,

krishna.

[SaiKrishna_V]

Hi Krishna,

Above behavior is expected. If you would like to get customer details from both the tables, please try like this.

Select T2."ORDERID",T1."CUSTOMERID",T2."CUSTOMERID",T1."CUSTOMERNAME",T2."PRODUCT",T2."TOTAL_UNITS" from "CUSTOMER" AS T1 FULL OUTER JOIN "ORDERS"AS T2 ON T1."CUSTOMERID" = T2."CUSTOMERID".

Regards,

sai krishna.

[Former Member]

Hi Sai Krishna,

The above full outer query might work to retrieve the desired data but, taking the "ColumnID" from both the tables is not really what i am looking for and moreover the purpose of full outer join should be survived if we take "ColumnID" from one single table and perform a join on it.

B R,

Krishna

[SaiKrishna_V]

Hi Krishna,

Whenever we trigger "Full outer join" on top of multiple tables with reference to a common column from both tables. The result set will contain both the columns(common columns). see below.

select * from "CUSTOMER" AS T1 FULL OUTER JOIN "ORDERS"AS T2 ON T1."CUSTOMERID" = T2."CUSTOMERID"

In above query--> column1 "CustomerID" coming from T1 table and column4 "CustomerID" coming from T2 table.

in your question you were trying to retrieve the data of "CustomerID" from T1 table.That's why you were missing "C5" entry.

I hope this explanation will help you.

Thanks & regards,

sai krishna.

[Former Member]

Hi Sai Krishna,

Thanks for the reply, i understood the point that you are trying to specify but then the usual behavior of the full outer join is it retrieves the tuples from both the tables regardless of the matching elements, but then the result set that i am getting has put me in doldrums.

PFA the image below: (This is what i am trying to achieve through the query)

As a workaround i am able to achieve it this way:

Select T2."OrderID",T1."CustomerID",T1."CustomerName",T2."Product",

T2."Total_Units" from "CUSTOMER" AS T1 LEFT JOIN "SALES_ORDER"AS T2 ON T1."CustomerID" = T2."CustomerID"

union

Select T2."OrderID",T2."CustomerID",T1."CustomerName",T2."Product",

T2."Total_Units" from "CUSTOMER" AS T1 RIGHT JOIN "SALES_ORDER"AS T2 ON T1."CustomerID" = T2."CustomerID";

by blending the queries (left & right joins) with a union.

Regards,

krishna

[RashmiAcharya]

Hi Krishna,

In the querry excecuted, CustomerID is querried from table T1 as T1."CustomerID". But in T1 table there is no entry for CustomerID 'C5' so it is showing '?' (Null). In the querry if you replace T1."CustomerID" to T2."CustomerID" you should get the entry for customer ID from SALES_ORDER table.

Regards,

Rashmi

[Former Member]

Hi Rashmi,

 In the querry if you replace T1."CustomerID" to T2."CustomerID" you should get the entry for customer ID from SALES_ORDER table.

If i replace the T1.CustomerID with T2.CustomerID  then i will be missing the value "C4" as T2 does not have C4.

However, The property of the Full outer join is

"returns all the rows from the left table, and all the rows from the right table. If there are rows in the left table that do not have matches in the right table, or if there are rows in the right table that do not have matches in the left table, those rows will be listed as well."

So i guess T2 or T1 shouldn't make much of a difference.

Regards,

Krishna