{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"join-keys-that-do-not-match","title":"When the join key looks right and matches nothing","url":"https://addaly.com/learn/data-and-sql/join-keys-that-do-not-match","position":15,"of":66,"minutes":8,"answer":"Zero matched rows is usually a lie told by a type mismatch, an invisible character, a case or leading-zero difference, or a NULL; the diagnosis is to compare one pair of values by hand.","keyIdea":"Zero matched rows is usually a lie told by a type mismatch, an invisible character, a case or leading-zero difference, or a NULL; the diagnosis is to compare one pair of values by hand."},"body":{"format":"markdown","text":"## Zero rows is an answer, and it is usually a lie\n\nYou write a join you are confident about and get back nothing, or a fraction of what you expected. The condition looks correct. Both tables clearly contain the same customers. This lesson is the checklist, in the order the causes actually occur.\n\n## Cause one: the types differ\n\n```sql\n-- orders.customer_ref is text, customers.id is bigint\nJOIN customers c ON c.id = o.customer_ref\n```\n\nPostgres will refuse this outright with a type error, which is the good outcome. MySQL will silently coerce the text to a number, so `'0012'` becomes 12 and matches, while `'abc'` becomes 0 and matches customer zero. SQLite compares a text value and an integer value as *never equal*, so you get zero rows and no complaint at all.\n\nThree databases, three behaviours, one bug. Check the types before you check anything else:\n\n```sql\nSELECT column_name, data_type FROM information_schema.columns\nWHERE table_name IN ('orders','customers') AND column_name LIKE '%id%';\n```\n\n## Cause two: invisible characters\n\nA CSV export gives you `'CUST-4412 '` with a trailing space, or `'CUST-4412\\r'` because the file has Windows line endings and the last column of each row swallowed the carriage return. Neither is visible when you print it.\n\n```sql\nSELECT '[' || customer_ref || ']', length(customer_ref)\nFROM orders LIMIT 5;\n-- [CUST-4412 ]  10\n```\n\nBracketing the value and printing its length makes both problems obvious in one look. The fix on read is `btrim(customer_ref)`; the better fix is to clean it once on import so every future query does not have to remember.\n\n## Cause three: case and leading zeros\n\n`'ade@example.com'` and `'Ade@Example.com'` are different strings to a case-sensitive database, and the domain part of an email address is officially case-insensitive. Account codes suffer the same fate as `'00412'` versus `'412'`: a system that stored the code as text and another that stored it as a number will disagree forever.\n\nNormalise on both sides, and prefer to do it once at write time. Normalising in the join means no index can help:\n\n```sql\nON lower(btrim(a.email)) = lower(btrim(b.email))   -- correct, and slow\n```\n\n## Cause four: the key is not unique on the side you assumed\n\nYou believe `customers.email` is unique. Check rather than believe:\n\n```sql\nSELECT email, count(*) FROM customers GROUP BY email HAVING count(*) > 1;\n```\n\nAny rows here mean your join will multiply. This is the check that catches the most expensive class of join bug, because unlike the previous three it produces a plausible number rather than an empty result.\n\n## Cause five: NULLs never match\n\n`NULL = NULL` is unknown, not true, so a row with a NULL key never joins to anything, including another NULL. In an inner join it disappears. In a left join it survives with NULLs on the right. Both are correct and neither is announced.\n\nIf you need NULLs to match each other, say so explicitly with `IS NOT DISTINCT FROM`, which treats two NULLs as equal:\n\n```sql\nON a.region IS NOT DISTINCT FROM b.region\n```\n\nUse it rarely and deliberately. Most of the time, a NULL join key means the data is incomplete and joining it away is the honest outcome.\n\n## The diagnosis routine\n\nWhen a join returns the wrong number of rows, do not experiment with join types. Measure, in this order:\n\n```sql\n-- 1. How many keys on each side, and how many are distinct?\nSELECT count(*), count(customer_ref), count(DISTINCT customer_ref) FROM orders;\nSELECT count(*), count(id), count(DISTINCT id) FROM customers;\n\n-- 2. Take five real keys from the left and look for them on the right.\nSELECT DISTINCT customer_ref FROM orders LIMIT 5;\nSELECT * FROM customers WHERE id::text IN ('CUST-4412', ...);\n\n-- 3. How many left rows fail to match?\nSELECT count(*) FROM orders o\nLEFT JOIN customers c ON c.id::text = o.customer_ref\nWHERE c.id IS NULL;\n```\n\nStep two is the one people skip and the one that solves it. Print the actual key from each side, side by side, and look at them. Ninety per cent of the time the answer is visible immediately: one has a prefix, one has padding, one is a different identifier entirely.\n\n## The structural fix\n\nIf you find yourself cleaning a key inside a join condition, that cleaning belongs earlier. Add a normalised column to the table, index it, and join on that:\n\n```sql\nALTER TABLE customers ADD COLUMN email_key text\n  GENERATED ALWAYS AS (lower(btrim(email))) STORED;\nCREATE INDEX ON customers (email_key);\n```\n\nNow the normalisation happens once at write time, the index works, and no future query can forget the rule. A generated column is not available in every database, but a plain column populated on import achieves the same thing.\n\n> Figure: Diagnosing a join key, in the order the causes actually occur. Steps: 1. Count both sides (rows, non-null keys, distinct keys); 2. Print five real keys (bracketed, with their length); 3. Compare the types (text against bigint behaves differently in every engine); 4. Count what missed (LEFT JOIN, right side IS NULL); 5. Fix at write time (a normalised column with an index on it). Do not experiment with join types when a join returns the wrong number of rows. Measure. Step two is the one people skip and the one that solves it: print a real key from each side, bracketed, and look at them."},"check":{"question":"The same join, `ON o.customer_ref = c.ref`, returns 18,000 matched rows in MySQL and zero rows in SQLite against a copy of the same data. What is the most likely explanation?","options":[{"text":"MySQL is using an index that SQLite lacks, so SQLite cannot find the matching rows.","correct":false,"tempting":"An index changes how fast rows are found, never which rows match."},{"text":"The key columns have different types; MySQL coerces one to the other, while SQLite treats a text value and a number as never equal.","correct":true},{"text":"SQLite defaults to an inner join and MySQL defaults to a left join when the join type is omitted.","correct":false,"tempting":"Both databases treat a bare JOIN as an inner join; there is no such default difference."},{"text":"SQLite is case-sensitive on text comparisons and MySQL is not, so the keys differ only in capitalisation and every one of the 18,000 matches depends on it.","correct":false,"tempting":"A genuine difference between the two, but it would still leave some exact-case matches in SQLite rather than exactly zero."}]},"prev":{"slug":"the-four-joins","title":"The four joins, and what each one is for","url":"https://addaly.com/learn/data-and-sql/the-four-joins"},"next":{"slug":"many-to-many-bridge-tables","title":"Many-to-many, and the table in the middle","url":"https://addaly.com/learn/data-and-sql/many-to-many-bridge-tables"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'When the join key looks right and matches nothing', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/join-keys-that-do-not-match (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"When the join key looks right and matches nothing","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"Zero matched rows is usually a lie told by a type mismatch, an invisible character, a case or leading-zero difference, or a NULL; the diagnosis is to compare one pair of values by hand.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"flow","steps":[{"label":"Count both sides","detail":"rows, non-null keys, distinct keys"},{"label":"Print five real keys","detail":"bracketed, with their length"},{"label":"Compare the types","detail":"text against bigint behaves differently in every engine"},{"label":"Count what missed","detail":"LEFT JOIN, right side IS NULL"},{"label":"Fix at write time","detail":"a normalised column with an index on it"}],"title":"Diagnosing a join key, in the order the causes actually occur","caption":"Do not experiment with join types when a join returns the wrong number of rows. Measure. Step two is the one people skip and the one that solves it: print a real key from each side, bracketed, and look at them."},"alt":"Figure: Diagnosing a join key, in the order the causes actually occur. Steps: 1. Count both sides (rows, non-null keys, distinct keys); 2. Print five real keys (bracketed, with their length); 3. Compare the types (text against bigint behaves differently in every engine); 4. Count what missed (LEFT JOIN, right side IS NULL); 5. Fix at write time (a normalised column with an index on it). Do not experiment with join types when a join returns the wrong number of rows. Measure. Step two is the one people skip and the one that solves it: print a real key from each side, bracketed, and look at them."}},"blocks":[{"t":"h2","id":"zero-rows-is-an-answer-and-it-is-usually-a-lie","runs":[{"text":"Zero rows is an answer, and it is usually a lie"}]},{"t":"p","runs":[{"text":"You write a join you are confident about and get back nothing, or a fraction of what you expected. The condition looks correct. Both tables clearly contain the same customers. This lesson is the checklist, in the order the causes actually occur."}]},{"t":"h2","id":"cause-one-the-types-differ","runs":[{"text":"Cause one: the types differ"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- orders.customer_ref is text, customers.id is bigint\nJOIN customers c ON c.id = o.customer_ref"},{"t":"p","runs":[{"text":"Postgres will refuse this outright with a type error, which is the good outcome. MySQL will silently coerce the text to a number, so "},{"text":"'0012'","code":true},{"text":" becomes 12 and matches, while "},{"text":"'abc'","code":true},{"text":" becomes 0 and matches customer zero. SQLite compares a text value and an integer value as "},{"text":"never equal","i":true},{"text":", so you get zero rows and no complaint at all."}]},{"t":"p","runs":[{"text":"Three databases, three behaviours, one bug. Check the types before you check anything else:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT column_name, data_type FROM information_schema.columns\nWHERE table_name IN ('orders','customers') AND column_name LIKE '%id%';"},{"t":"h2","id":"cause-two-invisible-characters","runs":[{"text":"Cause two: invisible characters"}]},{"t":"p","runs":[{"text":"A CSV export gives you "},{"text":"'CUST-4412 '","code":true},{"text":" with a trailing space, or "},{"text":"'CUST-4412\\r'","code":true},{"text":" because the file has Windows line endings and the last column of each row swallowed the carriage return. Neither is visible when you print it."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT '[' || customer_ref || ']', length(customer_ref)\nFROM orders LIMIT 5;\n-- [CUST-4412 ]  10"},{"t":"p","runs":[{"text":"Bracketing the value and printing its length makes both problems obvious in one look. The fix on read is "},{"text":"btrim(customer_ref)","code":true},{"text":"; the better fix is to clean it once on import so every future query does not have to remember."}]},{"t":"h2","id":"cause-three-case-and-leading-zeros","runs":[{"text":"Cause three: case and leading zeros"}]},{"t":"p","runs":[{"text":"'ade@example.com'","code":true},{"text":" and "},{"text":"'Ade@Example.com'","code":true},{"text":" are different strings to a case-sensitive database, and the domain part of an email address is officially case-insensitive. Account codes suffer the same fate as "},{"text":"'00412'","code":true},{"text":" versus "},{"text":"'412'","code":true},{"text":": a system that stored the code as text and another that stored it as a number will disagree forever."}]},{"t":"p","runs":[{"text":"Normalise on both sides, and prefer to do it once at write time. Normalising in the join means no index can help:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ON lower(btrim(a.email)) = lower(btrim(b.email))   -- correct, and slow"},{"t":"h2","id":"cause-four-the-key-is-not-unique-on-the-side-you-assumed","runs":[{"text":"Cause four: the key is not unique on the side you assumed"}]},{"t":"p","runs":[{"text":"You believe "},{"text":"customers.email","code":true},{"text":" is unique. Check rather than believe:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT email, count(*) FROM customers GROUP BY email HAVING count(*) > 1;"},{"t":"p","runs":[{"text":"Any rows here mean your join will multiply. This is the check that catches the most expensive class of join bug, because unlike the previous three it produces a plausible number rather than an empty result."}]},{"t":"h2","id":"cause-five-nulls-never-match","runs":[{"text":"Cause five: NULLs never match"}]},{"t":"p","runs":[{"text":"NULL = NULL","code":true},{"text":" is unknown, not true, so a row with a NULL key never joins to anything, including another NULL. In an inner join it disappears. In a left join it survives with NULLs on the right. Both are correct and neither is announced."}]},{"t":"p","runs":[{"text":"If you need NULLs to match each other, say so explicitly with "},{"text":"IS NOT DISTINCT FROM","code":true},{"text":", which treats two NULLs as equal:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ON a.region IS NOT DISTINCT FROM b.region"},{"t":"p","runs":[{"text":"Use it rarely and deliberately. Most of the time, a NULL join key means the data is incomplete and joining it away is the honest outcome."}]},{"t":"h2","id":"the-diagnosis-routine","runs":[{"text":"The diagnosis routine"}]},{"t":"p","runs":[{"text":"When a join returns the wrong number of rows, do not experiment with join types. Measure, in this order:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- 1. How many keys on each side, and how many are distinct?\nSELECT count(*), count(customer_ref), count(DISTINCT customer_ref) FROM orders;\nSELECT count(*), count(id), count(DISTINCT id) FROM customers;\n\n-- 2. Take five real keys from the left and look for them on the right.\nSELECT DISTINCT customer_ref FROM orders LIMIT 5;\nSELECT * FROM customers WHERE id::text IN ('CUST-4412', ...);\n\n-- 3. How many left rows fail to match?\nSELECT count(*) FROM orders o\nLEFT JOIN customers c ON c.id::text = o.customer_ref\nWHERE c.id IS NULL;"},{"t":"p","runs":[{"text":"Step two is the one people skip and the one that solves it. Print the actual key from each side, side by side, and look at them. Ninety per cent of the time the answer is visible immediately: one has a prefix, one has padding, one is a different identifier entirely."}]},{"t":"h2","id":"the-structural-fix","runs":[{"text":"The structural fix"}]},{"t":"p","runs":[{"text":"If you find yourself cleaning a key inside a join condition, that cleaning belongs earlier. Add a normalised column to the table, index it, and join on that:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE customers ADD COLUMN email_key text\n  GENERATED ALWAYS AS (lower(btrim(email))) STORED;\nCREATE INDEX ON customers (email_key);"},{"t":"p","runs":[{"text":"Now the normalisation happens once at write time, the index works, and no future query can forget the rule. A generated column is not available in every database, but a plain column populated on import achieves the same thing."}]}],"check":{"question":[{"text":"The same join, "},{"text":"ON o.customer_ref = c.ref","code":true},{"text":", returns 18,000 matched rows in MySQL and zero rows in SQLite against a copy of the same data. What is the most likely explanation?"}],"options":[{"text":[{"text":"MySQL is using an index that SQLite lacks, so SQLite cannot find the matching rows."}],"correct":false,"tempting":[{"text":"An index changes how fast rows are found, never which rows match."}]},{"text":[{"text":"The key columns have different types; MySQL coerces one to the other, while SQLite treats a text value and a number as never equal."}],"correct":true,"tempting":null},{"text":[{"text":"SQLite defaults to an inner join and MySQL defaults to a left join when the join type is omitted."}],"correct":false,"tempting":[{"text":"Both databases treat a bare JOIN as an inner join; there is no such default difference."}]},{"text":[{"text":"SQLite is case-sensitive on text comparisons and MySQL is not, so the keys differ only in capitalisation and every one of the 18,000 matches depends on it."}],"correct":false,"tempting":[{"text":"A genuine difference between the two, but it would still leave some exact-case matches in SQLite rather than exactly zero."}]}],"order":[2,3,1,0],"why":null},"prev":{"slug":"the-four-joins","title":"The four joins, and what each one is for","minutes":8},"next":{"slug":"many-to-many-bridge-tables","title":"Many-to-many, and the table in the middle","minutes":8}}}