PostgREST/Supabase: joins and join conditions
published
A look at how postgREST does joins and the gotchas involved
![PostgREST joins reference](/_app/immutable/assets/postgrest-joins.CMVsv1-c.png)
Unspecified: left joins
![PostgREST left/normal joins](/_app/immutable/assets/left.DU7FNHJP.png)
PostgREST
Supabase/postgrest-js
GET /users?select=name,posts(text)
A regular PostgREST embed.
!inner
for inner joins
![PostgREST inner joins](/_app/immutable/assets/inner.DpN_pPdL.png)
PostgREST
Supabase/postgrest-js
GET /users?select=name,posts!inner(text)
Only include rows that exist in both tables.
Left join with condition
![PostgREST left/normal joins with condition](/_app/immutable/assets/left-w-cond.Bqw5rBeI.png)
PostgREST
Supabase/postgrest-js
GET /users?select=name,posts(text)&posts.text=hello
Filters results in the embedded table (posts
). Importantly, this will not filter the top-level users
at all. Use inner joins with conditions for that.
Inner join with condition
![PostgREST inner joins with condition](/_app/immutable/assets/inner-w-cond.DJ0nqX-q.png)
PostgREST
Supabase/postgrest-js
GET /users?select=name,posts!inner(text)&posts.text=hello
Filters results in the embedded table (posts
) while also only keeping users
that have at least one embedded post
.
Null condition for anti-joins
![PostgREST anti-joins](/_app/immutable/assets/antijoin.BUinNJOM.png)
PostgREST
Supabase/postgrest-js
GET /users?select=name,posts()&posts=is.null
Skip users
for which there exists a post
.
count
for an embedded resource count
![PostgREST embedded resource count](/_app/immutable/assets/left-count.CndgMxyp.png)
PostgREST
Supabase/postgrest-js
GET /users?select=name,posts(count)
When count
is the only column for an embedded resource, PostgREST returns the number of items in the relationship.
While this cannot be directly combined with actually returning embedded relationships data, you can embed the same table twice:
PostgREST
Supabase/postgrest-js
GET /users?select=name,posts(text),post_count:posts(count)