🎓 A1Learner
🔍
0 of 48 lessons completed

Julia Joining Tables

Joining tables matches rows from two lists on a shared key. The shared key links a row in one list to a row in the other list.

A lookup finds a name by its key

Customers are stored by their number. A number gives its customer name.

struct Customer
 id::Int
 name::String
end
customers = [Customer(1, "Ana"), Customer(2, "Bo")]
lookup = Dict(c.id => c.name for c in customers)
println(lookup[1])
println(lookup[2])

Orders match customers through the lookup

Each order carries a customer number. The lookup turns each number into a name.

struct Customer
 id::Int
 name::String
end
struct Order
 customer_id::Int
 amount::Int
end
customers = [Customer(1, "Ana"), Customer(2, "Bo")]
orders = [Order(1, 50), Order(2, 30)]
lookup = Dict(c.id => c.name for c in customers)
matched = [lookup[o.customer_id] for o in orders]
println(matched)

Matched pieces combine into one string

Matched names form a list. The list prints as one string with commas between names.

struct Customer
 id::Int
 name::String
end
struct Order
 customer_id::Int
 amount::Int
end
customers = [Customer(1, "Ana"), Customer(2, "Bo")]
orders = [Order(1, 50), Order(2, 30)]
lookup = Dict(c.id => c.name for c in customers)
matched = [lookup[o.customer_id] for o in orders]
println(matched)
println(join(matched, ", "))

Example

struct Employee
 id::Int
 name::String
 dept_id::Int
end
struct Department
 id::Int
 title::String
end
employees = [Employee(1, "Ana", 1), Employee(2, "Bo", 2)]
departments = [Department(1, "Sales"), Department(2, "Support")]
lookup = Dict(d.id => d.title for d in departments)
for e in employees
 println(join([e.name, lookup[e.dept_id]], " - "))
end

Key points

  • Join: a match between rows from two lists on a shared key.
  • Use a Dict to look up a row by its shared key.
  • Use the shared key to find the matching row, not the row position.
  • Use join to combine matched pieces into one printed string.
  • Use a lookup built once to match many rows without searching each time.

In this example, departments are stored in a lookup by number, each employee is matched to a department through that number, and each matched pair prints as one joined string.

Note

A lookup fails for a key that is absent, so every matched key must exist in the lookup.

Try it Yourself

Edit the code, then press Run.

Running is simulated · JuliaHub

Exercise

Orders carry customer numbers, and customers are stored in a Dict by number. How is an order matched to its customer?