Question

What are specific situations in which joins and nested queries would be preferred over the other? Explain your reasoning.

Answer 1

Explanation:

Join and nested queries in Database:

The advancement in the field of software and various other fields led to the generations of large amounts of data.These data is stored using databases.

While performing operations on these databases and accessing the data from them, there are various methods.

One can think of placing the entire data in a single table.

This has its own problems like redundancies etc...

The tables are decomposed to small tables for efficient processing.

This is where the usage of joins and nested queries occur.

When we want to retrieve and access the data from multiple tables, the joins and nested queries can be used.

There are various types of joins that are used based on the type of data we want to access from databases.

A join or the nested queries can be basically subjected to following conditions.

While retrieving the data using joins or nested queries, the performing makes little difference.

In case of nested query,we get the needed data from each table and combine the tuples to show the required result.

In case of join, we get the entire table and make a new table with all these records from fetched table and display accordingly to type of join used.

The joins are universal and there are o performance optimizations.

There can be situations where the performance of the nested queries can be improved.

In mysql , a constant sub query gets evaluated every time it is being encountered.

This occurs because there is no cache to store the data once the sub query gets evaluated.

So it gets evaluated every time it is seen.

In case of joins, these return the data which has indexing to it.

It is easy to access the elements with indexes.

This improves the performance of the system as well.

It is seen that in most of the cases, the sub queries take much time to execute when compared with joins.

These sub queries and joins are used based on the situations and type of data we want to retrieve and access from database.