Relational algebra, SQL, and Arel

The algebra underneath SQL

Relational algebra is useful because it names the shapes that SQL uses every day: filter rows, choose columns, combine relations, group values, and project a new result. Rails developers often use these ideas without naming them.

SQL is not written in pure relational-algebra notation, but the mental model carries over. A query is a transformation from one or more relations into another relation-shaped result.

Arel sits underneath Active Record as a way to build SQL through objects. It is most useful when the query shape is easier to compose as an abstract syntax tree than as a long string.

Selection and projection

Selection means filtering rows. In SQL that is the WHERE clause. In Active Record it is usually where. The relation still has the same columns, but fewer rows.

Projection means choosing or computing columns. In SQL that is the SELECT list. In Active Record it may be select or pluck. Projection changes the shape of what comes back.

Keeping those ideas separate helps avoid mistakes. A query that filters correctly can still load too many columns. A query that selects the right columns can still return the wrong row set.

Joins

A join combines relations through a predicate. Inner joins keep matching rows. Outer joins preserve one side even when the other side is missing. This difference is one of the most common sources of subtle bugs in reporting queries.

Rails makes joins look simple, but the relational shape still matters. Joining a one-to-many association multiplies rows. Adding pagination, ordering, or counting after that multiplication can produce surprising results.

The useful habit is to ask what relation exists after the join. Is it still one row per article, or one row per article-comment pair? The answer decides whether distinct, grouping, preloading, or a different query is needed.

Grouping and aggregation

Grouping turns many rows into fewer rows by key. Aggregates such as count, sum, min, and max summarize each group. SQL makes this powerful, but it also makes invalid mental models visible quickly.

Every selected value must either belong to the grouping key or be produced by an aggregate. When Rails hides SQL construction, it is easy to forget that the database still enforces this rule.

For application code, grouping queries deserve named scopes or query objects when they represent product behavior. A dense group query buried in a controller is hard to review because it mixes relational logic with response logic.

Arel as an AST

Arel is useful when a query needs composition that Active Record does not express cleanly. It can build predicates, joins, SQL functions, and ordering rules while still letting Rails handle quoting and adapter details.

That does not mean every SQL fragment should become Arel. Simple SQL is often clearer than clever node construction. Arel earns its place when it removes unsafe string assembly or makes repeated query structure easier to combine.

The practical rule is to keep the relational idea visible. Whether the query is written as SQL, Active Record, or Arel, a reviewer should still be able to identify selection, projection, join, grouping, and ordering.

Arel as a relational builder

Arel is useful when query structure is conditional but still relational. Instead of concatenating SQL fragments, build an abstract syntax tree and let Rails compile it.

articles = Article.arel_table

predicate = articles[:published_at].not_eq(nil)
predicate = predicate.and(articles[:title].matches("%#{query}%")) if query.present?
predicate = predicate.and(articles[:author_id].eq(author_id)) if author_id.present?

Article.where(predicate).order(published_at: :desc)

This is not a reason to rewrite every query in Arel. Ordinary Active Record relations are clearer most of the time. Arel earns its keep when the query has dynamic predicates, SQL functions, grouped conditions, or joins that become brittle as strings.

orders = Order.arel_table
paid_total = orders[:total_cents].sum.as("paid_total_cents")

Order
  .where(orders[:status].eq("paid"))
  .select(orders[:account_id], paid_total)
  .group(orders[:account_id])

The benefit is not abstraction for its own sake. It is keeping the query composable while still being honest about the SQL shape underneath.

When to stop using Arel

Arel is not always the most honest form. If the query is easier to read as SQL, use SQL. A carefully named scope around a plain SQL fragment can be more maintainable than a dense tree of Arel nodes that only one person understands.

The dividing line is reviewability. Arel helps when it prevents unsafe string assembly or keeps optional predicates composable. It hurts when it hides the final query shape. Complex reporting queries, recursive CTEs, window functions, and database-specific features may deserve explicit SQL with tests around the result.

This is especially true when explaining the query to someone who thinks in SQL. If the Arel version requires mentally compiling the AST before review can begin, the abstraction is adding distance instead of safety.