Developer Mapping Guide

10.5
- 10.5.6
- 10.5.4
- 10.4.0

Back Next

Correlated Subqueries

A correlated subquery is a subquery that uses values from the outer query in its WHERE clause. The Data Integration Service flattens the correlated subqueries before it runs the query.

The following table shows the results of a correlated subquery that the Data Integration Service flattened:

Type	Query
Non-flattened	SELECT huge.* FROM huge WHERE c1 IN (SELECT c1 FROM tiny)
Flattened	SELECT huge.* FROM huge, tiny WHERE huge.c1 = tiny.c1

The Data Integration Service can flatten a correlated subquery when it meets the following requirements:

The type is IN or a quantified comparison.

It is not within an OR operator or part of a SELECT list.

It does not contain the LIMIT keyword.

It does not contain a GROUP BY clause, aggregates in a SELECT list, or an EXIST or NOT IN logical operator.

It generates unique results. One column in the corelated subquery is a primary key. For example, if r_regionkey column is a primary key for the vs.nation virtual table, you can issue the following query:

SELECT * FROM vs.nation WHERE n_regionkey IN (SELECT b.r_regionkey FROM vs.region b WHERE b.r_regionkey = n_regionkey).

If it contains a FROM list, each table in the FROM list is a virtual table in the SQL data service.

Rename Saved Search

Table of Contents

Developer Mapping Guide

Developer Mapping Guide

Correlated Subqueries

Correlated Subqueries