Why tables are kept apart
A layer of districts holds shapes and a few fields. The population, the rainfall and the number of schools come from other sources, in other files, collected by other people. Instead of retyping them, you attach them. This is called a join.
The idea comes from relational databases. Data is stored in tables, and tables are linked through a field that identifies the same thing in both.
The join key
A join needs a key in each table. The key is a field whose values identify the entity, such as a district code, a place ID or a ward number. The join looks at each feature, finds the row in the other table with the same key value, and copies its fields across.
A good key is:
- unique in the table you are joining from, so each value points to one row
- stable, so it does not change between versions of the data
- complete, with no empty values
A name is a weak key. "Whitefield" and "Whitefield " with a trailing space are different values, and so are "St. Mark's Road" and "St Marks Road".
What makes two keys match
The values must be equal, and the field types must be compatible. These mismatches cause most failed joins:
- Text and number. The code
07stored as text does not match the number7. - Leading zeros lost. A spreadsheet may turn
07into7when it saves. - Spaces and capital letters. The values look the same on screen and differ in the data.
- Different spellings or codes. A table that uses old district codes will not match one that uses current codes.
After every join, check how many features received a value. A join that matched only half the features is a warning.
Cardinality
Cardinality describes how many rows on one side match rows on the other.
| Relationship | Meaning | Example | Does a join handle it? |
|---|---|---|---|
| One to one | Each feature matches one row | A place ID with one attribute row | Yes |
| Many to one | Many features share one row | Many wards with the same district name, joined to district totals | Yes |
| One to many | One feature matches several rows | A hospital with a table of yearly visit counts | Not fully |
With a one to many match, a simple join keeps one matching row, usually the first, and the rest are ignored without a warning. If you need all the rows, use a relate, which keeps the two tables separate and shows the related rows for each feature. If you need one value, first summarise the table so there is one row per key, for example the total or the latest year.
Features with no match
Features with no matching row get empty values, called NULL. Some tools keep these features, and others drop them. This distinction matters. If you count features after a join, check whether unmatched ones are still there. Missing values also behave in a particular way in queries, as the note on attribute queries explains.
Joins, relates and joins by location
| Method | Links tables by | Use when |
|---|---|---|
| Attribute join | A shared key field | You have a table with an ID that matches the layer |
| Relate | A shared key, without merging | The match is one to many and you need every row |
| Join by location | Where features are | There is no shared key, but the features overlap or are near each other |
A join by location answers questions such as which ward each hospital is in. It uses geometry, so it needs both layers in the same CRS.
Temporary and permanent joins
In many programs a join is a link, not a change to the data. In QGIS, a join is stored in the project, the layer on disk is not altered, and the joined fields disappear if you share only the layer file. To make the joined fields part of the data, export the layer to a new file. Joined fields also keep a prefix, such as attr_, to avoid clashes with existing field names. Some tools, including layer filters, cannot use joined fields, which is another reason to export when you need them.
Example: places and attributes
In the QGIS tutorial, a CSV of ten places with id, name and coordinates becomes a point layer. A second table, place_attributes.csv, has the same id and adds a category and an estimated number of visitors per day. The id field is the key. The join gives each place its attributes with the prefix attr_. The first check is that all ten places received values.
Common mistakes
- Joining on names instead of codes.
- Not checking that the key field types match.
- Joining to a table where the key is not unique.
- Losing leading zeros in a spreadsheet.
- Expecting a one to many join to keep all rows.
- Forgetting that a join is lost when the layer is exported without its joined fields.
Glossary
| Term | Meaning |
|---|---|
| Join | Attaching the fields of one table to another through a key |
| Key | A field that identifies the same entity in two tables |
| Cardinality | How many rows on one side match rows on the other |
| Relate | A link between tables that keeps them separate |
| Join by location | Attaching attributes by position, not by key |
| NULL | The value given to a feature with no match |
Practise it
The tutorial CSV, joins, reprojection and Field Calculator imports a CSV as points and joins a second table on the id field.
Test yourself
1. Why is a place name a poor join key?
Names have spelling differences, extra spaces and duplicates, so values that should match do not, or one value matches several rows.
2. What happens in a simple join when one feature matches several rows?
Usually only one row, often the first, is used, and the others are ignored. Use a relate, or summarise the table first.
3. When would you use a join by location instead of an attribute join?
When the two layers have no shared key, but you can link them by where features are, such as assigning each hospital to the ward it lies in.