As you learnt previously, nested queries and joins are used for retrieving data from multiple tables. But is one of them more efficient than the other, especially in the case of large data sets? Well, it depends on the query processor. Query processors run optimising operations on your queries to ensure that the runtime is as low as possible. You will learn more about this in the upcoming video.
As you learnt in this video, executing a statement with the ‘join’ clause creates a join index, which is an internal indexing structure. This makes it more efficient than a nested query. However, a nested query would perform better than a join while querying data from a distributed database.
In a distributed database, tables are stored in different locations instead of a local system. In this case, a nested query would perform better than a join, as we can extract relevant information from different tables located in different computers. We can then merge the values in order to obtain the desired result. In the case of a join, we would need to create a large table from the existing tables, and filtering this large table would require comparatively more time.
With this, we have come to the end of this session. You learnt about various concepts in this session, so let’s summarise the topics and the syntax for writing optimised SQL queries in the final segment.
To learn more about the performance of joins in comparison with nested queries in different scenarios, you can refer to the link provided below.