join

Ruby

left.join(right, [:a, :b, ...])
left.join(right, :a => :x, :b => :y, ...)

TypeScript

left.join(right, ['a', 'b', ...])
left.join(right, { a: 'x', b: 'y' })

Problem

Extend tuples from one relation with matching tuples from another relation, by comparing their attributes according to a given criterion.

Example: I want all customers along with their addresses.

Description

The join of two relations is produced by selecting tuples from left and right relations by one or more matching attributes, and for every match, combine the tuples.

The result’s heading contains all attributes from left plus all attributes from right.

The first variant specifies the names of attributes used for matching. This is used when the attributes to match on have the same names in both tuples.

The second variant first renames some attributes in right, then uses the renamed attributes for matching.

Requirements

The join key attributes must exist in both relations (for array syntax) or in the respective relations (for object syntax).

Examples

Without renaming

Consult the Overview page for the data model used in this example.

Ruby

parts.join(cities, [:city]).to_a

=>
[{:pid=>"P1", :name=>"Nut", :color=>"Red", :weight=>12.0, :city=>"London", :country=>"England"},
 {:pid=>"P2", :name=>"Bolt", :color=>"Green", :weight=>17.0, :city=>"Paris", :country=>"France"},
 {:pid=>"P4", :name=>"Screw", :color=>"Red", :weight=>14.0, :city=>"London", :country=>"England"},
 {:pid=>"P5", :name=>"Cam", :color=>"Blue", :weight=>12.0, :city=>"Paris", :country=>"France"},
 {:pid=>"P6", :name=>"Cog", :color=>"Red", :weight=>19.0, :city=>"London", :country=>"England"}]

Generated SQL

SELECT `t1`.`pid`,
       `t1`.`name`,
       `t1`.`color`,
       `t1`.`weight`,
       `t1`.`city`,
       `t2`.`country`
FROM `parts` AS 't1'
INNER JOIN `cities` AS 't2'
  ON (`t1`.`city` = `t2`.`city`)

TypeScript

parts.join(cities, ['city']).toArray()

// =>
// [{ pid: "P1", name: "Nut", color: "Red", weight: 12.0, city: "London", country: "England" },
//  { pid: "P2", name: "Bolt", color: "Green", weight: 17.0, city: "Paris", country: "France" },
//  { pid: "P4", name: "Screw", color: "Red", weight: 14.0, city: "London", country: "England" },
//  { pid: "P5", name: "Cam", color: "Blue", weight: 12.0, city: "Paris", country: "France" },
//  { pid: "P6", name: "Cog", color: "Red", weight: 19.0, city: "London", country: "England" }]

With renaming

Ruby

purchases = Bmg::Relation.new([
  { id: 1, product_id: 10, quantity: 2 },
  { id: 2, product_id: 10, quantity: 4 },
  { id: 3, product_id: 20, quantity: 1 },
])

products = Bmg::Relation.new([
  { id: 10, name: "Bananas" },
  { id: 20, name: "Oranges" },
  { id: 30, name: "Apples" },
])

# The id attribute in products is matched against
# the product_id attribute in purchases
purchases.join(products, :product_id => :id).to_a

=>
[{:product_id=>10, :name=>"Bananas", :id=>1, :quantity=>2},
 {:product_id=>10, :name=>"Bananas", :id=>2, :quantity=>4},
 {:product_id=>20, :name=>"Oranges", :id=>3, :quantity=>1}]

Generated SQL

SELECT `t1`.`id`,
       `t1`.`product_id`,
       `t1`.`quantity`,
       `t2`.`name`
FROM `purchases` AS 't1'
INNER JOIN `products` AS 't2'
  ON (`t1`.`product_id` = `t2`.`id`)

TypeScript

const purchases = Bmg([
  { id: 1, product_id: 10, quantity: 2 },
  { id: 2, product_id: 10, quantity: 4 },
  { id: 3, product_id: 20, quantity: 1 },
])

const products = Bmg([
  { id: 10, name: "Bananas" },
  { id: 20, name: "Oranges" },
  { id: 30, name: "Apples" },
])

// The id attribute in products is matched against
// the product_id attribute in purchases
purchases.join(products, { product_id: 'id' }).toArray()

// =>
// [{ product_id: 10, name: "Bananas", id: 1, quantity: 2 },
//  { product_id: 10, name: "Bananas", id: 2, quantity: 4 },
//  { product_id: 20, name: "Oranges", id: 3, quantity: 1 }]