HR schema下旧式SQL连接无结果，JOIN查询有结果的原因咨询

Great question! The issue boils down to a critical mistake in your join condition in the first query. Let's break this down step by step:

The Problem in Your First Query #

Looking at your first (old-style implicit join) query:

select country_name,city, department_name 
from HR.COUNTRIES c, HR.Locations l, HR.DEpartments d 
where c.COUNTRY_ID = l.country_id 
and d.DEPARTMENT_ID=l.location_id;

The second condition d.DEPARTMENT_ID=l.location_id is incorrect.

• DEPARTMENT_ID is the unique identifier for departments in the HR.DEPARTMENTS table
• LOCATION_ID is the unique identifier for locations in the HR.LOCATIONS table

These two fields have no logical relationship and their values don't overlap. When you try to join them, there are zero rows where a department's ID matches a location's ID—so the query returns nothing.

Why the Second Query Works #

Your second (explicit JOIN) query uses the correct join condition:

select country_name,city,department_name 
from HR.COUNTRIES c 
join HR.LOCATIONS l on c.COUNTRY_ID =l.country_id 
join HR.DEPARTMENTS d on l.location_id=d.location_id;

Here, you're joining HR.LOCATIONS.location_id to HR.DEPARTMENTS.location_id—this is the proper relationship: departments are associated with locations via their shared LOCATION_ID field. This matches existing data in the HR schema, so you get valid results.

A Quick Note on Join Syntax #

While old-style implicit joins (using commas and WHERE clauses) are technically valid, explicit JOIN syntax is strongly recommended. It:

• Makes your join logic clearer and easier to read
• Separates join conditions from filter conditions, reducing the chance of mistakes like the one here
• Supports more explicit join types (like LEFT JOIN, RIGHT JOIN) without ambiguity

内容的提问来源于stack exchange，提问作者Sheikh Rahman