Using inner or outer join based on input parameter - without using dynamic sql

Question

It is possible to condition a join to outer or inner according to the value of a parameter without using dynamic sql?

• I mean, if a parameter(filter value) is given then the query must return exactly matching records (or 0 records) - acting as inner join
• If this filter is not provided then it is needed to return all records - acting as an outer join

Answer 1

If the filter is on the table you're outer joining to

select some_columns
  from left l
       left outer join right r
         on( l.right_id = r.right_id )
 where r.filter_column = p_some_parameter
    or p_some_parameter is null

would seem to be all you need. If p_some_parameter is specified, the predicate effectively turns the outer join into an inner join. If p_some_parameter is not specified, the query remains an outer join.