Why joins are important

The main motivation behind joins is to efficiently load data. With joins you can significantly reduce the time it takes to select records that fit the criteria you are looking for.

Slow code without joins

Let’s say we have 2 tables: Employee and Company where a company has many employees.

## == Schema Information
# Table name: employees
# id
# company_id
# name
# status
class Employee < ApplicationRecord
  belongs_to: :company
end
## == Schema Information
## Table name: companies
## id
## name
## verified
class Company < ApplicationRecord
  has_many: :employees
end

If we wanted to load all active employees, we would write a query like this:

Employee.where(status: "active")

Pretty simple. But what if we want to load all active employees who belong to companies that are verified? Without joins, you would write something like this:

active_employees = Employee.where(status: "active")
active_employees.select do |employee|
  employee.company.verified?
end

This isn’t great because it triggers an N + 1 query since it is looking in the database for a company record for each employee in the loop. I ran a similar query in a MySQL database containing 62 employee records and 3 company records. This unoptimized query took about 1 second to run on average.

Improvements with :joins

We can easily improve the code above by using :joins.

Employee.where(status: "active").joins(:company).where(companies: { verified: true })

Running this optimized query in the same database described from above took about 0.5 seconds on average.

Let’s break this down even more

#sql-joins #ruby-on-rails #rails #sql #ruby