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

Source: Dev.to

Problem Overview

While solving LeetCode #175 (Combine Two Tables), I ran into a couple of unexpected errors. The issue turned out to be a mix of SQL alias syntax and a misunderstanding of the default behavior of JOIN.

Initial Query and Error

SELECT firstName, lastName, city, state
FROM P Person 
JOIN Address A 
ON P.personId = A.personId;

Running this query produced the runtime error:

Table 'test.p' doesn't exist

Alias Syntax

In SQL, the correct order for aliasing is:

[Table Name] [Alias]

• What I wrote: FROM P Person
• What happened: The engine interpreted P as the table name and Person as the alias. Since there is no table named P, it threw the error.

Fix: Use the actual table name followed by the alias, e.g., FROM Person p.

JOIN Behavior

I mistakenly assumed that a plain JOIN behaves like a LEFT JOIN. 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 problem required reporting NULL when an address is missing, so an INNER JOIN would drop those people from the results entirely.

Correct Query with LEFT JOIN

SELECT 
    p.firstName, 
    p.lastName, 
    a.city, 
    a.state
FROM Person p
LEFT JOIN Address a 
    ON p.personId = a.personId;

• LEFT JOIN keeps all rows from the left table (Person) and fills in NULL for missing values from the right table (Address).

Key Takeaways

• Alias Order Matters: Always write [Table] [Alias], not the other way around.
• Be Explicit with JOINs: Never assume the default. Use LEFT JOIN or RIGHT JOIN when you need to preserve rows from one side.
• Read the Requirements: Phrases like “report null if not present” are strong hints to use an outer join.

Understanding these fundamentals is just as important as crafting complex logic. Have you ever made a similar “default behavior” assumption in your code? Let me know in the comments!