---
title: "Joins"
description: "Joins in Tauri SQLite ORM."
canonical_url: "https://docs.ctrl-neo.dev/en/tauri-sqlite-orm/advanced-topics/joins"
---
# Joins

> Joins in Tauri SQLite ORM.

While [Relations](working-with-relations) provide a high-level, automated way to fetch related data, sometimes you need more control. Manual joins give you direct access to `LEFT JOIN` and `INNER JOIN`, allowing for custom join conditions and flattened result sets.

Use manual joins when you need to:

- Perform an `INNER JOIN` (to only include records that have a match in the other table).
- Filter or sort based on a column in the joined table.
- Get a flat result object instead of a nested one.
- Use complex `ON` conditions not supported by the relations feature.

## Join Methods

The query builder provides two methods for manual joins:

- **.leftJoin(table, condition, alias)**
- **.innerJoin(table, condition, alias)**

### Aliasing is Required

When you join a table, you **must** provide a short string alias. This alias is crucial for telling the ORM which table a column belongs to, especially in the `where` clause.

## Example: Finding Users by Post Title

Let's find all users who have written a post with "Tauri" in the title. This is a perfect use case for an `innerJoin`, as we only want users who have matching posts.

```typescript
import { db } from '$lib/db';
import { users, posts } from '$lib/db/schema';
import { eq, like, innerJoin } from '@type32/tauri-sqlite-orm';

const usersWithTauriPosts = await db.select(users)
  .innerJoin(
    posts,                                           // Table to join
    eq(users._.columns.id, posts._.columns.authorId), // ON condition
    'p'                                               // Alias for the 'posts' table
  )
  .where(
    like(posts._.columns.title, '%Tauri%', 'p')     // Filter on the aliased table
  )
  .all();
```

**Explanation:**

1. We start the query from the `users` table.
2. We `innerJoin` the `posts` table, giving it the alias `'p'`. The `ON` condition is `users.id = posts.authorId`.
3. In the `.where()` clause, we filter on `posts.title`. Critically, we must pass the alias `'p'` as the third argument to `like()` so the ORM knows to generate `WHERE p.title LIKE ?` instead of `WHERE title LIKE ?`.

**Note:** Manual joins return flat results. For nested data structures, use [Relations](working-with-relations) with `.include()` instead.

## The Result Shape

Unlike the nested objects from `.include()`, queries with manual joins return a **flat object**. To prevent column name collisions (e.g., both tables having an `id` column), the ORM prefixes each column with its table name or alias.

The result of the query above would look like this:

```json
[
  {
    "users.id": 1,
    "users.fullName": "Jane Doe",
    "p.id": 101,
    "p.title": "Getting Started with Tauri",
    "p.authorId": 1
  }
]
```


## Sitemap

See the full [sitemap](https://docs.ctrl-neo.dev/sitemap.md) for all pages.