Simple Example: Let's say you have a Students table, and a Lockers table. In SQL, the first table you specify in a join, Students, is the LEFT table, and the second one, Lockers, is the RIGHT table.
Each student can be assigned to a locker, so there is a LockerNumber column in the Student table. More than one student could potentially be in a single locker, but especially at the beginning of the school year, you may have some incoming students without lockers and some lockers that have no students assigned.
For the sake of this example, let's say you have 100 students, 70 of which have lockers. You have a total of 50 lockers, 40 of which have at least 1 student and 10 lockers have no student.
INNER JOIN is equivalent to "show me all students with lockers".
Any students without lockers, or any lockers without students are missing.
Returns 70 rows
LEFT OUTER JOIN would be "show me all students, with their corresponding locker if they have one".
This might be a general student list, or could be used to identify students with no locker.
Returns 100 rows
RIGHT OUTER JOIN would be "show me all lockers, and the students assigned to them if there are any".
This could be used to identify lockers that have no students assigned, or lockers that have too many students.
Returns 80 rows (list of 70 students in the 40 lockers, plus the 10 lockers with no student)
FULL OUTER JOIN would be silly and probably not much use.
Something like "show me all students and all lockers, and match them up where you can"
Returns 110 rows (all 100 students, including those without lockers. Plus the 10 lockers with no student)
CROSS JOIN is also fairly silly in this scenario.
It doesn't use the linked lockernumber field in the students table, so you basically end up with a big giant list of every possible student-to-locker pairing, whether or not it actually exists.
Returns 5000 rows (100 students x 50 lockers). Could be useful (with filtering) as a starting point to match up the new students with the empty lockers.
I would presume that the answer lies in prefetching the associations that you want to. That way you can fetch the needed part of your object graph in a single shot. You would do this in criteria queries like so.
ICriteria query = session.CreateCriteria(typeof (Cat))
.SetFetchMode("Mate", FetchMode.Join)
.SetFetchMode("Kittens", FetchMode.Join);
IList<Cat> results = query.List<Cat>();
This will give you back a list of cats with both the Mate and the Kittens prepopulated. You can then navigate to these properties without incurring an N+1 penalty. If you need a more flattened result I'd do it using linq, perhaps like this.
var results = query.List<Cat>()
.Select((c) => new {mother = c, mate = c.Mate, offspr = c.Kittens});
This will give you back a flattened list of anonymous types with the given properties. This will work if all you need is prefetching the object graph. However if you need to prefetch things such as counts or sums then you will need to examine the Projections and Alias parts of Criteria queries.
One more thing. If you are trying to exactly duplicate your above query, you can do so like this.
ICriteria query = session.CreateCriteria(typeof (Cat), "mother")
.CreateAlias("Mate", "mate", JoinType.InnerJoin)
.CreateAlias("Kittens", "offspr", JoinType.LeftOuterJoin)
.SetResultTransformer(Transformers.AliasToEntityMap);
This will return you basically the same as your hql query, however instead of using an indexed list it will use a dictionary that maps the alias to the entity.
Best Answer
You probably figure out this long time ago. Solution is to add ICriteria parameter in JoinAlias method, like this:
I have restriction on aliasAccFrom, where i want that TimeTo is null, in last line of code.