1

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

6
  • 1
    Why are you looking at a SQL Server post for performance tips for Oracle? Those tips usually don't cross well. May 30, 2020 at 18:50
  • @MichaelKutz well the most important thing here is that the full join does not produce false result and I want to know in which condition exactly it produces false result and want to make sure it won't happen.
    – Pantea
    May 30, 2020 at 18:53
  • 1
    @MichaelKutz the thoughts in the linked article are largely independent from the RDBMS implementation.
    – miracle173
    May 31, 2020 at 6:47
  • 1
    @PanteaTourang the author of the proposed article writes "(Feel free to suggest some FULL OUTER JOIN situations in the comments and we can discuss". Replace "nvl" by "coalesce" and I think you have a valid SQLSERVER query that you can discuss with the author. I think you should accept his suggestion.
    – miracle173
    May 31, 2020 at 6:52
  • The sample data suggest that you could use a UNION instead of the FULL JOIN. May 31, 2020 at 10:30

1 Answer 1

1

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.
5
  • Thanks for your answer . I'm storing the date exactly the way I used in the example . for example '9/22/2013'.
    – Pantea
    May 31, 2020 at 4:46
  • 1
    @PanteaTourang - A date in Oracle always has a time component and does not have a format. Are you setting the time component to be midnight? Does that mean that each customer can have only one transaction of a given type per branch? That seems an unlikely business rule. May 31, 2020 at 5:58
  • could you please run this query and look at the data in the table? create table z_test_test (fdate date); insert into z_test_test select to_date('5/31/2020','mm/dd/yyyy') from dual; commit; we are storing the date , like this to_date('5/31/2020','mm/dd/yyyy')
    – Pantea
    May 31, 2020 at 6:22
  • And about the primary keys , the combination of date , customer_num and branch_code is the primary key of both table . And the design is due to the customer's need and actually that's what they wanted That all modern transaction be assigned to a specific branch .
    – Pantea
    May 31, 2020 at 6:28
  • 2
    @PanteaTourang 'to_date' converts a string to the internal date format. in 'select fdate from z_test_test' the internal date is implicitely converted to a string. "select to_char(fdate,'mm/dd/yyyy HH:Mi:SS' from z_test_test" will show you that the time component is midnight ("00:00:00"). Here is a manual entry about the DATE type.
    – miracle173
    May 31, 2020 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.