Join
Combining two tables by matching rows that share a value, such as attaching customer names to orders through a customer ID.
A join can lose rows when values fail to match and multiply rows when a value matches more than once. Count the rows before and after every join.
Where it comes up
- The same entity in two systemsOne person or organization exists in two systems under different identifiers, with nothing linking the records.
- Stray whitespace and invisible charactersTwo values look identical on screen but differ by a trailing space, a non-breaking space, or a hidden character.
- Identifiers that lost their leading zerosA code that starts with zero, such as a postal code or account number, was read as a number and the zeros were dropped.
See also
- Foreign keyA column whose values refer to the primary key of another table, such as the customer ID stored on each order.
- MergeCombining two or more records judged to describe the same thing into a single record. The word is also used for joining two tables, and for merged cells in a spreadsheet; in data cleaning the first meaning is the usual one.
- Primary keyA column, or set of columns, whose value is unique for every row and identifies it: a customer number, an order ID.