To make an outer query column available to a nested (sub) query, you can use correlated subqueries or common table expressions (CTEs). Here are two approaches:
sql Copy Edit SELECT e.id, e.name, (SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id) AS department_name FROM employees e; 2. Using a Common Table Expression (CTE) A CTE makes the outer query’s results accessible in a structured way for further querying.
sql Copy Edit WITH EmployeeData AS ( SELECT e.id, e.name, e.department_id, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id ) SELECT id, name, department_name FROM EmployeeData;