Difference between Self join & correlated query

Where did you learn these terms ? Do you know what they mean ? The answer, as always, is that it depends.

SELECT
   employee_number,
   name,
   (SELECT AVG(salary)
      FROM employees
      WHERE department = Bob.department) AS department_average
   FROM employees AS Bob;

The second select is a subquery.

A self join is where a table joins to itself. Both are expensive. Both have uses and are sometimes the right choice, depending on your situation. I almost never use either, I use CTEs, my article on those is here[^]. If you have a specific question, try posting the details of the specific SQL you're working on. If this is a general question, it has no real answer, the answer is to know the pitfalls and benefits of many approaches, and choosing the right approach for each task.