SQL Joins in Rails
Fast recall
Quick revision
Core answer
INNER JOIN keeps only matching rows; LEFT OUTER JOIN keeps every left-side row and fills missing right-side columns with NULL. In Rails, joins(:comments) normally creates an inner join for filtering, while left_joins(:comments) preserves records without comments. A join does not automatically preload the association, and one-to-many joins can duplicate parent rows, so selection, distinct, grouping, and eager loading must be chosen for the actual result needed.Whether you know joins (INNER/LEFT) filter by an association, and that joins doesn't load the association (use includes/eager_load to load).
Expecting joins(:posts) to load posts (it only filters; use includes/eager_load to load them).
joins(:posts) = INNER JOIN (filter by association); left_joins = LEFT JOIN. joins filters, includes/eager_load loads. Use distinct to dedupe.
Interview-ready guide
Interview answer
A join asks the database to combine rows from related tables using an ON condition, usually a foreign key to primary key relationship. An inner join returns only pairs that satisfy the condition. A left outer join also returns left-table rows with no match, using NULL for the right-side columns.
In Rails, Post.joins(:comments) uses the association metadata to build an inner join. It is useful for a condition such as posts with approved comments. Post.left_joins(:comments) preserves posts that have no comments, which enables questions such as finding rows where the joined comment ID is null.
For example, one post with three approved comments appears in three joined database rows. If the application needs unique Post objects, distinct may be required. If it needs counts, group and an aggregate express that result more directly than loading the repeated rows into Ruby.
joins and eager loading solve different problems. A join shapes which rows match; later calling post.comments can still issue another query. preload, includes, or eager_load loads association data, with different query shapes. I choose them based on filtering and access rather than assuming any join removes N+1 queries.
The model association is application behaviour, while a database foreign-key constraint protects referential integrity for every writer. An index on the foreign-key column often supports join lookup. I check nullability, duplicate parent rows, missing relationships, and the generated SQL before deciding the join is correct.
SQL Joins in Rails
Direct Answer
A SQL join combines rows through a relationship condition. INNER JOIN keeps only matching rows; LEFT OUTER JOIN keeps every left-side row and fills missing right-side columns with NULL. In Rails, joins(:comments) normally creates an inner join for filtering, while left_joins(:comments) preserves records without comments. A join does not automatically preload the association, and one-to-many joins can duplicate parent rows, so selection, distinct, grouping, and eager loading must be chosen for the actual result needed.
Deep Dive & Architecture
Imagine the left table as the set of posts and the right table as comments. The join condition compares each relevant foreign key. An inner join discards a post when no comment satisfies it. A left join emits one row for that post anyway, with null comment columns. A condition placed in WHERE can accidentally remove those null rows and turn the intended outer behaviour back into inner-like filtering.
Join cardinality determines result multiplication. A belongs-to join contributes at most one matched parent for each child when the key is valid. A has-many join can produce many rows for one parent. This is not a database bug; the relational result represents every matching pair.
Active Record usually returns model instances from the table named before joins, not a new combined object containing every column. Custom projections, aggregates, or plucked values should name aliases and result shapes clearly. Loading full models after grouping can be misleading when selected columns no longer represent one complete row.
Performance follows the join keys, selectivity, and query plan. A foreign key enforces validity but does not replace deliberate indexing in every database. Explain output shows whether the database scans, uses an index, changes join order, or estimates far more rows than expected.
Code Example
# Posts having at least one approved comment, once per post.
reviewed = Post
.joins(:comments)
.where(comments: { approved: true })
.distinct
# Posts with no associated comments.
without_comments = Post
.left_joins(:comments)
.where(comments: { id: nil })
# Joining does not promise that post.comments is preloaded.
Common Mistakes & Pitfalls
Expecting joins(:posts) to load posts (it only filters; use includes/eager_load to load them).
Check your understanding
Answer these without looking back at the article.
- How can a WHERE condition accidentally remove the unmatched side of a left join?
- Why can a has-many join duplicate parent records?
- What is the difference between joining an association and preloading it?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.