---
title: "Support for left outer join in Rails 5"
description:
  "No need of writing SQL in your code every time there is need to do left outer
  join"
canonical_url: "https://www.bigbinary.com/blog/support-for-left-outer-joins-in-rails-5"
markdown_url: "https://www.bigbinary.com/blog/support-for-left-outer-joins-in-rails-5.md"
---

# Support for left outer join in Rails 5

No need of writing SQL in your code every time there is need to do left outer
join

- Author: Ratnadeep Deshmane
- Published: March 24, 2016
- Categories: Rails 5, Rails

Suppose in a blog application there are authors and posts. A post belongs to an
author, while author has many posts.

The app needs to show a list of all the authors along with a number of posts
that they have written.

For this, we need to join author and posts table with "left outer join". More
about "left outer join"
[here](http://blog.codinghorror.com/a-visual-explanation-of-sql-joins),
[here](http://www.dofactory.com/sql/left-outer-join) and
[here](http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server)
.

In Rails 4.x, we need to write the SQL for left outer join manually as Active
Record does not have support for outer joins.

```ruby
authors = Author.join('LEFT OUTER JOIN "posts" ON "posts"."author_id" = "authors"."id"')
                .uniq
                .select("authors.*, COUNT(posts.*) as posts_count")
                .group("authors.id")
```

Rails 5 has [added left_outer_joins](https://github.com/rails/rails/pull/12071)
method.

```ruby
authors = Author.left_outer_joins(:posts)
                .uniq
                .select("authors.*, COUNT(posts.*) as posts_count")
                .group("authors.id")
```

It also allows to perform the left join on multiple tables at the same time.

```ruby
>> Author.left_joins :posts, :comments
  Author Load (0.1ms)  SELECT "authors".* FROM "authors" LEFT OUTER JOIN "posts" ON "posts"."author_id" = "authors"."id" LEFT OUTER JOIN "comments" ON "comments"."author_id" = "authors"."id"
```

If you feel `left_outer_joins` is too long to type, then Rails 5 also has an
alias method `left_joins`.

## Links

- [Human page](https://www.bigbinary.com/blog/support-for-left-outer-joins-in-rails-5)