Supabase Joins: Nested Selects, !inner, Left Joins (2026)

To join tables in Supabase, you embed the related table inside select() instead of writing a SQL JOIN string: supabase.from('parent').select('id, name, child_table ( id, name )'). The embed behaves like a left join by default; append !inner (child_table!inner ( ... )) when you want inner-join semantics, and filter joined columns with dot notation such as .eq('child_table.name', 'value').

That one sentence is the whole mental-model switch. supabase-js talks to PostgREST, and PostgREST models joins as resource embedding — the foreign keys you declared in your schema tell it how two tables connect, so the query string only names the related table. The symptom list this page resolves:

• "How do I write a join in Supabase JS?"
• "Why am I getting parent rows with empty arrays?"
• "Why does my filter on the related table not behave like an inner join?"
• "Why is Supabase asking me to disambiguate the relationship?"

Join syntax cheat sheet#

Every variant below is documented Supabase/PostgREST behavior; the rest of the guide walks through each one.

The basic join pattern#

Supabase documents joins using nested selections. With a simple one-to-many relationship:

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

the joined query is:

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

That gives you sections with nested instruments. Note what you did not write: no ON clause, no junction column. PostgREST resolves the path from the references orchestral_sections foreign key. If no foreign key links the two tables, the embed fails with PGRST200 unless you define a computed relationship (a SQL function), which PostgREST supports for objects that can't carry foreign keys.

Many-to-many works the same way#

For a many-to-many relationship (say teams ↔ users through a team_members junction table), you still name only the target table. Supabase detects the junction table automatically:

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

You do not write team_members in the select string while it is the only path between the two tables. The detection has two conditions, both in PostgREST's v12 embedding reference: the join table "must contain foreign keys to other two tables and they must be part of its composite key". A junction table whose primary key is a lone surrogate id is not detected as many-to-many, and the embed fails with PGRST200; make the two foreign keys the primary key, or include them in it alongside id. When a second junction table links the same pair, the embed becomes ambiguous (PGRST201) and you name the junction table after !, as in users!team_members — the full walkthrough is in the "more than one relationship was found" fix.

Why your filter still returns empty arrays#

This catches a lot of people. Supabase's docs say embedded relations use left join semantics by default.

So this:

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

can still return non-matching parent rows, just with instruments: []. The filter narrowed which embedded rows appear, not which parents appear. For a to-one relationship the non-match shows up as instruments: null instead of an empty array — same rule, different shape.

If you want true inner-join behavior, add !inner:

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

That filters out parent rows that do not match. In PostgREST's own words: "In order to filter the top level rows you need to add !inner to the embedded resource."

The anti-join: parents with no children#

The inverse question — "give me sections that have no instruments" — is a null filter on the embed, which PostgREST executes as an anti-join:

GET /orchestral_sections?select=id,name,instruments(id)&instruments=is.null

And the mirror image, instruments=not.is.null, is documented as equivalent to !inner: only parents that do have matching children. Both run at the PostgREST layer, so they compose with any other filter on the request.

Filtering on the joined table#

Supabase uses joined_table.column in filters:

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

That is the part most SQL-minded examples skip. You do not write WHERE orchestral_sections.name = ... as raw SQL. You use the builder and reference the joined table path.

Counting related rows without fetching them#

You do not need a second query (or a fetched array you .length) to count children. Supabase supports count directly on the embed:

const { data, error } = await supabase
  .from('orchestral_sections')
  .select(`
    *,
    instruments(count)
  `)

Each section comes back with instruments: [{ count: n }]. For counting rows on the top-level table, the options are different — see How to get COUNT(*) in Supabase.

The ambiguous-foreign-key case (error PGRST201)#

When a table references the same related table twice, PostgREST cannot pick a relationship for you. It answers with error code PGRST201 — "Could not embed because more than one relationship was found" — and the response details list every candidate relationship, including the exact hint strings you can use.

Example schema:

create table shifts (
  id serial primary key,
  scan_id_start int references scans,
  scan_id_end int references scans,
  attendance_status text
);

You cannot just write scans(*) here. Supabase documents the fix: explicitly name the foreign-key relationship and alias the result.

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

That alias:relation!foreign_key(...) syntax is the exact thing to reach for when one table points at the same table more than once. The hint after ! can be the referencing column name (as above) or the constraint name, which is what Supabase's own docs use for a messages/users pair:

const { data, error } = await supabase
  .from('messages')
  .select(`
    content,
    from:users!messages_sender_id_fkey ( name ),
    to:users!messages_receiver_id_fkey ( name )
  `)

If you hit PGRST201, read the details array in the error body first — it literally enumerates the valid hints, so there is no need to guess constraint names from the dashboard.

Flattening the embed with spread syntax#

When you want joined columns merged into the parent object rather than nested one level down, PostgREST supports spread embedding with ...:

GET /films?select=title,...directors(director_last_name:last_name)

Each film comes back as { "title": ..., "director_last_name": ... } — no directors wrapper object. The same alias:column renaming works inside the spread. This is the clean way to feed a joined result straight into a flat table UI or CSV export without a mapping pass in JavaScript.

The practical rule set#

When a Supabase join looks wrong, check these in order:

1. Are you embedding the related table inside select()?
2. Does the foreign key actually exist as a constraint? (No FK and no computed relationship, no embed.)
3. Do you actually want default left-join behavior, or do you need !inner?
4. Are you filtering with related_table.column?
5. Is the relationship ambiguous — did the error body say PGRST201? Then you need alias:relation!fk_hint(...).
6. Is the "missing" data actually there, but shaped as [] (to-many) or null (to-one) because the default is a left join?

That checklist resolves most "Supabase joins don't work" bugs without falling back to raw SQL.

When raw SQL or RPC is still the better choice#

If the query needs heavy aggregation, window functions, or complex multi-step write logic, an RPC or SQL function can still be the cleaner production move. Embedded selects cover relational reads; they do not replace GROUP BY rollups or transactional writes. But for standard relational reads — including two levels of nesting, counts, and both join semantics — Supabase's embedded-join syntax is usually enough.

One production caveat that has nothing to do with syntax: every embedded table is still subject to its own Row Level Security policies. A join that "returns nothing" under the authenticated role but works in the SQL editor is usually an RLS problem on the embedded table, not a join problem — walk through the Supabase RLS debug checklist before rewriting the query.

For adjacent database work:

• How to insert into multiple tables with one Supabase API call 2026
• Next.js Supabase Type Safety Guide
• Database Design and Optimization for Next.js and Supabase Applications
• Next.js Data Fetching Patterns with Supabase: Server Components, Streaming, and Caching

References#

• Stack Overflow: How to query using join in Supabase?
• Supabase docs: Querying joins and nested tables
• Supabase docs: select() — querying referenced tables
• PostgREST docs: Resource embedding

Further reading: Fix "More Than One Relationship Was Found" in Supabase.