Source: https://coderpad.io/interview-questions/postgresql-interview-questions/

The question says that there is an error when you run the query. However, this is not true.

This code runs perfectly fine.


SELECT e.id, e.first_name, e.last_name, e.salary, e.department_id
FROM employees e
JOIN (SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id) d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

Why? because the subquery does not need to be aliased.

Execution of his query should be

  1. Suquery runs first. All columns required are in the query, there are no ambiguous columns because it's all from one table
  2. The subquery is aliased as "d"
  3. The query will then JOIN the two tables together
  4. The query will then filter out any rows based on the WHERE clause