When the join key looks right and matches nothing

Zero rows is an answer, and it is usually a lie

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.

Cause one: the types differ

-- orders.customer_ref is text, customers.id is bigint
JOIN customers c ON c.id = o.customer_ref

Postgres 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.

Three databases, three behaviours, one bug. Check the types before you check anything else:

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name IN ('orders','customers') AND column_name LIKE '%id%';

Cause two: invisible characters

A 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.

SELECT '[' || customer_ref || ']', length(customer_ref)
FROM orders LIMIT 5;
-- [CUST-4412 ]  10

Bracketing 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.

Cause three: case and leading zeros

'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.

Normalise on both sides, and prefer to do it once at write time. Normalising in the join means no index can help:

ON lower(btrim(a.email)) = lower(btrim(b.email))   -- correct, and slow

Cause four: the key is not unique on the side you assumed

You believe customers.email is unique. Check rather than believe:

SELECT email, count(*) FROM customers GROUP BY email HAVING count(*) > 1;

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.

Cause five: NULLs never match

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.

If you need NULLs to match each other, say so explicitly with IS NOT DISTINCT FROM, which treats two NULLs as equal:

ON a.region IS NOT DISTINCT FROM b.region

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.

The diagnosis routine

When a join returns the wrong number of rows, do not experiment with join types. Measure, in this order:

-- 1. How many keys on each side, and how many are distinct?
SELECT count(*), count(customer_ref), count(DISTINCT customer_ref) FROM orders;
SELECT count(*), count(id), count(DISTINCT id) FROM customers;

-- 2. Take five real keys from the left and look for them on the right.
SELECT DISTINCT customer_ref FROM orders LIMIT 5;
SELECT * FROM customers WHERE id::text IN ('CUST-4412', ...);

-- 3. How many left rows fail to match?
SELECT count(*) FROM orders o
LEFT JOIN customers c ON c.id::text = o.customer_ref
WHERE c.id IS NULL;

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.

The structural fix

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:

ALTER TABLE customers ADD COLUMN email_key text
  GENERATED ALWAYS AS (lower(btrim(email))) STORED;
CREATE INDEX ON customers (email_key);

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.