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 most often 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 optimizer 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.

You might also want to review our Oracle SQL tutorials. Also see our Oracle training page for information about formal training courses both on-line and in person.

Comments for What are the advantages of using joins in Oracle?

Average Rating starstarstarstarstar

Click here to add your own comments

Jul 07, 2015
Rating
starstarstarstarstar
good post
by: Sagan

Here you like to discuss Advantages of using Oracle language here. I really need it because I'm looking a healthy post about Oracle and also benefits as well. Now finally this website i got which contain every thing which i want regarding oracle. I think you're expert in Oracle. any how, I also want to looking issertations help and also Happy to get ultimate source of learning about Oracle.

Jun 27, 2011
Rating
starstarstarstarstar
Informative
by: Anonymous

Thanks for the valuable information.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.