Netezza Correlated Subquery and its Restrictions

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:3 mins read

Netezza Correlated subquery is a query within a query that refer the columns from the parent or outer query. Netezza does support the regular and correlated subqueries. In most cases, the Netezza correlated subqueries are used to improve the SQL query performance.

netezza correlated subquery

Netezza Correlated Subquery Example

For example, consider query, “check if department is already exists in the patient table before selecting it from patient department table”. We can correlate both tables and write sql query. You can write the SQL as below:

SELECT T1.DEPT_CD
 , DATA_SRC_CD
 , T1.DEPT_NM
FROM PAT_DEPT T1
WHERE T1. DATA_SRC_CD = 1
AND EXISTS (SELECT 1 FROM
 PAT AB1
WHERE AB1.DEPT_CD = T1.DEPT_CD
);

This is the Netezza correlated subquery, because inner subquery in WHERE clause refers the DEPT_CD from the outer or parent query.

Restriction on Netezza Correlated Subquery

If you choose to use the correlated subqueries in Netezza, you should keep in mind following restrictions.

  • You can use correlated subqueries in WHERE clause
  • You can use the correlated subqueries in inner join conditions and only with equal condition operators
  • Use of correlated subqueries in SET operations are not supported
  • You cannot correlate the queries in aggregations with GROUP By and HAVING clauses
  • CASE/WHEN expressions doesn’t support correlated subqueries in
  • You cannot use correlated subqueries in IN conditions
  • Netezza doesn’t support the inline correlated queries, that is, query written in an SELECT clause of parent or outer query.

For example:

Look at the following example that is trying to get the department of each employee.

SELECT emp.emp_name,
 (SELECT dept_name
 FROM departments dept
 WHERE dept.dept_id = emp.dept_id)
FROM employee emp;

These types of correlated subqueries are supported in database like Oracle. However, Netezza has restrictions on using subqueries in SELECT lists. You can write the same query as follows:

SELECT emp.emp_name,
 dept.dept_name
FROM employees emp
 LEFT JOIN department dept
 ON (emp.dept_id = dept.dept_id);

The above query will run in Netezza.

Related Reading:

Tags: , ,