Are there better alternatives rather than using a “Full outer join” when it is not possible to use a “left or right join”

join;oracle-11g-r2plsqlquerysyntax

I have two tables with below structure :

 Create table Modern_transaction
 (Eeffective_date   Date,
  Customer_Num      Number,
  Branch_Code       Number,
  MTrns_type_one    Number,
  MTrns_type_two    Number)


 Create table Card_transaction
  (Eeffective_date   Date,
   Customer_Num      Number,
   Branch_Code       Number,
   CTrns_type_one    Number,
   CTrns_type_two    Number)

I need to have the data of both table in one table with this structure:

Create table Customer_transaction
  (Eeffective_date   Date,
   Customer_Num      Number,
   Branch_Code       Number,
   MTrns_type_one    Number,
   MTrns_type_two    Number
   CTrns_type_one    Number,
   CTrns_type_two    Number)

some example data :

                                Modern_transaction
 ----------------------------------------------------------------------------------
   Eeffective_date | Customer_Num  | Branch_Code | MTrns_type_one  |  MTrns_type_two    
      9/22/2013    |       x       |       12    |       10        |      20
      9/22/2013    |       y       |       13    |       20        |       0
      9/22/2013    |       z       |       18    |       12        |      12




                                Card_transaction
 ----------------------------------------------------------------------------------
   Eeffective_date | Customer_Num  | Branch_Code | CTrns_type_one  |  CTrns_type_two    
      9/22/2013    |       x       |       22    |       10        |      20
      9/22/2013    |       y       |       13    |       20        |       0
      9/22/2013    |       y       |       12    |       20        |      20

The point is that I'm using a full join for this :

 select nvl(mt.Eeffective_date,ct.Eeffective_date),
        nvl(mt.Customer_Num ,ct.Customer_Num),
        nvl(mt.Branch_Code ,ct.Branch_Code),
        nvl(mt.CTrns_type_one,0),
        nvl(mt.CTrns_type_two,0),
        nvl(mt.MTrns_type_one,0),
        nvl(mt.MTrns_type_two,0)
 from Modern_transaction mt full join
      Card_transaction  ct
   on mt.Eeffective_date = ct.Eeffective_date and
      mt.Customer_Num    = ct.Customer_Num
      mt.Branch_Code     = ct.Branch_Code

according to many references , full join is harmful and it's not suggested. for example take this for example https://weblogs.sqlteam.com/jeffs/2007/04/19/full-outer-joins/. I wan to know is it always bad and harmful to use full outer join? I mean do yo consider full join as your last solution ? Are there better alternatives for writing this query? In which circumstances will full join produce false or duplicate result?Using a left join is not possible here cause non of the tables above could be considered as the base table.

Update :
The primary key of both tables is the combination of Eeffective_date ,Customer_Num,Branch_Code

thanks in advance

Best Answer

If you need to use a full outer join, use a full outer join. It exists for a reason. In general, though, it should be an uncommon tool in the toolbox.

When designing systems, you generally try to minimize the number of outer joins that you need to do. In this case, it would seem to make more sense to have a general transaction table that both modern_transaction and card_transaction are children of. Then you'd just need to do a left outer join to both children from the parent.

A full outer join isn't going to produce false results. It will produce exactly the results you ask for. As with any code, however, that may not be the results you actually want. In your case, what is the primary key of each table? Eeffective_date is a date and dates always store time down to the second. Are you storing the actual date there? Or are you truncating the date and storing the time as midnight?

  • If the primary key is Eeffective_date, customer_num, branch_code and you are storing the time as midnight, that implies that each customer can only do one of each transaction at each branch every day. It seems unlikely that a customer couldn't do two transactions in one day.
  • If the primary key is Eeffective_date, customer_num, branch_code and you are storing the actual time, then customers can do multiple transactions every day but it would be likely that some transactions that should match wouldn't match in your join because one table was inserted into with a value just before the change of a second and the other was inserted into with a value just after the change.
  • If the primary key is something else, then a single row from t1 might match multiple rows from t2 and vice versa. The full outer join will do exactly what you're asking but it is extremely rare that this would be what you want to happen.
Related Question