Here’s a question you probably haven’t asked yourself in years: what happens when you assume a SQL JOIN behaves one way, but the database engine has other ideas?
I’m asking because I watched someone—okay, fine, it was me—spend three hours debugging a query that failed with a cryptic error: “Table ‘test.p’ doesn’t exist.” The table was right there. The schema was fine. The problem wasn’t the database. It was me misunderstanding the most basic rule of SQL aliasing, and then compounding that mistake with another wrong assumption about how JOINs work by default.
This wasn’t some edge case or obscure MySQL behavior. This was me getting caught on LeetCode problem #175, a problem so simple it’s used to onboard junior developers. And you know what? That embarrassment taught me something worth sharing.
The Alias Order Trap That Tanked My Query
I wrote this:
FROM P Person
JOIN Address A
And got slapped with a runtime error. The reason? I had the alias syntax backwards.
In SQL, the rule is: [Table Name] [Alias]. Always. No exceptions. What I’d written told the database engine that P was the actual table name and Person was just a nickname for it. Since there was no table called P—the real table was Person—the whole thing exploded.
It’s a stupid mistake once you know it. But here’s the thing: I’ve been writing SQL for years, and I still did it. Why? Because when you’re moving fast, when you’re focused on the logic of your query rather than the mechanics, these tiny syntax rules slip past you like they don’t exist.
The fix was immediate:
FROM Person p
JOIN Address a
One letter at a time, in the right order. Done.
The Bigger Problem: What Plain JOIN Actually Does
But fixing the alias syntax only revealed a second problem. My logic was still wrong.
The original problem asked me to combine two tables and report null values when an address didn’t exist for a given person. I’d written a plain JOIN—no LEFT, no RIGHT, nothing. Just JOIN. And I assumed it would keep all the people, filling in nulls where addresses were missing.
“In standard SQL (MySQL, PostgreSQL, etc.), JOIN is shorthand for INNER JOIN. And INNER JOIN only returns rows when there is a match in both tables.”
Wrong assumption. Dead wrong.
Let me be direct about this: a plain JOIN in SQL defaults to INNER JOIN. Not LEFT. Not RIGHT. INNER. And INNER JOIN is a brutal filter—it only returns rows where there’s a match in both tables. If someone has no address on file, they’re gone. Completely removed from your results. No null values. No record at all.
If the problem wanted every person reported (with nulls for missing addresses), I needed a LEFT JOIN. That keeps all rows from the left table (Person) and fills in NULL for anything missing from the right table (Address).
Here’s what correct actually looks like:
SELECT
p.firstName,
p.lastName,
a.city,
a.state
FROM Person p
LEFT JOIN Address a
ON p.personId = a.personId;
Two small changes. Different results entirely.
Why This Matters More Than You Think
Look, I know this sounds like basic SQL stuff. And it is. But here’s what I realized: the reason I made these mistakes isn’t stupidity. It’s that I’d stopped asking questions about default behavior. I’d been using JOINs so long that I’d internalized bad assumptions. I skimmed documentation instead of reading it. I trusted my muscle memory instead of double-checking.
And in production code, those kinds of assumptions kill you. Slowly, invisibly, in ways that won’t show up until someone notices that your customer report is missing 30% of the data.
The scarier part? This isn’t just me. I talk to developers constantly who’ve made the same mistake. They’ll confidently write a JOIN without specifying LEFT or INNER, get the wrong result set, spend hours investigating, and only then realize they never checked the default behavior.
So here’s what I learned, the hard way:
Alias syntax is [Table] [Alias], always. Not [Alias] [Table]. Write it wrong once, and you’ll spend time debugging something that should’ve been obvious.
Never trust default behavior. If you care about the type of join you’re doing—and you should—say it explicitly. Be specific. LEFT JOIN, RIGHT JOIN, INNER JOIN. Don’t abbreviate it down to JOIN and hope the database guesses your intention.
Read the requirements obsessively. If a problem says “report null if not present,” that’s not a casual suggestion. That’s a direct hint that you need an outer join. INNER JOIN will silently drop those rows. You won’t notice until someone screams about missing data.
The Bigger Picture
Here’s what bugs me about this whole situation: LeetCode problems are designed to be simple tests of foundational knowledge. And I still stumbled. Not because the logic was hard, but because I wasn’t thinking carefully about the mechanics underneath.
This is how bugs get into production. Not because someone’s incompetent, but because everyone—even experienced developers—can drift into autopilot. We write code the way we’ve always written it, assume it does what it did last time, and move on.
The solution isn’t to never make mistakes. It’s to slow down occasionally and question your assumptions. Ask yourself: “Do I actually know what this does?” Because chances are, there’s a rule you half-remember from a tutorial five years ago that’s about to bite you.
I spent three hours on a LeetCode problem. On paper, that’s embarrassing. But I’d rather spend three hours now learning it right than spend three days in production debugging data loss.
🧬 Related Insights
- Read more: Tekton’s CNCF Incubation Win Signals Kubernetes CI/CD Is Now Enterprise Standard
- Read more: Three Friends Built and Published a Party Game in One Year—Without Writing a Line of Code
Frequently Asked Questions
What’s the difference between JOIN and INNER JOIN in SQL?
Nothing. They’re identical. In MySQL, PostgreSQL, and most relational databases, JOIN is shorthand for INNER JOIN, which only returns rows with matches in both tables. If you want all rows from one side, be explicit and use LEFT JOIN or RIGHT JOIN.
Why does SQL alias order matter?
SQL reads syntax literally. You must write [Table Name] [Alias], not [Alias] [Table Name]. If you reverse it, the database interprets the first word as the actual table name. If that table doesn’t exist, your query crashes with “table doesn’t exist” error.
How do I know when to use LEFT JOIN vs INNER JOIN?
Ask yourself: “Do I want to keep rows from the left table even if they don’t match anything on the right?” If yes, use LEFT JOIN. If you only want matched pairs, use INNER JOIN. Read the requirements carefully—phrases like “report null if missing” almost always mean you need a LEFT JOIN.
