What is the difference between nested queries & set theory in SQL?

by Sachin
(Aus)

If we can write SQL queries in nested way what is the need to go complex way of relational calculus. One more thing - which one is faster?


It seems that you may not have quite understood one of the fundamental concepts of relational databases such as Oracle and that is that they are based on relational calculus (see our Oracle tutorial for an introduction to this). This means that all SQL queries against the database have to use relational algebra. A nested query is just another query. By definition a nested query must be able to stand on its own (with minor adjustments for correlated sub queries) so whether a query is nested or not makes no difference to the principles underlying it (see this question and answer What is the difference between a normal query and a subquery in sql?).

A nested query itself may also include nested queries of arbitrary complexity and so on ad infinitum and may use any of the SQL constructs allowed for any other query.

You may be thinking of SQL queries involving joins. In which case it is correct that they can often be rewritten as a nested query but it doesn't make any difference to the underlying principles - the query is still based on relational algebra (see this tutorial on SQL sub queries. As to whether using joins or sub queries is better for performance, the answer is it depends. Sometimes using a join in Oracle may give better performance than using a sub query and sometimes the reverse and the Oracle optimizer will often convert from one form to the other to optimize the query automatically. The only way to know which is better is to test both forms on your database.

For more help with SQL see our Oracle SQL tutorial.

If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Click here to post comments

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