Outer Join vs Dummy Rows

I found that in a dimensional layout, it is beneficial to create a dummy row in the dimension to avoid writing LEFT OUTER from the fact table. There are other reason for this aside from perceived performance benefit.

However, is it appropriate to use it in a relational database? Should we create dummy rows in our base tables so that our relation table can attach to it? Besides being unnatural for RDBMS, it causes unnecessary database bloat. Just wondering if I am NOT seeing something.

All in the spirit of avoiding OUTER JOINs.

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 |
-------------------------------------------
|

StackExchange Database Administrators Q#33599, answer score: 3