# Joins

Query across up to four tables with Join / LeftJoin / RightJoin / FullOuterJoin / NaturalJoin / CrossJoin, with typed tuples, OrderBy, projection, and aggregates.

## Overview

The generated `Query()` builder extends into a join against another table. The result is an async stream of
typed tuples: `(T1? , T2?)` for two tables, `(T1?, T2?, T3?)` for three, up to **four** tables. Elements are
**nullable**: an outer-join miss yields `null` for the unmatched side (see [Outer joins](#outer-joins)).

```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}");
}
```

Attach the connection or transaction with `.WithConnection()` / `.WithTransaction()`, and materialize with
`ExecuteAsync()` (stream), `ToListAsync()`, or `FirstOrDefaultAsync()`.

## 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) |

Each `on` is an `Expression<Func<…, bool>>` with one parameter per table joined so far.

## Inner join

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

Generated SQL. Every column of every table is selected and aliased `a{N}_column`, with each table aliased `a0`, `a1`, and so on:

```sql
SELECT
    a0."id" AS a0_id, a0."username" AS a0_username, ...,
    a1."id" AS a1_id, a1."user_id" AS a1_user_id, ...
FROM "users" a0
INNER JOIN "user_logins" a1 ON a0."id" = a1."user_id"
```

## Outer joins

`LeftJoin` / `RightJoin` / `FullOuterJoin` return **`null`** for the side that didn't match (not a
zeroed-out instance), so you can tell "no match" from a real row of defaults. (Detection uses the table's
primary key: a row whose PK comes back `NULL` is a miss.)

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

> **NOTE** Because elements are nullable, null-check (or `!`) the side that can be absent. Inner/cross joins
> never produce nulls, but the tuple type is nullable for all join kinds.

## Filtering the driving table

A predicate on `Query()` filters the driving table *before* the join:

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

## Filtering across tables with WHERE

`.Where(...)` takes one parameter per joined table and references any of them:

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

## Three and four tables

Chain `.Join` (and the outer/cross variants) for a third and fourth table; each ON receives a parameter per
table joined so far:

```csharp
var rows = await Order.Query()
    .Join<User>((o, u) => o.UserId == u.Id)
    .Join<Product>((o, u, p) => o.ProductId == p.Id)
    .Where((o, u, p) => p.InStock)
    .WithConnection(conn)
    .ToListAsync();   // List<(Order?, User?, Product?)>
```

**Four tables is the maximum.** For five or more, use a [`.sql` procedure](/database/0.3.2/advanced/procedure-mapping).

## Sorting

`.OrderBy` / `.OrderByDesc` take an `object?[]` of columns from any joined table:

```csharp
var rows = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .OrderByDesc((u, l) => new object?[] { l.LastSeenAt })
    .WithConnection(conn)
    .ToListAsync();
```

## Projection to a typed result

`.Select(...)` maps each tuple into your own type instead of returning `(T1?, T2?, …)`:

```csharp
var summaries = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .Select((u, l) => new LoginSummary(u!.Username, l!.LastSeenAt))
    .ToListAsync();
```

The projection runs **client-side** (a compiled delegate, AOT-safe) over each materialized tuple. It gives
you typed results but still fetches every column. The projector's parameters are nullable, so outer-join
misses surface as `null` inside `Select` too.

## Aggregates over a join

Aggregate without materializing rows. `CountAsync()` returns `long`; `SumAsync`/`AvgAsync`/`MinAsync`/`MaxAsync`
take a column selector and return `T?` (`null` when no rows match):

```csharp
long n = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .Where((u, l) => u.IsActive)
    .WithConnection(conn)
    .CountAsync();

decimal? total = await Order.Query()
    .Join<User>((o, u) => o.UserId == u.Id)
    .WithConnection(conn)
    .SumAsync<decimal>((o, u) => o.Total);
```

## Limit, Offset, and transactions

`.Limit()` / `.Offset()` are available on every join builder, and `.WithTransaction(tx)` enlists the join in a
transaction instead of `.WithConnection()`.

## Limitations

- **Up to four tables.** For more, use a [`.sql` procedure](/database/0.3.2/advanced/procedure-mapping).
- **Projection is client-side.** `.Select(...)` gives typed results but doesn't reduce the columns fetched
  (all columns of all tables are still selected).