In Oracle SQL, what does PRIMARY_FLAG(+) = 'Y' do?

Question

I'm looking at this chunk of SQL from a query:

AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'EMAIL' 
AND CONT_POINT.PRIMARY_FLAG(+) = 'Y'
AND ra.cust_trx_type_id = rt.cust_trx_type_id

What is the PRIMARY_FLAG(+) = 'Y' part for? thanks!!

edit: hmm , I think I've found a lead - Oracle "(+)" Operator

score 1 · Accepted Answer · answered Dec 10 '13 at 17:45

Yes, it's the old (pre-ANSI SQL) outer join operator.

In this particular usage, however, it's worth making an additional point.

If you have a query such as:

select a.name parent_name, 
       b.name child_name 
  from parent a,
       child b
 where a.id = b.id(+);

then you're going to show all the parent names, even those without children. (That's what the (+) operator does. If there are rows in parent that don't have any children, those rows will still be shown, with a NULL value for child_name.)

Now, what if you wanted to show parents, even those without children, but only female children?

If you do something like:

select a.name parent_name,
       b.name child_name
  from parent a,
       child b
 where a.id = b.id(+)
   and b.gender='F';

that would effectively invalidate the outer join. Since any outer joined rows would have NULL for all the rows where no data is present in CHILD, the "b.gender='F'" would effectively cancel out the outer join, by filtering out the outer joined rows.

So, if instead, you do something like:

select a.name parent_name,
       b.name child_name
  from parent a,
       child b
 where a.id = b.id(+)
   and b.gender(+)='F';

That would give you the desired effect. It would show only females for rows joined from PARENT to CHILD, and for rows w/ missing children, they would not be filtered out.

So, even though you didn't show the complete query, my guess is that you're outer joining to the table that has the PRIMARY_FLAG column, and this predicate is saying, show me all the rows where PRIMARY_KEY is 'Y' or PRIMARY_KEY is NULL due to outer join to a row that doesn't exist.

Hope that's clear.

In Oracle SQL, what does PRIMARY_FLAG(+) = 'Y' do?

1 Answers1