Comment by jherdman
Composability is the often cited benefit. As an example, I can do the following in Active Record (Ruby):
class Account < ApplicationRecord
scope :active, -> { where.not(active_at: nil) }
belongs_to :user
end
class User < ApplicationRecord
scope :born_before, ->(born_on) { where(birthdate: born_on) }
end
active_users_with_birthdays_today = Account.active.joins(:user).merge(User.born_before(Date.today))
The thought process can be the same in SQL. You can start by writing SELECT * FROM Account; then add your JOIN to User, then add your predicates. Then you can refine it – here, if I’m understanding the code correctly, you’re using User for a JOIN but never return anything from that table, so you could turn it into a semi-join (WHERE EXISTS) and likely get a speed-up.