Join tables on a key

Status

The DuckDB step

Variants

Choose the join type

Join two tables on equal keys with one operator whose setting picks inner, left, right or full outer.

Keep only matching rows

Join two tables on equal keys and keep only the rows whose key is found in both.

Join on shared column names

Join two tables on every column whose name they share, without naming the keys.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Background

When to use it

An inner join leaves out rows that have no match in the other table, and depending on the result wanted that can be a subtle bug, avoided by using an outer join.^[1] Reach for an inner join when a row without a partner means nothing to the question, and for an outer join whenever the unmatched rows are part of the answer. (Meridian's view) Count the rows of each input and of the result: a total you did not expect is the quickest sign of a dropped or repeated key. (Meridian's view)

In PostgreSQL, an inner join gives, for each row of the first table, one row for each row of the second table that satisfies the join condition with it.^[2] In dplyr, a many-to-many relationship, where a row on each side matches several rows on the other, can cause a Cartesian explosion in the number of rows returned.^[3] dplyr warns by default when an equality join meets a many-to-many relationship.^[3] dplyr's relationship argument throws an error when the data breaks the expected relationship, such as one-to-one or many-to-one.^[3] State the relationship you expect between the keys and let the tool check it, rather than finding the fan-out later in a total. (Meridian's view)

In SQL, NULL never matches any other value in a join condition, not even another NULL.^[1] pandas merge matches rows whose keys are both null against each other, which differs from usual SQL join behaviour.^[4] In dplyr, joins treat two NA values as equal by default, and na_matches = "never" stops them matching, which is similar to joins for database sources.^[3] Decide what a missing key should mean before you join, and check how your tool treats it. (Meridian's view)

Tableau Prep reads values that differ in capitalisation, spelling or extra spaces as not matching, and excludes them from the join.^[5] Standardise the key columns, their case, spacing and type, before the join rather than after it. (Meridian's view)

A natural join compares all the columns the two tables share by name, so the join columns are not written in the query and a change in column names may change the results.^[1] Name the key columns with USING or ON, so that adding a column to either table cannot change what the join matches on. (Meridian's view)

The method

The SQL join corresponds to the join of relational algebra, a theory introduced by Edgar F. Codd as part of his relational model of data in 1970.^[1][6] Outer joins are not considered part of the classical relational algebra, and they assume a null value to fill the attributes of an unmatched row.^[6]

The result of an inner join can be defined as the Cartesian product of the tables, keeping only the rows that satisfy the join predicate.^[1] Actual SQL implementations normally use other approaches, such as hash joins or sort-merge joins, since computing the Cartesian product is slower and would often need a prohibitively large amount of memory.^[1] Three fundamental algorithms perform a binary join: the nested loop join, the sort-merge join and the hash join.^[1] A hash join builds a hash table from one input, ideally the smaller, then probes it with the rows of the other, so only rows with the same hash code are compared for equality.^[7] A hash join needs an equijoin predicate, a conjunction of equality comparisons on one or more columns.^[7] A sort-merge join first sorts both inputs on the join attribute, so that interleaved linear scans meet the matching rows of each value at the same time.^[8]

In PostgreSQL, USING (a, b) forms an equality comparison for each listed column, and its output shows each listed column once rather than once per table.^[2]

Compared with the alternatives

A cross join returns every combination of a row of the first table with a row of the second, and filtering it with a WHERE clause can give the equivalent of an inner join.^[1] In PostgreSQL, T1 CROSS JOIN T2 is equivalent to T1 INNER JOIN T2 ON TRUE.^[2] In DuckDB, a semi join returns the rows of the left table that have at least one match in the right table, and its result never has more rows than the left table.^[9] Where the question is only whether a match exists, reach for a semi join rather than a keyed join followed by DISTINCT. (Meridian's view)

Sources

1. [1]Join (SQL) - Wikipediasecondary source
2. [2]PostgreSQL: Documentation: 18: 7.2. Table Expressionsdocumentation
3. [3]Mutating joins — mutate-joins • dplyrdocumentation
4. [4]pandas.merge — pandas 3.1.1 documentationdocumentation
5. [5]Aggregate, Join, or Union Data - Tableaudocumentation
6. [6]Relational algebra - Wikipediasecondary source
7. [7]Hash join - Wikipediasecondary source
8. [8]Sort-merge join - Wikipediasecondary source
9. [9]FROM and JOIN Clauses – DuckDBdocumentation

Types it works with

Found by dovetail

Used in