SQL: Joining on Names Instead of IDs Is Risky

The symptom

A join seems natural because two tables both have a readable label, like a customer name, product name, category name, or campaign name. Later, the query misses rows or creates duplicates because the label is not a stable key.

Why it happens

Labels are for people. Keys are for relationships. A name can be edited, typed with different casing, translated, reused, or duplicated. An id column is designed to stay stable even when the display name changes.

The fix

Use the foreign key relationship when the schema gives you one. In the Garden Shop data, products connect to categories throughcategory_id, not a category-name string — a label can be broad, stale, reused, or simply spelled differently from the one the categories table stores.

The CTE below stands in for an imported feed that carries a category label but no category id, so the join has nothing but the label to match on.

with imported as (
  select product_name, category_id, 'seeds' as category_name
  from products
  where category_id in (1, 2)
)
select i.product_name, c.category_name
from imported as i
join categories as c on c.category_name = i.category_name
order by i.product_name

select p.product_name, c.category_name
from products as p
join categories as c on c.category_id = p.category_id
where p.category_id in (1, 2)
order by p.product_name

Learn more

• Lesson 5.2 · Joining orders to products
• Reference · JOIN types
• Cheatsheet · Joins