Correlated Vs Non-Correlated sub queries.

Difference between Correlated and Non-correlated sub-queries.

Which is an efficient one? Discussions.

1. What is a non-correlated sub-query?

Non-correlated sub-query is a sub-query where the inner query does not refer to any of the fields or columns specified in the outer query. Consider the following

SELECT * FROM EMPLOYEE EMP

WHERE EMP.CODE = (SELECT CODE FROM LOCATION WHERE NAME= ‘Chennai’ )

In the above query, the inner query is executed only once and the value is substituted for every row from the outer query. For example, if the EMP table contains 50 rows and the LOCATION table contains 10 rows, the read operations is done 60 times.

2. What is correlated sub-query?

The query with sub-query is a correlated sub-query when the inner query refers to any of the fields from the tables specified in the outer query.

Assume the following query:

SELECT * FROM EMPLOYEE EMP

WHERE EMP.CODE = (SELECT LOC.CODE FROM LOCATION LOC WHERE LOC.NAME= EMP.LOCATION )

This type of query is called as corrleated sub-query. Because the inner query refers to the field LOCATION from the outer query which degrades the performance of the application. This happens particularly when the number of rows are more in both tables.

In the above query, the inner query is executed for every row available in the table mentioned in the outer query. Assume that the EMP table contains 50 rows and the LOCATION table contains 10 rows, the read operation is done 50 + (50*10) which is 550 times.

3. How to decide, which query to use?

The answer is, ‘It depends’. But we should observe carefully which type of query needs to be used in our application. We need to use correlated sub-queries only when there is no other way to achieve that result. However it is essential for us to know the nature of the execution of SQL queries as a developer, so that we deliver the code as efficient as possible.

There are lot of other topics to come this week. Keep looking for it. Till then, Bye. Thanks for reading.

Regards,

Swathika.

swathikalakshmi@gmail.com