Re: outer join between parent-child, not null and FK constraints

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: ax.mount@xxxxxxxxx
  • Date: Fri, 25 Jan 2013 08:57:12 +0100

Alex,
     I agree that in your example the outer join is useless, but there 
is a constant in Oracle, the assumption (which experience proves to be 
bold) that developers know what they are writing (as opposed to copying 
and pasting something "that works"). You are asking for an outer join, 
you are getting an outer join, even if it's useless. The customer is 
king. Another similar example is the famous case of HAVING - if you 
write a HAVING condition that doesn't use an aggregate in its 
predicate(s), filtering will occur after aggregation, not before. 
However, if your aggregate is a view , the same condition applied with a 
WHERE to the view will be pushed into the view and filtering will occur 
before aggregation, which proves that Oracle *could* do it (for SQL 
Server, filtering occurs before aggregation in both cases).

     I know that SQL is supposed to be declarative, and that you are 
just supposed to state what you want, but for me the Oracle behaviour 
makes sense. Your (+), after all, states "Outer join", as much as HAVING 
states "after the aggregate". I am not sure that a Nanny-optimizer 
watching every misstep would ultimately be a good thing. People would 
understand what they are doing even less than today (which is saying 
something), you would probably find many cases where what you really 
wanted to do would be completely misinterpreted and would return useless 
data (see what Word processors or Google suggest at times ...), probably 
after a patch or when volumes reach some thresholds. Great fun.
     Of course the positive aspect is that you could outsource queries 
to randomly typing monkeys.

My 0.02 euros.

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


On 01/25/2013 08:02 AM, amonte wrote:
> Hi David
> As you say outer join returns all values from one table even there are no
> matches.
>
> In a parent child relation ship all child must have a parent (unless the
> child tabla rows has a null for FK). If the FK is enforced a NOT NULL then
> the child table must have a value pointing to a parent correct? What I am
> saying is that if we have FK plus NOT NULL then all child must have a match
> in parent table so outer join with parent table is redundant because there
> is no way a child table wont match a parent row.
>
> Unless I am missing some situation that even with FK and NOT NULL there
> wont be matches between child and parent?
>
> Alex
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>



--
//www.freelists.org/webpage/oracle-l


Other related posts: