3:53 AM
Deep Dey
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 . 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.
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 . 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.
0 comments:
Post a Comment