Common mistake

SQL: Why Trailing Spaces Make a Join Match Nothing

A code padded with trailing spaces looks like 'MATH-101' in a results grid, but it is a different value, so the join matches nothing. Join on trim(code), or clean the column once.

The symptom

You count enrollments per school by joining enrollments to the course catalog. Two schools come back. The third, Lakeside High School, is not in the result at all: no error, no zero, just a missing row. Its 413 enrollments are all in the table, and every one of their course codes looks like a code in the catalog.

Why it happens

Lakeside's records came from a legacy system that pads every course code with spaces to ten characters, the way a fixed-width CHAR(10) column does. The padding survived the export, so the enrollment says 'MATH-101 ' where the catalog says 'MATH-101'. A join's = compares text exactly, and a trailing space is a character like any other, so not one Lakeside row finds its course. An inner join drops the rows that match nothing, and the school goes with them.

Nothing on screen gives it away. The results grid shows MATH-101 either way, because a trailing space has no width.

The fix

Join on the trimmed code. trim removes leading and trailing spaces, so the padded codes match the catalog again:

Wrong: join on the raw code

select s.school_name, count(*) as enrollments
from enrollments as e
join courses as c
  on c.course_code = e.course_code
join schools as s
  on s.school_id = e.school_id
group by s.school_name
order by s.school_name

Right: join on the trimmed code

select s.school_name, count(*) as enrollments
from enrollments as e
join courses as c
  on c.course_code = trim(e.course_code)
join schools as s
  on s.school_id = e.school_id
group by s.school_name
order by s.school_name

Lakeside comes back with all 413 enrollments. Northgate gains 3 rows and Ridgeview 5, because a few of their codes had a stray trailing space too. In the full download the same join recovers all 9,828 Lakeside enrollments.

Find the keys that don't match

Before you trust an inner join, look for the rows it would drop. A left join keeps them, with NULL on the catalog side. Wrap each key in brackets to see where the spaces are:

Unmatched codes, with their padding shown

select '[' || e.course_code || ']' as raw_code,
       count(*) as rows
from enrollments as e
left join courses as c
  on c.course_code = e.course_code
where c.course_id is null
group by e.course_code
order by rows desc, raw_code
limit 5;

The first row is [ENG-401 ] with 23 rows. To measure the problem, compare each key's length with its trimmed length:

Padded codes per school

select school_id,
       count(*) as rows,
       count(*) filter (where length(course_code) > length(trim(course_code))) as padded
from enrollments
group by school_id
order by school_id;

Every one of Lakeside's 413 codes is padded, against 3 at Northgate and 5 at Ridgeview. All of the padding here is trailing, but trim handles both ends, so you don't need to know which.

What trim doesn't fix

After trimming, 35 Ridgeview enrollments still match nothing. Their codes were typed by hand: math 101, ART101, sci-401. Those need upper and a regexp_replace that puts the hyphen back, which the regular expressions reference covers. That leaves 6 rows for HLTH-100, a course that is not in the catalog at all. No cleaning should make those match: they are the rows to report, not to fix.

Clean the column once

Trimming inside every join works, but every query has to remember it, and a function on the join key can stop the database from using an index on it. When you own the table, trim the values once:

Trim the padded codes in place

update enrollments
set course_code = trim(course_code)
where course_code <> trim(course_code);

select count(*) as matched
from enrollments as e
join courses as c
  on c.course_code = e.course_code;

The update changes 421 rows, and the plain join then matches 1,020 of the 1,055 enrollments. The grades table has the same padding, so clean it the same way. In the SQLShed workbench the change lasts until you reload the page.

In other databases

The same join can work in one database and fail in another. SQL Server pads the shorter string before comparing, so 'abc' = 'abc ' is true there (LIKE is the exception). MySQL ignores trailing spaces in PAD SPACE collations such as utf8mb4_general_ci, but counts them in the NO PAD collations, which include the 8.x default, utf8mb4_0900_ai_ci. PostgreSQL ignores them when comparing char(n) values, but not in varchar or text. DuckDB and SQLite always count them, and DuckDB reads char(n) as plain VARCHAR. So a join that worked in SQL Server can drop rows after the data moves to PostgreSQL or DuckDB. A leading space breaks the match in every engine.