Netezza Subqueries and Type of Subqueries with an Examples

  • Post author:
  • Post last modified:August 27, 2019
  • Post category:Netezza
  • Reading time:3 mins read

A subquery in Netezza is a select expression that is enclosed in parentheses as a nested query block in a query statement. The Subquery may return zero to one or more values to its upper select statements. In this article, we will check Netezza subqueries and and type of subqueries available with an examples.

Netezza Subqueries

Netezza Subqueries and Type of Subqueries

As mentioned earlier, Netezza subquery is a select expression enclosed in parenthesis as a nested query block.

You can use these nested query blocks in any of the following SQL statements:

  • SELECT
  • INSERT
  • DELETE
  • UPDATE
  • CREATE TABLE AS
  • INSERT INTO
  • SELECT INTO

The parent query that contains the subquery is called a super query or outer query. Subqueries are usually used to derive the values will be used in the parent or outer subquery.

Types of Netezza Subqueries

There are three categories of subqueries available in Netezza.

Row Subquery

These types of subqueries returns zero or one row and with multiple columns. You can use row subquery in SELECT or in a condition expression such as argument of a conditional expression.

Below is the sample example:

TRAINING.ADMIN(ADMIN)=> select *,(select '1') as dummy from PATIENT_1; 
 ID | NAME | DUMMY 
----+------+------- 
 5 | EFG | 1 
 1 | ABd | 1 
 3 | CDr | 1 
 7 | BBB | 1 
 4 | DEF | 1 
 2 | BCa | 1 
 6 | AAA | 1 
(7 rows)

Table Subquery

Returns multiple rows and multiple columns. These type of subqueries can exist in a FROM clause or as an argument of an EXISTS, IN, ANY, or ALL clauses.

Below is the sample examples of table subqueries

SELECT StoreId FROM Stores
 WHERE TotalSale > 0.01*
 (SELECT SUM(TotalSales) FROM Stores);

Singleton Subquery

Returns exactly one value in the format of one row (or zero rows), one column table.

For all these above subqueries, the system evaluates them once and calculates and stores their select expression. When you run the super query, the system substitutes the computed values in place of the subquery.

Correlated Subquery

There is other type of subquery, correlated subquery, and it is different from above types. You can read about in my other post: Netezza Correlated Subquery and its Restrictions

Read:

Tags: