is this a subquery or just an inner join
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;
Not exactly. The equivalent would be a left join
. The correlated version keeps all rows in the employees
table, even when there is no match. The inner join
requires that there be a match.
In general, the execution plans are not going to be exactly the same, because the SQL engine does not know before-hand if all rows match.
With the additional filtering condition, the two versions are equivalent. Note that the filter for the correlated version requires a subquery or CTE because the where
clause does not recognize column aliases.