Subqueries: Correlated vs Uncorrelated

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, CData Virtuality can use optimized JOIN operations using just two source queries. In the first case, CData 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 CData 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.