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
CodePudding user response:
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
.