Basics

Understanding how joins work helps you set them up successfully.

Understanding joins

What is a join?

Joining is a method to combine information from two different tables as long as they have something in common. In databases (and in Polyteia), this common element is typically a column – like a name, ID, or code.

You can think of joins like matching puzzle pieces. Each table contains part of the needed information, and the join helps you see the complete picture.

Real-life example: A wedding guest list and seating chart

The wedding planner

Imagine you're organizing a wedding.

You have two lists:

1. Guest list (Table A)

Guest Name	RSVP	Menu Choice
Anna	Yes	Vegan
Ben	Yes	Chicken
Carla	No	-
David	Yes	Fish

2. Seating chart (Table B)

Guest Name	Table Number
Anna	3
Ben	1
David	2
Emma	4

Each table contains different information:

• Table A knows who's invited, who's coming, and what they want to eat.

• Table B knows which guest sits at which table.

The first join

We use the Guest Name column to match rows. This is our join key – the common piece of information.

When we join Table A and Table B, we can combine their data in one table like this:

Left join (keep everyone from the guest list)

Guest Name	RSVP	Menu Choice	Table Number
Anna	Yes	Vegan	3
Ben	Yes	Chicken	1
Carla	No	-	(null)
David	Yes	Fish	2

This new table gives us the complete picture:

• We know who's coming

• What they want to eat

• Where they'll be sitting

You can now easily print place cards with names and menu choices and ensure everyone has a seat.

Note:

• Carla is on the guest list but not in the seating chart, so her Table Number is empty.

• Emma is in the seating chart but not on the guest list – she doesn't appear in the result.

This type of join is called a Left Join: Keep everyone from the first table (the guest list) and supplement what's available from the second table (the seating chart).

Inner Join (only what exists in both tables)

Guest Name	RSVP	Menu Choice	Table Number
Anna	Yes	Vegan	3
Ben	Yes	Chicken	1
David	Yes	Fish	2

Only guests who appear in both the guest list and seating chart are shown. Carla and Emma are excluded.

Right join (keep everything from the seating chart)

Guest Name	RSVP	Menu Choice	Table Number
Anna	Yes	Vegan	3
Ben	Yes	Chicken	1
David	Yes	Fish	2
Emma	(null)	(null)	4

Everyone in the seating chart is included, even if they're not on the guest list. Carla is excluded, but Emma is included.

Full join (everyone from both lists)

Guest Name	RSVP	Menu Choice	Table Number
Anna	Yes	Vegan	3
Ben	Yes	Chicken	1
Carla	No	-	(null)
David	Yes	Fish	2
Emma	(null)	(null)	4

Here we keep everyone from both tables – no one is left out. This is the most complete, but also the most complex view.

Cross join (every possible combination)

A cross join combines every row from Table A with every row from Table B. It doesn't use a common column – it simply connects everything with everything.

In our wedding example, this would mean showing each guest with every possible table, even if they don't match:

Guest Name (A)	RSVP	Menu Choice	Guest Name (B)	Table Number
Anna	Yes	Vegan	Anna	3
Anna	Yes	Vegan	Ben	1
Anna	Yes	Vegan	David	2
Anna	Yes	Vegan	Emma	4
Ben	Yes	Chicken	Anna	3
Ben	Yes	Chicken	Ben	1
Ben	Yes	Chicken	David	2
Ben	Yes	Chicken	Emma	4
Carla	No	-	Anna	3
...	...	...	...	...

This results in many rows – every possible combination. It's rarely useful unless you want to test all combinations (e.g., checking compatibility or matching scenarios).

---

The technical side

You now understand joins through a practical example, but let's translate that to how databases actually work.

In databases, each table contains rows of data, and each row contains columns. When we talk about joins, we use SQL (Structured Query Language) to combine tables based on keys – common columns.

Here's a simplified overview of how it works:

• Join key: The common column used to join the tables. In our example, it's Guest Name.

• Join type: This tells the system what to keep:

Join Type	Description
Left Join	Keeps all rows from the left (first) table and matching rows from the right table.
Right Join	Keeps all rows from the right (second) table and matching rows from the left table.
Inner Join	Keeps only the rows that have matching values in both tables.
Full Join	Keeps all rows from both tables. Missing values are shown as null.
Cross Join	Combines every row from the first table with every row from the second. No join key required.

This logic applies to any dataset you'll work with in Polyteia. Whether you're combining program enrollments with city data or merging statistics with geographies – joins help you create meaning across datasets.

Joins in Polyteia

In Polyteia, your tables are typically datasets. One might contain registrations, another demographic data, another performance numbers. By joining, you can answer questions like:

• How many people registered for a program by city?

• Which cities have low income and high need?

You'll learn how to do this in the next article: Joining Datasets in Polyteia.

But now that you understand the idea, you're already halfway there!