Are there better alternatives rather than using a "Full outer join" when it is not possible to use a "left or right join"?
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.