Joins and includes in Ruby on Rails - Step-by-Step Execution

Concept Flow - Joins and includes

Start Query

↓

Choose Join Type

↓

joins

↓

Build SQL

↓

Execute Query

↓

Return Results with or without Preloaded Associations

This flow shows how Rails decides between 'joins' and 'includes' to build and execute queries, either joining tables in SQL or eager loading associations.

Execution Sample

Ruby on Rails

User.joins(:posts).where(posts: { published: true })
User.includes(:posts).where(posts: { published: true })

Two queries: one uses joins to filter users with published posts, the other uses includes to preload posts and filter.

Execution Table

Step	Method Called	SQL Generated	Query Behavior	Result Behavior
1	User.joins(:posts)	SELECT users.* FROM users INNER JOIN posts ON posts.user_id = users.id	Joins users and posts tables	Returns users with matching posts only
2	where(posts: { published: true })	Adds WHERE posts.published = true	Filters joined posts by published	Filters users accordingly
3	Execute Query	Runs SQL with INNER JOIN and WHERE	Fetches filtered users	No posts preloaded, posts accessed separately
4	User.includes(:posts)	SELECT users.* FROM users	Loads users first	Prepares to eager load posts
5	where(posts: { published: true })	Generates LEFT OUTER JOIN or separate query	Filters users by posts.published	May trigger JOIN or separate query depending on conditions
6	Execute Query	Runs SQL and separate posts query or JOIN	Fetches users and posts	Posts are preloaded to avoid N+1 queries
7	Access posts	No new query if preloaded	Uses loaded posts	Efficient access to posts without extra queries
8	End	-	-	Query complete with results

💡 Execution stops after queries run and results are returned with or without preloaded associations.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	Final
users_relation	User.all	User.joins(:posts)	User.joins + where	Query executed, users filtered	User.includes(:posts)	Includes + where applied	Queries executed, users and posts loaded	Posts accessed from preload	Final users with posts loaded

Key Moments - 3 Insights

Why does 'joins' only return users with matching posts but 'includes' can return all users?

'joins' uses INNER JOIN which excludes users without posts (see execution_table step 1). 'includes' uses LEFT OUTER JOIN or separate queries to load all users and their posts (see steps 4-6).

When does 'includes' trigger a JOIN versus separate queries?

'includes' triggers a JOIN if you filter on the included table (step 5). Otherwise, it runs separate queries to preload associations efficiently.

Does accessing posts after 'joins' cause extra queries?

Yes, because 'joins' does not preload posts, accessing posts triggers new queries (step 3). 'includes' preloads posts to avoid this (step 7).

Visual Quiz - 3 Questions

Look at the execution_table at step 1. What SQL does 'User.joins(:posts)' generate?

A SELECT users.*, posts.* FROM users, posts

B SELECT * FROM users LEFT OUTER JOIN posts

C SELECT users.* FROM users INNER JOIN posts ON posts.user_id = users.id

D SELECT * FROM users

💡 Hint

Check the SQL Generated column at step 1 in execution_table.

At which step does 'includes' preload posts to avoid extra queries?

A Step 6

B Step 3

C Step 2

D Step 1

💡 Hint

Look at the Query Behavior and Result Behavior columns around step 6.

If you remove the where condition on posts, how would 'includes' behave differently?

A It would still use INNER JOIN

B It would run separate queries to preload posts

C It would not preload posts

D It would return only users with posts

💡 Hint

Refer to key_moments about when includes triggers JOIN or separate queries.

Concept Snapshot

Rails 'joins' adds INNER JOIN to SQL, returning only records with matching associations.
Rails 'includes' preloads associations to avoid extra queries, using LEFT OUTER JOIN or separate queries.
Use 'joins' to filter by associated table columns.
Use 'includes' to eager load associations for efficient access.
Filtering on included tables may cause 'includes' to use JOIN.
Choose based on whether you want filtering or preloading behavior.

Full Transcript

This visual execution shows how Rails handles 'joins' and 'includes' in queries. 'joins' builds an INNER JOIN SQL query that returns only records with matching associations. 'includes' loads associations eagerly, either by LEFT OUTER JOIN or separate queries, to avoid extra database calls later. Filtering on associated tables with 'joins' narrows results, while 'includes' can preload data for efficient access. The execution table traces each step, showing SQL generated, query behavior, and results. Variable tracking shows how the query relation changes. Key moments clarify common confusions about filtering and preloading. The quiz tests understanding of SQL generated and behavior differences. The snapshot summarizes when to use each method for filtering or eager loading.

Practice

1. What is the main purpose of using joins in Rails ActiveRecord queries?

easy

A. To update multiple tables at once

B. To preload associated records to avoid extra database queries

C. To combine tables and filter records based on matching columns

D. To delete records from multiple tables

Solution

1. Step 1: Understand what joins does

   joins connects tables in a query to filter or find records that match conditions across those tables.

2. Step 2: Compare with includes

   includes is used to preload associated data to reduce queries, not for filtering.

3. Final Answer:

   To combine tables and filter records based on matching columns -> Option C

4. Quick Check:

   joins = combine tables for filtering [OK]

Hint: Remember: joins filters by matching tables, includes preloads data [OK]

Common Mistakes:

• Confusing joins with includes
• Thinking joins preload data
• Assuming joins update or delete records

2. Which of the following is the correct syntax to use includes to preload the comments association on a Post model?

easy

A. Post.includes('comments').where(published: true)

B. Post.includes(:comments).where(published: true)

C. Post.include(:comments).where(published: true)

D. Post.joins(:comments).includes.where(published: true)

Solution

1. Step 1: Check the correct method name and argument type

   The method is includes (plural), and it accepts a symbol like :comments.

2. Step 2: Validate the chaining and syntax

   Post.includes(:comments).where(published: true) uses Post.includes(:comments).where(published: true), which is correct syntax to preload comments and filter posts.

3. Final Answer:

   Post.includes(:comments).where(published: true) -> Option B

4. Quick Check:

   Correct method and symbol argument = Post.includes(:comments).where(published: true) [OK]

Hint: Use plural includes with symbol for associations [OK]

Common Mistakes:

• Using singular include instead of includes
• Passing association as string instead of symbol
• Chaining includes incorrectly

3. Given these models: Author has_many :books and Book belongs_to :author, what will this query return?

Author.joins(:books).where(books: { published: true }).distinct

medium

A. All authors with all their books loaded

B. All books that are published

C. All authors including those without books

D. All authors who have at least one published book

Solution

1. Step 1: Analyze the joins and where clause

   The query joins authors with books and filters books where published: true.

2. Step 2: Understand the effect of distinct

   distinct ensures authors are unique even if they have multiple published books.

3. Final Answer:

   All authors who have at least one published book -> Option D

4. Quick Check:

   joins + where filters authors by published books [OK]

Hint: joins filters parents by child conditions; distinct avoids duplicates [OK]

Common Mistakes:

• Thinking it returns books instead of authors
• Assuming includes loads all books
• Ignoring distinct causing duplicates

4. Identify the error in this Rails query:

Post.joins(:comments).includes(:author).where(comments: { approved: true })

medium

A. No error; the query is valid and will work as expected

B. The where clause should reference authors instead of comments

C. Using both joins and includes together causes a syntax error

D. The includes should come before joins to avoid errors

Solution

1. Step 1: Check if using joins and includes together is allowed

   Rails allows chaining joins and includes to combine filtering and eager loading.

2. Step 2: Verify the where clause references a valid table

   The where filters on comments which is joined, so it's valid.

3. Final Answer:

   No error; the query is valid and will work as expected -> Option A

4. Quick Check:

   joins + includes + where on joined table = valid [OK]

Hint: joins and includes can be chained; where must match joined tables [OK]

Common Mistakes:

• Thinking joins and includes can't be combined
• Confusing which table to filter in where
• Assuming order of joins/includes causes errors

5. You want to fetch all Order records with their associated Customer and OrderItems, but only include orders where the customer is active and at least one order item is in stock. Which query correctly uses joins and includes to achieve this efficiently?

hard

A. Order.joins(:customer, :order_items).includes(:customer, :order_items).where(customers: { active: true }, order_items: { in_stock: true }).distinct

B. Order.includes(:customer, :order_items).where(customers: { active: true }, order_items: { in_stock: true })

C. Order.joins(:customer).includes(:order_items).where(customers: { active: true }).where(order_items: { in_stock: true })

D. Order.joins(:customer).joins(:order_items).where(customers: { active: true }, order_items: { in_stock: true })

Solution

1. Step 1: Understand filtering and eager loading needs

   We must filter orders by customer active status and order items in stock, so joins is needed to filter across tables.

2. Step 2: Use includes to preload associations for efficient access

   To avoid N+1 queries when accessing customers and order items, includes preloads them.

3. Step 3: Use distinct to avoid duplicate orders from multiple matching order items

   Because joins can cause duplicates, distinct ensures unique orders.

4. Final Answer:

   Order.joins(:customer, :order_items).includes(:customer, :order_items).where(customers: { active: true }, order_items: { in_stock: true }).distinct -> Option A

5. Quick Check:

   joins filters, includes preloads, distinct removes duplicates [OK]

Hint: Use joins to filter, includes to preload, distinct to avoid duplicates [OK]

Common Mistakes:

• Using only includes without joins for filtering
• Not using distinct causing duplicate records
• Mixing joins and includes incorrectly