FreeSQL

Hi Team,

I would like to report an observation regarding query behavior in the SQL practice environment, along with the root cause identified after detailed debugging.

1. Scenario Overview

I was working with the HR.EMPLOYEES table and performing a self-join to compare employee salaries with their respective managers.

2. Base Join Validation (Working as Expected)

SELECT COUNT(*)
FROM hr.employees e
JOIN hr.employees m
ON e.manager_id = m.employee_id;

Result: 106 rows

This confirms:

• Valid relationship between EMPLOYEE_ID and MANAGER_ID
• Join condition is correct

3. Data Validation

SELECT COUNT(*)
FROM (
    SELECT e.salary emp_salary, m.salary mgr_salary
    FROM hr.employees e
    JOIN hr.employees m
    ON e.manager_id = m.employee_id
)
WHERE emp_salary > mgr_salary;

Result: 2 rows

This confirms:

• Matching records do exist
• Salary comparison logic is valid

4. Observed Issue

The following query:

SELECT 
    e.first_name, e.salary,
    m.first_name, m.salary
FROM hr.employees e
JOIN hr.employees m
ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Expected: 2 rows
Actual: “No items to display”

5. Root Cause Identified

After further analysis, the issue appears to be caused by duplicate column names in the result set:

• first_name appears twice
• salary appears twice

While Oracle Database allows duplicate column names in query output, it seems that the current environment has difficulty rendering such results properly.

6. Verified Fix (Using Column Aliases)

SELECT 
    e.first_name AS emp_name,
    e.salary AS emp_salary,
    m.first_name AS mgr_name,
    m.salary AS mgr_salary
FROM hr.employees e
JOIN hr.employees m
ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Result: Correct 2 rows returned

7. Conclusion

• SQL logic and data are correct
• Join condition is valid
• The issue arises due to non-unique column names in the result set
• Using column aliases resolves the problem

8. Suggestion

If this is expected behavior in this environment, it would be helpful to:

• Document this limitation clearly
• Or improve handling of duplicate column names in result rendering

This behavior can otherwise be misleading during learning and debugging.

Please let me know if any additional details are required.

Thanks & Regards,
Akanksh