There are mainly two types of joins. An inner join and an outer join. The best way to understand the difference between the two is to use Venn diagram from set theory. If you think of employee as one set and benefit as another set, then intersection between the two can be represented by a Venn diagram as follows:

The overlapping area or intersection of the Employee and Benefit sets represents the records that are present in both sets. These are employee records having associated benefit records or vice versa. The non-overlapping area represents the records that are either present only in the Employee set or the Benefit set.

A SQL join lets you connect two tables and return records that are either present in both tables or present in one of the tables. If you want the records that are present in both the tables, then you should use inner join. If you want records that are present in either of the tables then you use an outer join. Following modified Venn diagram depicts which is the inner join and which is outer:

Outer joins have three subtypes – a left outer join, a right outer join, and a full outer join. A left outer join would return records that are present in the overlapping part of the preceding Venn diagram plus the records that are present in the left non-overlapping part. A right outer join returns records that are present in the right non-overlapping part and the overlapping part of the preceding Venn diagram. A full outer join is combination of right and left outer join. Syntactically, following is how you write these different joins:

SELECT *
FROM Employee E
INNER JOIN Address A ON E.Id = A.Employee_Id

SELECT *
FROM Employee E
LEFT OUTER JOIN Address A ON E.Id = A.Employee_Id

SELECT *
FROM Employee E
RIGHT OUTER JOIN Address A ON E.Id = A.Employee_Id

SELECT *
FROM Employee E
FULL OUTER JOIN Address A ON E.Id = A.Employee_Id

The equality construct after the preceding ON is how your associate records from two sets. This is mostly a foreign key relationship between the two tables. Most relational database technologies support both inner and outer joins but there may be a few which do not. If you are using such database, then NHibernate would throw an exception if you happen to use outer join while it is not supported.

Since NHibernate knows from the mapping metadata how two entities are related to each other, it takes away the complexity and makes joining two entities simple. This is also called implicit joins.

NHibernate supports notion of implicit join. It means that if you have mapped two entities as having association between them, then NHibernate would implicitly join the mapped tables when one entity is navigated from the other in the query. This could be difficult to understand, so let's take an example. Suppose you want to load all employees who live in London. You would need an inner join between the Employee and Address tables to load those employees. Since we have mapped the association between the Employee and Address class, NHibernate knows to use inner join when the ResidentialAddress property on the Employee class is navigated in LINQ query, as shown next:

[Test]
public void QueryEmployeesWhoLiveInLondon()
{
  IList<Employee> employees = null;
  using (var transaction = Database.Session.BeginTransaction())
  {
    employees =
    Database.Session.Query<Employee>()
    .Where(e => e.ResidentialAddress.City == "London").ToList();
    transaction.Commit();
  }

  Assert.That(employees.Count, Is.EqualTo(3));
}

Take a look at the part in bold where we specify restriction on the City property just by navigating the ResidentialAddress property of Employee. Because of the mapped association between the two classes, NHibernate knows that a foreign key relationship exists between the tables that these entities are mapped to. NHibernate will use that knowledge to generate the following SQL involving an inner join between the Employee and Address tables:

SELECT employee0_.id             AS Id0_, 
       //…other columns…// 
FROM   employee employee0_ 
       inner join address address1_ 
               ON employee0_.id = address1_.employee_id 
WHERE  address1_.city =@ p0; 
@p0 = 'London' [TYPE: String (0)] 

Criteria and QueryOver support implicit joins but the syntax is slightly verbose. Following is how the preceding query can be written using QueryOver:

[Test]
public void QueryEmployeesWhoLiveInLondon()
{
  IList<Employee> employees;
  using (var transaction = Database.Session.BeginTransaction())
  {
    employees = Database.Session.QueryOver<Employee>()
    .JoinQueryOver(x => x.ResidentialAddress)
    .Where(r => r.City == "London").List<Employee>();
    transaction.Commit();
  }

Assert.That(employees.Count, Is.EqualTo(3));
}

For LINQ version, the navigation of the ResidentialAddress property inside call to the Where method gave us the join we needed. But that does not work with QueryOver. In QueryOver, you need to explicitly tell NHibernate about your intention to perform a join by calling method named JoinQueryOver. It is in this method that you navigate to the ResidentialAddress property. Call to JoinQueryOver returns a new instance of IQueryOver<Address>, so any subsequent restrictions are applied on properties of the Address entity. Internally, there is a lot going on here. Let's take a look at this in detail to understand how exactly joins work for QueryOver.

Address addressAlias = null;
var employees = Database.Session.QueryOver<Employee>()
.JoinAlias(x => x.ResidentialAddress,() => addressAlias)
.Where(e => e.Firstname == "John" && addressAlias.City =="London")
.List<Employee>();

var employees = Database.Session.Query<Employee>()
.Where(e => e.Firstname == "John" &&
e.ResidentialAddress.City == "London").ToList();

In QueryOver, specifying left or right outer joins is not very different from specifying inner joins. Rather, the implicit join syntax can be slightly modified to tell NHibernate that it should consider an outer join in place of inner join. The QueryOver example we just saw can be changed as follows to result in a left outer join instead of inner join:

using (var transaction = Database.Session.BeginTransaction())
{
  employees = Database.Session.QueryOver<Employee>()
  .Left.JoinQueryOver(x => x.ResidentialAddress)
  .Where(r => r.City == "London").List<Employee>();
  transaction.Commit();
}

The preceding code is exact reproduction of the earlier code except for one minor modification in bold. Call to chained property Left before JoinQueryOver tells NHibernate that a left outer join should be considered here. Similarly, chained properties Right and Full are available to declare our intent to consider right outer join and full outer join respectively.

Support for outer joins in LINQ is limited and syntax is not intuitive. For starters, you cannot use lambda expressions for outer joins. Following is an example of left outer join between Employee and Benefit using a LINQ query:

                 from b in e.Benefits.DefaultIfEmpty()
                 select e).ToList();

Note the call to DefaultIfEmpty() in bold after join with e.Benefits. This is LINQ's implementation of left join. What this is telling NHibernate is that if benefit side is empty then consider a default record – which is what a left join does. A caveat to remember with this syntax is that it only works for the joins with the properties that can be navigated from the root entity. In the preceding query, root entity is Employee and the joined entity is Benefit which can be navigated from Employee via the Benefits collection on it.

The joins we have seen so far are called ANSI joins. There is another class of joins called theta style joins. Theta style joins are similar to Cartesian product of two tables with a where clause limiting the records that end up in the result. The previous query to return employees living in London can be rewritten using theta style join as follows:

SELECT employee0_.id             AS Id0_, 
       //… other columns …// 
FROM   employee employee0_, 
       address address1_ 
WHERE  address1_.employee_id = employee0_.id 
       AND address1_.city =@ p0; 
@p0 = 'London' [TYPE: String (0)]  

Theta joins are useful when you need to join two entities without having any mapped association between them (or two database tables not related through a foreign key). It is strange to have two entities which are related but do not have that relation reflected in database or in mappings. But you may encounter such situation while working with legacy databases, so it is useful to know how to do theta joins. Lack of association between the entities means that we cannot use implicit joins. Unfortunately, not all querying methods of NHibernate support theta joins. Only HQL and LINQ queries support theta joins. Following is an example of theta join using LINQ that would result in the previous SQL being sent to database:

var employees = from e in Database.Session.Query<Employee>()
                join a in Database.Session.Query<Address>() 
on e equals a.Employee
                where a.City == "London"
                select e;

Here, instead of navigating the ResidentialAddress property on the Employee class, we are getting a new independent IQueryable<Address> instance in order to perform a join. This results in a theta style join being issued.