This content originally appeared on DEV Community and was authored by DEAN LEE
While solving the LeetCode #175 (Combine Two Tables) problem, I encountered a couple of unexpected errors.
What seemed like a simple query taught me a valuable lesson about SQL syntax and the fundamental behavior of JOINs. Here’s a breakdown of the "traps" I fell into and how I fixed them.
1. The Problem
I initially wrote the following MySQL query to fetch person details along with their addresses:
SELECT firstName, lastName, city, state
FROM P Person
JOIN Address A
ON P.personId = A.personId
However, I hit a Runtime Error: Table 'test.p' doesn't exist. Even after fixing the syntax, I realized the logic was slightly off for the problem's requirements.
2. Root Cause Analysis
Error 1: Incorrect Table Aliasing
In SQL, the correct order for aliasing is [Table Name] [Alias].
What I wrote: FROM P Person
What happened: The SQL engine interpreted P as the table name and Person as the alias. Since there is no table named P in the database, it threw the "Table doesn't exist" error.
Error 2: The "Default JOIN" Misconception
I used to think that a plain JOIN would behave like a LEFT JOIN by default. I was wrong. In standard SQL (MySQL, PostgreSQL, etc.), JOIN is shorthand for INNER JOIN.
Inner Join: Returns rows only when there is a match in both tables (the intersection).
The Issue: The problem asked to report null if the address is missing. An INNER JOIN would simply drop those people from the results entirely.
3. The Solution: Switching to LEFT JOIN
To ensure that every person in the Person table is reported (even those without an address), we must use a LEFT JOIN. This keeps all rows from the "left" table and fills in NULL for missing values from the "right" table.
SELECT
p.firstName,
p.lastName,
a.city,
a.state
FROM Person p
LEFT JOIN Address a
ON p.personId = a.personId;
4. Key Takeaways
Alias Order Matters: Always use Table p, not p Table.
Be Explicit with JOINs: Never assume the default. If you need to preserve data from one side, explicitly use LEFT JOIN or RIGHT JOIN.
Read the Requirements: If a problem mentions "report null if not present," it's a massive hint to use an Outer Join.
Final Thoughts
It’s easy to overlook the basics when you're focused on complex logic. This "simple" LeetCode problem was a great reminder that understanding the underlying mechanics of SQL is just as important as getting the right output.
Have you ever made a similar "default behavior" assumption in your code? Let me know in the comments!
This content originally appeared on DEV Community and was authored by DEAN LEE
DEAN LEE | Sciencx (2026-04-03T13:26:22+00:00) “Table ‘test.p’ doesn’t exist” — Understanding SQL Aliases and Default JOIN Behavior. Retrieved from https://www.scien.cx/2026/04/03/table-test-p-doesnt-exist-understanding-sql-aliases-and-default-join-behavior/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.