Subqueries: Correlated vs Uncorrelated

You are looking at an older version of the documentation. The latest version is found here.

Correlated and uncorrelated are some common types of subqueries. In many cases - although not always - the same results can be achieved with a correlated and uncorrelated subquery. However, the uncorrelated subquery will typically perform better than the correlated one. Here are some examples of equivalent correlated and uncorrelated subqueries:

1. Correlated subquery:

SELECT * FROM source1.table1 a WHERE a.id IN (SELECT id FROM source2.table2 WHERE date>NOW() AND a.id=id) 

2. Uncorrelated subquery:

SELECT * FROM source1.table1 a JOIN (SELECT id FROM source2.table2 WHERE date>NOW()) b ON a.id=b.id

The queries above will provide equivalent results. However, the uncorrelated one will be much more performant since, in the second case, Data Virtuality can use optimized JOIN operations using just two source queries. In the first case, Data Virtuality is forced to perform the subquery as many times as many rows as there are in the main table. Although in many cases, we can somewhat optimize the performance by using PreparedStatement, this is still slower compared to the Uncorrelated subquery. 

The difference in performance between correlated and uncorrelated subqueries is not something specific for Data Virtuality servers. Instead, this is something common to many RDBMS systems. Please see the links for the relevant discussion for MS SQL Server and SQL in general.