"Table 'test.p' doesn't exist" — Understanding SQL Aliases and Default JOIN Behavior

#database #sql #leetcode #beginners

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!