Fuzzy Joins: Handling Approximate Matches

Real-world data almost never lines up cleanly. The same city is New York in one system, new york in another, and " New York " with stray spaces in a third. When you join two tables on a column like that, an exact match quietly drops every row that doesn’t line up character-for-character — and you never see an error, just a suspiciously empty result. Fuzzy joins are how you match records that are close enough instead of identical.

The playground below is a real SQL engine running in your browser. It has a small leads table with messy, human-typed city text and a clean customers table. Run the join as written — an exact match on the raw text — and watch it return nothing:

Zero rows — even though four of the five leads clearly belong to a real customer’s city. That silent emptiness is exactly how bad data corrupts a report nobody double-checks.

Why exact joins fail

An exact join compares text byte-for-byte. 'new york' is not 'New York' (different case), and ' London ' is not 'London' (extra spaces). To a database these are entirely different values, so the join finds no partner and the row vanishes. Look at the raw text next to its normalized form:

TRIM() strips the surrounding whitespace and LOWER() folds the case, so " London " and 'new york' collapse to clean, comparable keys. This is the first and cheapest fuzzy technique: normalize, then join on the normalized values.

Technique 1: normalize, then join

Apply the same normalization to both sides of the ON condition and the match succeeds:

SELECT l.company, c.name
FROM leads l
JOIN customers c
  ON LOWER(TRIM(l.city_raw)) = LOWER(c.city);

Run the “normalize both sides” example in the first playground — the join jumps from 0 rows to 8. The rule is simple: whatever you do to one side of the join, do to the other. Beyond LOWER/TRIM, real pipelines also strip punctuation (Acme, Inc. → acme inc), collapse internal spaces, and standardize abbreviations (St → Street) before matching.

Technique 2: partial matching with LIKE

Normalization handles case and whitespace, but not substrings. When one value contains the other — "Bob" inside "Bob Smith", or a product code buried in a description — reach for LIKE with the % wildcard:

LIKE is still a pattern match, not a similarity match — it can’t tell that "Jon" and "John" are one typo apart. For that you need real distance and similarity functions.

Technique 3: real fuzzy matching — similarity and distance

When values differ by typos, abbreviations, or transpositions, you score how close two strings are and keep the pairs above a threshold. These functions aren’t in this teaching engine (they’re database-specific), but here’s the canonical SQL in each major system:

• Levenshtein (edit) distance — the number of single-character edits to turn one string into another. PostgreSQL’s fuzzystrmatch extension exposes LEVENSHTEIN(a, b); Snowflake computes the same edit distance with EDITDISTANCE(a, b). You keep pairs where the distance is small: WHERE LEVENSHTEIN(a.name, b.name) <= 2.
• Trigram similarity — PostgreSQL’s pg_trgm extension scores overlap of three-character chunks. WHERE similarity(a.name, b.name) > 0.4, or the % operator, with a GiST/GIN index to keep it fast.
• Phonetic matching — SOUNDEX() (SQL Server, MySQL, Oracle) matches by how words sound, catching Smith vs Smyth; SQL Server adds DIFFERENCE() to score how close two SOUNDEX codes are. Snowflake and others offer JAROWINKLER_SIMILARITY().

-- PostgreSQL, pg_trgm: join names that are ~40%+ similar
SELECT a.id, b.id, similarity(a.company, b.company) AS score
FROM source_a a
JOIN source_b b ON a.company % b.company
WHERE similarity(a.company, b.company) > 0.4;

The hard part isn’t the function — it’s choosing a threshold. Too strict and you miss real matches (low recall); too loose and you merge records that shouldn’t be (false positives). Tune it against a labeled sample, and for big tables, block first (only compare records that share a cheap key like the same first letter or postal code) so you aren’t computing similarity across every possible pair.

Where fuzzy joins earn their keep

• Deduplication & entity resolution — the same customer entered three times as Bob Smith, Robert Smith, and bob smith becomes one profile.
• Reconciling systems — matching SKUs, vendors, or accounts across a CRM, an ERP, and a spreadsheet that were never designed to agree.
• Compliance & fraud — screening names against watchlists where an exact match is trivially defeated by a spelling variation.

In each case the exact join looks like it works — it runs without error — while silently under-counting. That’s what makes messy-data bugs so expensive: they’re invisible until someone reconciles the numbers by hand.

Practice

Frequently asked questions

What is a fuzzy join? A join that matches records that are approximately equal rather than identical — using normalization, pattern matching, or string-similarity scoring — so real-world data with typos, casing, and spacing differences still links up.

How do I do a fuzzy join in SQL? Start by normalizing both sides (LOWER(TRIM(...))) and joining on that. For partial matches use LIKE. For typos, use a similarity/distance function — LEVENSHTEIN, pg_trgm’s similarity(), or SOUNDEX — and keep pairs above a threshold.

Why does my join return no rows? The join keys don’t match exactly — usually casing, trailing spaces, or punctuation. Compare the raw values against their LOWER(TRIM(...)) form, and normalize both sides of the ON clause.

Is LIKE a fuzzy join? Partly. LIKE matches substrings and patterns, but it can’t measure similarity, so it won’t catch single-character typos. Use a distance/similarity function for that.

Do it on a canvas instead of by hand

Normalizing keys and re-joining messy sources is the daily grind of data integration. ET1 turns it into a pipeline you can see: a Trim & Normalize node cleans the keys, a Find & Replace node standardizes abbreviations, and the Joiner node matches the cleaned sources — with a live preview of the rows that lined up (and the ones that didn’t) across a CSV, a database, and an API at once.

Keep learning: the joins guide, the join types, and pattern matching with LIKE.