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.

You want (SQL)	Supabase select() syntax	Result shape
LEFT JOIN (default)	instruments ( id, name )	Parent rows always returned; embed is [] (to-many) or null (to-one) when nothing matches
INNER JOIN	instruments!inner ( id, name )	Parent rows without a match are dropped
WHERE joined.col = x	.eq('instruments.name', 'flute')	Filters the embedded rows only, unless combined with !inner
Anti-join (WHERE NOT EXISTS)	embed + filter is.null on the embed	Parents with no matching child
Pick one of two FKs to the same table	alias:scans!scan_id_start ( ... )	Explicit relationship + renamed key
Flatten joined columns into the parent	...directors ( last_name ) (spread)	No nested object; columns merged into parent
COUNT(*) of related rows	instruments(count)	[{ count: n }] per parent
Many-to-many through a junction table	users ( id, name ) directly	Junction table auto-detected, never named

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 the foreign key does not exist in the schema, the embed fails — declaring real FK constraints is not optional with this API.

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 never write team_members in the select string. If the auto-detection fails, the usual cause is a junction table whose two foreign keys are not both declared as constraints.

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, 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

Related

• How to get COUNT(*) in Supabase

---

Originally published at https://www.iloveblogs.blog