How to query using join in Supabase#

The symptom is usually one of these:

• "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?"

The root cause is that Supabase's JavaScript API is not asking you to write a raw SQL JOIN. It uses embedded relations inside select().

Once you switch mental models, the syntax gets much easier.

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.

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: [].

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.

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.

The ambiguous-foreign-key case#

Supabase's docs also cover the case where a table references the same related table twice.

Example:

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, because the relationship is ambiguous. 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 practical rule set#

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

1. Are you embedding the related table inside select()?
2. Do you actually want default left-join behavior, or do you need !inner?
3. Are you filtering with related_table.column?
4. Is the relationship ambiguous, meaning you need relation!foreign_key(...)?

That four-step 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. But for standard relational reads, Supabase's embedded-join syntax is usually enough.

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

Related#

• How to get COUNT(*) in Supabase