Rails uses self-join for UPDATE with outer joins on PostgreSQL and SQLite

ActiveRecord joins is used to combine records from multiple tables based on associations. In this blog, we will discuss how UPDATE statements with outer joins are handled in PostgreSQL and SQLite.

class Client < ApplicationRecord
  has_many :projects
end

class Project < ApplicationRecord
  belongs_to :client
end

Before

When we do UPDATE with an OUTER JOIN and reference the updated table in the ON clause in PostgreSQL and SQLite, Rails generated subqueries as the join condition cannot be safely moved to the WHERE clause without breaking the query.

Client.joins("LEFT JOIN projects ON projects.client_id = clients.id")
      .where("projects.id IS NULL")
      .update_all(name: 'Archived Client')

UPDATE "clients" 
SET "name" = 'Archived Client' 
WHERE ("clients"."id") IN (
  SELECT "clients"."id" 
  FROM "clients" 
  LEFT JOIN projects ON projects.client_id = clients.id 
  WHERE (projects.id IS NULL)
)

This approach was less efficient, especially for large datasets, and generated more complex SQL. The subquery added unnecessary overhead, making updates slower and harder to optimize.

After

Rails no longer generate the subqueries as it uses self-join for UPDATE with outer joins on PostgreSQL and SQLite. As the updated table is added to the FROM clause with a self-join on the primary key.

UPDATE "clients" AS "__active_record_update_alias" 
SET "name" = 'Archived Client' 
FROM "clients" 
LEFT JOIN projects ON projects.client_id = clients.id 
WHERE (projects.id IS NULL) 
AND "clients"."id" = "__active_record_update_alias"."id" 

Refer

• PR#54722