What are the advantages of using joins in Oracle?

by samba
(hyderabad)

I'd like to know what are the advantages or disadvantages of using joins over sub queries in Oracle

To answer this question let's first define a subquery. A subquery is query within a query and is used to answer multi-part questions such as "Which department does the highest paid employee work for?".

Assuming you have a standard normalised Oracle database, the employee table would contain details of employees (id, name, salary, job title, manager hire date, department id) and the department table would contain details of departments (id, name).

Therefore to determine which department the highest paid employee works for we need to answer a 3-part question - first determine what is the maximum salary, second find out the department id(s) of the person/people with that salary, finally we can get the department name(s).

In SQL we would write something like this:

SELECT department_name
FROM departments
WHERE department_id IN (
   SELECT department_id
   FROM employees
   WHERE salary =
      (SELECT max(salary) FROM employees)
)


In this query we use a non-correlated sub-query as the subquery does not reference any columns in the parent query. See the
section on sub queries in the Oracle SQL Language Reference manual for the distinction between correlated and non-correlated subqueries.

To change this to a join query we would write something like this:

SELECT department_name
FROM departments d, employees e, (SELECT max(salary) salary FROM employees) e2
WHERE d.department_id = e.department_id
AND e.salary=e2.salary
)

In this query we've also used a sub-query in the FROM clause of the SQL statement, so we have both joins and sub-queries.


Now we are in a better position to discuss whether a join is better than a subquery or vice versa.

In a relatively simple query like the one above there would probably be no difference in performance terms either way when running this query against our Oracle database. Even with more complex queries there might be no performance difference as the Oracle optimiser can often un-nest queries to determine the overall optimal path.

There are situations though when we can't use a sub query. For example if we change our original question to ask not just for the department but also the employee name and his/her salary then we have to use a join. In other words, we can't use a subquery if we need data from the other table(s) involved in the query.

In Oracle SQL terms we need to say:
SELECT employee_name, e.salary, department_name
FROM departments d, employees e, (SELECT max(salary) salary FROM employees) e2
WHERE d.department_id = e.department_id
AND e.salary=e2.salary
)

In conclusion therefore, there may sometimes be a slight performance advantage to using a join query rather than a subquery. However if you need data from more than one table you have to use a join query. On the other hand, using sub-queries may make the code more elegant and easier to read and it may seem easier to answer the question that way rtaher than using a join.