Joins

Implicit Joins

Implicit joins can be written with multiple generators:

      val q = for {
        u <- Users
        o <- Orders if u.id is o.userID
      } yield u.first ~ o.orderID

This notation makes use of the monadic nature of queries. It translates directly to an implicit join in SQL:

    select u.first, u.orderID from Users u, Orders o where u.id = o.userID

Explicit Joins

The same inner join can be written explicitly as:

      val q = for {
        Join(u, o) <- Users innerJoin Orders on (_.id is _.userID)
      } yield u.first ~ o.orderID

The explicit notation can also be used for outer joins, currently only the outer join of two tables is supported:

      val qLeftOuter = for {
        Join(u, o) <- Users leftJoin Orders on (_.id is _.userID)
      } yield u.first ~ o.orderID.?

      val qRightOuter = for {
        Join(u, o) <- Users rightJoin Orders on (_.id is _.userID)
      } yield u.first.? ~ o.orderID

      val qFullOuter = for {
        Join(u, o) <- Users outerJoin Orders on (_.id is _.userID)
      } yield u.first.? ~ o.orderID.?

Note that columns are not automatically converted to Option types even though outer joins can return NULL values. If you do not want to get default values for those columns if they are NULL, you need to convert them manually with .?.