is this a subquery or just an inner join is this a subquery or just an inner join oracle oracle

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.