Hi ,

This may be a stupid question and discuss b4, l wonder why a lot ORMs use outer join instead of inner join for eager fetching ?

let's consider a scenario , l have three tables - Books , Authors and Publishers , their relationship are ..

Publishers <-- 1:M -->Books <-- M:M--> Authors

Let's assume it is a web application (library automation system) , and l have a search page "search book", there are three search criteria input boxs( bookTitle , authorName and publisherName) , and a submit button.


Scenarias :

1. if l leave the three input boxs blank and press the submit button , l expect -- A. display all books (may be it is not a good idea , but can be accepted if l limit it with maximum nos return) , or B. display none (this probably the most people choice)

2. if l fill the input box "authorName" a keyword , l hope to get a list books (a collections if more than one) with all authors and publishers "initialized".

Question : For scenario No.2 , we use "inner join" or "outer join" ?

if l choose "inner join" as eager loading both publisher and authors , then how l do it in hibernate ? oh ... we can use "inner join fetch" , there is an "inner join fetch" in Hibernate Reference Doc 3.1.3 , pg143.



or

initialize() method describe in HIA or
"Ensuring Initialized Association Paths"http://www.javalobby.org/java/forums/t62077.html

if l choose "outer join" , the result is not what l want , it will alway return ALL books for me whether l fill in either publisherName or authorName and leave bookTitle blank ...

this seem to be a common use for "inner eager fetching" than "outer eager fetching" ....

l am confusing ...... any one can help me ?

moon

I'd recommend you reading a good book about SQL.

You could try:
SQL: Practical Guide for Developers

no no ... this is my mistake .... it is not true that it will return ALL books.

Let's try a simple example , here are my tables ,



this is outer join :


it return :



this is inner join :



it return:


The above simple example showing that l "do" know the different between inner join and outer join , : ) .. hihi

.... to be continue

moon

before come to this thread , l found a thread http://saloon.javaranch.com/cgi-bin/ubb ... 8&t=001062 discuss the different between inner join and outer join , but now let's continue my problem ...

let's do a search by author_name (scenario no2.) , what will happen between outer join and inner join ?

1. outer join:


results (2 nos):


2. inner join:



result (2 nos) :


yes , you will get the *same* results .

This seem to imply that the criterias after the "where" clause make no different for the outer and inner join ! (right ?) it make different if we don't use where clause , as the example in the last post ....

For my search books page , if l hope to display

| title | author_name | publisher_name | (as the report data or whatever)

using an "eager fetch" to retrieve the book collections with authors and publishers initialized (may be add some criterias after the where clause , like publisher_name or author_name) , what would l use ? inner or outer ?

this seem both is OK , but since l consider the performance cost ....



why not using inner join as eager fetching instead of outer ?

l must be missing something ! because simple logic telling me that those ORM guys are smarter than me ....hihihi

somebody help ~~~

* yes , l do know that the HIA tell us to use the method described in pg 262~265 (the Object[] array method) , but why using the "array" instead of "object" ?

moon

Try with



do you see it now?

Aha .... , thanks ~ pepelnm.

this is the reason they use outer join instead of inner join , let's write it down for someone who have the same stupid question as l.

outer join :



result (1 no) :



inner join :


result (0 no) :
none

this is indeed not a trivial answer for a sql beginner , although l understand what is inner and outer join is ....

if this example can be a "exercise" for those ORM doc / book , l think it will help a lot beginner as l .... : )

Now , let's proceed to some other derived questions -

1. Is this the only reason that they choice "outer" instead of "inner" ?

2. If the books table (or other tables) can ensure the non-nullable value , can l assert that the inner join is same as outer join again , and the outer join can be replaced by inner join (performance consideration) ?

moon