supabase/apps/docs/content/guides/database/joins-and-nesting.mdx

---
id: 'joins-and-nested-tables'
title: 'Querying Joins and Nested tables'
description: 'The Data APIs automatically detect relationships between Postgres tables.'
---

The data APIs automatically detect relationships between Postgres tables. Since Postgres is a relational database, this is a very common scenario.

## One-to-many joins

Let's use an example database that stores `orchestral_sections` and `instruments`:

<Tabs
  scrollable
  size="small"
  type="underlined"
  defaultActiveId="table"
  queryGroup="output-format"
>
<TabPanel id="table" label="Tables">

**Orchestral sections**

| `id` | `name`    |
| ---- | --------- |
| 1    | strings   |
| 2    | woodwinds |

**Instruments**

| `id` | `name` | `section_id` |
| ---- | ------ | ------------ |
| 1    | violin | 1            |
| 2    | viola  | 1            |
| 3    | flute  | 2            |
| 4    | oboe   | 2            |

</TabPanel>
<TabPanel id="SQL" label="SQL">

```sql
create table orchestral_sections (
  "id" serial primary key,
  "name" text
);

insert into orchestral_sections
  (id, name)
values
  (1, 'strings'),
  (2, 'woodwinds');

create table instruments (
  "id" serial primary key,
  "name" text,
  "section_id" int references "orchestral_sections"
);

insert into instruments
  (name, section_id)
values
  ('violin', 1),
  ('viola', 1),
  ('flute', 2),
  ('oboe', 2);
```

</TabPanel>
</Tabs>

The APIs will automatically detect relationships based on the foreign keys:

<Tabs
  scrollable
  size="small"
  type="underlined"
  defaultActiveId="js"
  queryGroup="language"
>
<TabPanel id="js" label="JavaScript">

```js
const { data, error } = await supabase.from('orchestral_sections').select(`
  id,
  name,
  instruments ( id, name )
`)
```

### TypeScript types for joins

`supabase-js` always returns a `data` object (for success), and an `error` object (for unsuccessful requests).

These helper types provide the result types from any query, including nested types for database joins.

Given the following schema with a relation between orchestral sections and instruments:

```sql
create table orchestral_sections (
  "id" serial primary key,
  "name" text
);

create table instruments (
  "id" serial primary key,
  "name" text,
  "section_id" int references "orchestral_sections"
);
```

We can get the nested `SectionsWithInstruments` type like this:

```ts
import { QueryResult, QueryData, QueryError } from '@supabase/supabase-js'

const sectionsWithInstrumentsQuery = supabase.from('orchestral_sections').select(`
  id,
  name,
  instruments (
    id,
    name
  )
`)
type SectionsWithInstruments = QueryData<typeof sectionsWithInstrumentsQuery>

const { data, error } = await sectionsWithInstrumentsQuery
if (error) throw error
const sectionsWithInstruments: SectionsWithInstruments = data
```

</TabPanel>
<$Show if="sdk:dart">
<TabPanel id="dart" label="Dart">

```dart
final data = await supabase.from('orchestral_sections').select('id, name, instruments(id, name)');
```

</TabPanel>
</$Show>
<$Show if="sdk:swift">
<TabPanel id="swift" label="Swift">

```swift
struct OrchestralSection: Codable {
  let id: Int
  let name: String
  let instruments: [Instrument]

  struct Instrument: Codable {
    let id: Int
    let name: String
  }
}

let orchestralSections: [OrchestralSection] = try await supabase
  .from("orchestral_sections")
  .select("id, name, instruments(id, name)")
  .execute()
  .value
```

</TabPanel>
</$Show>
<$Show if="sdk:kotlin">
<TabPanel id="kotlin" label="Kotlin">

```kotlin
val data = supabase.from("orchestral_sections").select(Columns.raw("id, name, instruments(id, name)"))
```

</TabPanel>
</$Show>
<$Show if="sdk:python">
<TabPanel id="python" label="Python">

```python
data = supabase.from_('orchestral_sections').select('id, name, instruments(id, name)').execute()
```

</TabPanel>
</$Show>
<TabPanel id="graphql" label="GraphQL">

```javascript
const Query = `
  query {
    orchestral_sectionsCollection {
      edges {
        node {
          id
          name
          instruments {
            id,
            name
          }
        }
      }
    }
  }
`
```

</TabPanel>
<TabPanel id="url" label="URL">

```bash
GET https://[REF].supabase.co/rest/v1/orchestral_sections?select=id,name,instruments(id,name)
```

</TabPanel>
</Tabs>

## Many-to-many joins

The data APIs will detect many-to-many joins. For example, if you have a database which stored teams of users (where each user could belong to many teams):

```sql
create table users (
  "id" serial primary key,
  "name" text
);

create table teams (
  "id" serial primary key,
  "team_name" text
);

create table members (
  "user_id" int references users,
  "team_id" int references teams,
  primary key (user_id, team_id)
);
```

In these cases you don't need to explicitly define the joining table (members). If we wanted to fetch all the teams and the members in each team:

<Tabs
  scrollable
  size="small"
  type="underlined"
  defaultActiveId="js"
  queryGroup="language"
>
<TabPanel id="js" label="JavaScript">

```js
const { data, error } = await supabase.from('teams').select(`
  id,
  team_name,
  users ( id, name )
`)
```

</TabPanel>
<$Show if="sdk:dart">
<TabPanel id="dart" label="Dart">

```dart
final data = await supabase.from('teams').select('id, team_name, users(id, name)');
```

</TabPanel>
</$Show>
<$Show if="sdk:swift">
<TabPanel id="swift" label="Swift">

```swift
struct Team: Codable {
  let id: Int
  let name: String
  let users: [User]

  struct User: Codable {
    let id: Int
    let name: String
  }

  enum CodingKeys: String, CodingKey {
    case id, users
    case name = "team_name"
  }
}
let teams [Team] = try await supabase
  .from("teams")
  .select(
    """
      id,
      team_name,
      users ( id, name )
    """
  )
  .execute()
  .value
```

</TabPanel>
</$Show>
<$Show if="sdk:kotlin">
<TabPanel id="kotlin" label="Kotlin">

```kotlin
val data = supabase.from("teams").select(Columns.raw("id, team_name, users(id, name)"));
```

</TabPanel>
</$Show>
<$Show if="sdk:python">
<TabPanel id="python" label="Python">

```python
data = supabase.from_('teams').select('id, team_name, users(id, name)').execute()
```

</TabPanel>
</$Show>
<TabPanel id="graphql" label="GraphQL">

````javascript
const Query = `
  query {

</TabPanel>
<TabPanel id="graphql" label="GraphQL">

```javascript
const Query = `
  query {
    teamsCollection {
      edges {
        node {
          id
          team_name
          users {
            id,
            name
          }
        }
      }
    }
  }
`
````

</TabPanel>
<TabPanel id="url" label="URL">

```bash
GET https://[REF].supabase.co/rest/v1/teams?select=id,team_name,users(id,name)
```

</TabPanel>
</Tabs>

## Specifying the `ON` clause for joins with multiple foreign keys

For example, if you have a project that tracks when employees check in and out of work shifts:

```sql
-- Employees
create table users (
  "id" serial primary key,
  "name" text
);

-- Badge scans
create table scans (
  "id" serial primary key,
  "user_id" int references users,
  "badge_scan_time" timestamp
);

-- Work shifts
create table shifts (
  "id" serial primary key,
  "user_id" int references users,
  "scan_id_start" int references scans, -- clocking in
  "scan_id_end" int references scans, -- clocking out
  "attendance_status" text
);
```

In this case, you need to explicitly define the join because the joining column on `shifts` is ambiguous as they are both referencing the `scans` table.

To fetch all the `shifts` with `scan_id_start` and `scan_id_end` related to a specific `scan`, use the following syntax:

<Tabs
  scrollable
  size="small"
  type="underlined"
  defaultActiveId="js"
  queryGroup="language"
>
<TabPanel id="js" label="JavaScript">

```js
const { data, error } = await supabase.from('shifts').select(
  `
    *,
    start_scan:scans!scan_id_start (
      id,
      user_id,
      badge_scan_time
    ),
   end_scan:scans!scan_id_end (
     id,
     user_id,
     badge_scan_time
    )
  `
)
```

</TabPanel>
<$Show if="sdk:dart">
<TabPanel id="dart" label="Dart">

```dart
final data = await supabase.from('shifts').select('''
  *,
  start_scan:scans!scan_id_start (
    id,
    user_id,
    badge_scan_time
  ),
end_scan:scans!scan_id_end (
    id,
    user_id,
    badge_scan_time
  )
''');
```

</TabPanel>
</$Show>
<$Show if="sdk:swift">
<TabPanel id="swift" label="Swift">

```swift
struct Shift: Codable {
  let id: Int
  let userId: Int
  let attendanceStatus: String?

  let scans: [Scan]

  struct Scan: Codable {
    let id: Int
    let userId: Int
    let badgeScanTime: TimeInterval

    enum CodingKeys: String, CodingKey {
      case id
      case userId = "user_id"
      case badgeScanTime = "badge_scan_time"
    }
  }

  enum CodingKeys: String, CodingKey {
    case id
    case userId = "user_id"
    case attendanceStatus = "attendance_status"
  }
}

let shifts: [Shift] = try await supabase
  .from("shifts")
  .select(
    """
      *,
      start_scan:scans!scan_id_start (
        id,
        user_id,
        badge_scan_time
      ),
     scans: scan_id_end (
        id,
        user_id,
        badge_scan_time
     )
    """
  )
  .execute()
  .value
```

</TabPanel>
</$Show>

<$Show if="sdk:kotlin">

<TabPanel id="kotlin" label="Kotlin">

```kotlin
val data = supabase.from("shifts").select(Columns.raw('''
  *,
  start_scan:scans!scan_id_start (
    id,
    user_id,
    badge_scan_time
  ),
end_scan:scans!scan_id_end (
    id,
    user_id,
    badge_scan_time
  )
'''));
```

</TabPanel>
</$Show>
<$Show if="sdk:python">
<TabPanel id="python" label="Python">

```python
data = supabase.from_('shifts').select("""
  *,
  start_scan:scans!scan_id_start (
    id,
    user_id,
    badge_scan_time
  ),
  end_scan:scans!scan_id_end (
    id,
    user_id,
    badge_scan_time
  )
""").execute()
```

</TabPanel>
</$Show>
<TabPanel id="graphql" label="GraphQL">

```javascript
const Query = `
  query {
    shiftsCollection {
      edges {
        node {
          id
          user_id
          attendance_status
          scan_id_start {
            id
            user_id
            badge_scan_time
          }
          scan_id_end {
            id
            user_id
            badge_scan_time
          }
        }
      }
    }
  }
`
```

</TabPanel>
</Tabs>