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

     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

>

>

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>




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