Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Columns in outer joins
But that's the point. IT IS how SQL works. Why should there be any
problem with using the syntax of a language as it is documented. It may
seem strange, but it is consistant and those of us who have been writing
sql for years are used to writing outer joins that way. It has nothing
to do with vendor-specific implementation.
Ken
stuart_kohler_at_my-deja.com wrote:
> In evaluating gui SQL reporting tools, it seems
> common to assist in the outer joining of tables
> but ignore outer joining column references in the
> outer joined table.
>
> For example, when joining an address table and
> phone table, the table join is built correctly,
> as:
> address_studentid = phone_studentid (+)
>
> This will correctly pull students with or without
> phones.
>
> However, when there is a phone, we usually
> only want to pull the main phone number and so add
> a statement like:
> phone_type = 'Main'
>
> Unless this line is manually edited to
> phone_type (+) = 'Main'
> the table outer join is useless and only pulls
> students with phones, specifically of the type
> 'Main' - and therefore negates having outer joined
> the tables in the first place.
>
> When I've queried the software vendors, they seem
> to shrug and say "That's the way SQL works."
>
> Fundamentally, this makes no sense, since I cannot
> imagine an instance where after having outer
> joined tables that I would NOT want
> phone_type (+) = 'Main'
> so I don't understand why any vendor would choose
> to implement the behavior of
> phone_type = 'Main'
> and claim it's the user's problem to outer join
> the phone_type column.