FULL OUTER JOIN

A full outer join will return unmatched and matched values from both tables.

For Example:

Let's use the job_history table and regions table. Refer Introduction to know about this table and the database.

In job_history, one records has a region_id, and one record doesn't have a region_id. So, one record doesn't have any matches with the regions table.

select * from regions full outer join job_history on regions.region_id = job_history.region_id;

Result:

Ecto query for full outer join

The join/5 function is used to perform a full outer join in Ecto. To learn more about the join/5 function, visit my JOIN page. Now let's use the :full option in join.

join/5

:full option in join

Expression

For Example:

HR.Region
|> join(:full, [r], c in HR.Country, on: r.region_id == c.region_id)
|> select([r, c], [r,c])
|> HR.Repo.all()

So the above query will return all matching and non-matching records in both tables.

|> join(:full, [r], c in HR.Country, on: r.region_id == c.region_id)

  • It accepts the HR.Region schema as first argument.
  • [r] is the reference variable for the HR.Region schema. To understand reference variables, refer to Aliases in Ecto.
  • c in HR.Country is another schema with a reference variable.
  • on: r.region_id == c.region_id is the condition.

Result:

iex(4)> HR.Region |> join(:full, [r], c in HR.Country, on: r.region_id == c.region_id) |> select([r, c], [r,c]) |> HR.Repo.all()

[
  [
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    },
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "AR",
      country_name: "Argentina",
      region_id: 2
    }
  ],
  [
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 3,
      region_name: "Asia"
    },
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "AU",
      country_name: "Australia",
      region_id: 3
    }
  ],
  [
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 1,
      region_name: "Europe"
    },
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "BE",
      country_name: "Belgium",
      region_id: 1
    }
  ],
  [
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 2,
      region_name: "Americas"
    },
    %HR.Country{
      __meta__: #Ecto.Schema.Metadata<:loaded, "countries">,
      country_id: "BR",
      country_name: "Brazil",
      region_id: 2
    }
  ],

  [
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 5,
      region_name: "India"
    },
    nil
  ],
  [
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 6,
      region_name: "China"
    },
    nil
  ],
  [
    %HR.Region{
      __meta__: #Ecto.Schema.Metadata<:loaded, "regions">,
      region_id: 7,
      region_name: "Korean"
    },
    nil
  ]
]

Keywords

HR.Repo.all(from r in HR.Region, full_join: c in HR.Country, on: r.region_id == c.region_id, select: [r, c])
Back to Homepage