is this a subquery or just an inner join

Question

I am studying the subquery concept, below is one query which is extracted from wikipedia https://en.wikipedia.org/wiki/Correlated_subquery

SELECT employees.employee_number, employees.name
   FROM employees INNER JOIN
     (SELECT department, AVG(salary) AS department_average
       FROM employees
       GROUP BY department) AS temp ON employees.department = temp.department
   WHERE employees.salary > temp.department_average;

the sql is a rewritten version of an correlated subquery as below

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

And now my question : Is the sql from the rewritten version a subquery? I am so confused on it

  INNER JOIN
         (SELECT department, AVG(salary) AS department_average
           FROM employees
           GROUP BY department) AS temp ON employees.department = temp.department
       WHERE employees.salary > temp.department_average;

Answer 1

Welcome to Stackoverflow. This is certainly confusing, so I'd make it a little bit simpler by using two different tables and no table aliases.

I'd say if it's in the FROM clause, it's called a join:

SELECT employee_id, department_name
  FROM employees JOIN departments USING (department_id);

If it's in the WHERE clause, it's called a subquery:

SELECT employee_id
  FROM employees
 WHERE employee_id = (
         SELECT manager_id
           FROM departments
          WHERE employees.employee_id = departments.manager_id);

If it's in the SELECT clause, it's called a scalar subquery (thanks, @Matthew McPeak):

SELECT employee_id,
       (SELECT department_name
          FROM departments
         WHERE departments.department_id = employees.department_id)
  FROM employees;