Why do you wish to avoid outer joins? Using these does have a specific meaning which is "return me all the values from the driving table regardless of whether there is a matching row in the outer joined table." This gives a clear statement to future developers who maintain your code which would be lost if using a dummy value.
There are some things to bear in mind however:
(Note - discussion is based on Oracle; other major RDBMSes are likely to be similar but may have different/better optimizations which affect how outer joins are executed).
From a performance perspective, outer joining restricts the options available to the query optimizer. When constructing the query plan, the optimizer determines which is the smaller rowset and uses that as the driving table. When you specify an outer join, then the table you're outer joining from will become the driving rowsource.
Using the classic emp and dept tables supplied with Oracle, we can see this in action:
explain plan for
select *
from scott.emp e, scott.dept d
where e.deptno = d.deptno;
select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));
--------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 |
| 1 | MERGE JOIN | | 14 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 |
| 4 | SORT JOIN | | 14 |
| 5 | TABLE ACCESS FULL | EMP | 14 |
--------------------------------------------------------
explain plan for
select *
from scott.emp e, scott.dept d
where e.deptno = d.deptno (+);
select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 14 |
| 1 | HASH JOIN OUTER | | 14 |
| 2 | TABLE ACCESS FULL| EMP | 14 |
| 3 | TABLE ACCESS FULL| DEPT | 4 |
-------------------------------------------
When we fully join the two tables, Oracle chooses the smaller of the two (dept) to be the driving table. However, when we outer join to it, the order is switched. This is because it knows we need to return all the rows from emp no matter what.
As query plans are generally most efficient when starting with the smallest rowset, working up to the biggest, this could cause issues with multi-table joins outer joining from a "large" table to a "small" one. In some cases this can cause queries to be very slow. In an OLTP application most queries should only be returning a handful of rows though, so all rowsets should be small (even if coming from a large table), meaning you shouldn't encounter this problem very often in a well designed (OLTP) system.
To convert your queries to become full joins, you'll need to decide what value to use instead of null. This is a tricky issue as you're converting something which means I don't know into whatever value you assign "null" to be. This can cause confusion for future developers down the line and introduce performance issues of it's own (e.g. single column indexes don't include the null rows). See this for some discussion around null.
Personally I wouldn't attempt to create dummy values to represent null. If you find that you have performance issues related to outer joins then I would start by looking at the affected queries and tuning those individually. If this issue is affecting a lot of queries I may start looking at using dummy values, but only as a last resort.
Of course, you should always ask whether you genuinely mean to use an outer join or if you're using it "just in case"!
