List every combination of dept_name, employee_name, and city such that the employee belongs to the department and the same city in which the department is located.

Why Join + Same-Location Filter: Business rule: employee must be in dept AND colocated. The join condition e.city = d.city enforces referential locality—critical for org hierarchy and location-based access control.

Query Design: JOIN on dept_id plus e.city = d.city. Alternative: JOIN on (dept_id, city) as composite key if indexed—can be faster. Use INNER JOIN when you want only matching pairs; LEFT JOIN if you need depts with no local employees (would show NULL).

Scalability: Index (dept_id, city) on both tables. For large orgs (100K+ employees): consider materialized view for 'employees by location' refreshed nightly.

SELECT d.dept_name, e.employee_name, e.city
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id AND e.city = d.city;