# Joins

Query across two tables with Join, LeftJoin, RightJoin, FullOuterJoin, NaturalJoin, and CrossJoin.

## Overview

The generated `Query()` method can be extended with a join to a second table. The result is an async enumerable of `(T Left, TJoin Right)` value tuples.

---

## Syntax

Call a join method on an existing query builder, then supply a connection and execute:

```csharp
await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{user.Username} — last login: {login.LastSeenAt}");
}
```

The join method takes one argument: an expression `Expression<Func<T, TJoin, bool>>` for the `ON` condition. The connection (or transaction) is attached separately via `.WithConnection()` / `.WithTransaction()`.

---

## Join types

| Method | SQL |
|--------|-----|
| `.Join<TJoin>(on)` | `INNER JOIN` |
| `.LeftJoin<TJoin>(on)` | `LEFT JOIN` |
| `.RightJoin<TJoin>(on)` | `RIGHT JOIN` |
| `.FullOuterJoin<TJoin>(on)` | `FULL OUTER JOIN` |
| `.NaturalJoin<TJoin>()` | `NATURAL JOIN` (no ON clause) |
| `.CrossJoin<TJoin>()` | `CROSS JOIN` (no ON clause) |

---

## Inner join

```csharp
await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{user.Username} — last login: {login.LastSeenAt}");
}
```

Generated SQL:

```sql
SELECT
    t."id" AS t_id, t."username" AS t_username, ...,
    j."id" AS j_id, j."user_id" AS j_user_id, ...
FROM "users" t
INNER JOIN "user_logins" j ON t."id" = j."user_id"
```

All columns from both tables are selected. The generator aliases them with `t_` (main table) and `j_` (join table) prefixes to avoid name collisions.

---

## Left join

Use `LeftJoin` when the joined record may not exist. The `TJoin` result will be a default instance when there is no match:

```csharp
await foreach (var (user, profile) in User.Query()
    .LeftJoin<UserProfile>((u, p) => u.Id == p.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{user.Username}: {profile?.Bio ?? "(no profile)"}");
}
```

---

## Filtering the main table before joining

Pass a predicate to `Query()` to filter the driving table before the join:

```csharp
await foreach (var (user, login) in User.Query(u => u.IsActive == true)
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }
```

---

## Filtering across both tables with WHERE

Use `.Where()` on the join builder to add a condition that references both tables. The expression receives two parameters — one for each table:

```csharp
await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .Where((u, l) => u.IsActive == true && l.LastSeenAt > cutoff)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }
```

---

## Limit and Offset

`.Limit()` and `.Offset()` are available on the join builder:

```csharp
await foreach (var (user, login) in User.Query()
    .LeftJoin<UserLogin>((u, l) => u.Id == l.UserId)
    .Limit(50)
    .Offset(0)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }
```

---

## Using a transaction

Pass a `DbTransaction` via `.WithTransaction()` instead of `.WithConnection()`:

```csharp
await foreach (var (user, order) in User.Query()
    .Join<Order>((u, o) => u.Id == o.UserId)
    .WithTransaction(tx)
    .ExecuteAsync())
{ ... }
```

---

## Limitations

- Join queries select all columns from both tables; column projection is not supported on join builders.
- Only two-table joins are supported. For more complex joins, use [Procedure mapping](/database/0.2.0/advanced/procedure-mapping) with a raw `.sql` file.
- `OrderBy` / `OrderByDesc` are not available on the join builder. Sort in C# using LINQ after materializing, or use a procedure.