You can use the Netezza set operators to combine similar data sets from two or more SELECT statements. Here the similar data set literally mean, the data type of the result set should also match, otherwise you have to explicitly type cast data when using Netezza set operators.
Read:
Types of Netezza Set Operators
Netezza supports the three types of set operators:
- UNION [DISTINCT] and UNION ALL
- INTERSECT [DISTINCT]
- EXCEPT [DISTINCT] or MINUS [DISTINCT]
Netezza Set Operators Syntax:
Below is the general syntax for Netezza SET operators:
<SELECT-statement>
{UNION | INTERSECT | EXCEPT | MINUS} [ALL | DISTINCT]
<SELECT-statement>
{UNION | INTERSECT | EXCEPT | MINUS} [ALL | DISTINCT]
<SELECT-statement>]*
[ORDER BY …]
[LIMIT …];
Netezza UNION and UNION ALL
The UNION and UNION ALL operation combines the results of two similar subqueries into a single result that contains the rows that are returned by both SELECT statement. These operations are logical OR. This operation is different from JOIN, which combine the columns from two tables.
A UNION expression removes duplicate rows from the result; a UNION ALL expression does not remove duplicates.
Netezza UNION, UNION ALL Examples
Below is the example that demonstrates Netezza UNION and UNION ALL SET operator usage
TRAINING.ADMIN(ADMIN)=> select ID, Name from Patient_1 TRAINING.ADMIN(ADMIN)-> UNION TRAINING.ADMIN(ADMIN)-> select ID, Name from Patient_2 TRAINING.ADMIN(ADMIN)-> order by 1; ID | NAME ----+------ 1 | ABC 2 | BCD 3 | CDE 4 | DEF 5 | EFG 6 | FGH 7 | GHI (7 rows) TRAINING.ADMIN(ADMIN)=> select ID, Name from Patient_1 TRAINING.ADMIN(ADMIN)-> UNION ALL TRAINING.ADMIN(ADMIN)-> select ID, Name from Patient_2 TRAINING.ADMIN(ADMIN)-> order by 1; ID | NAME ----+------ 1 | ABC 1 | ABC 2 | BCD 2 | BCD 3 | CDE 4 | DEF 5 | EFG 6 | FGH 7 | GHI (9 rows)
Netezza INTERSECT
The INTERSECT operation combines the similar results of two subqueries into a single result that contains all the rows common to both SELECT statements.
Netezza INTERSECT Examples
Below is the example that demonstrates Netezza INTERSECT SET operator usage
TRAINING.ADMIN(ADMIN)=> select ID, Name from Patient_1 TRAINING.ADMIN(ADMIN)-> INTERSECT TRAINING.ADMIN(ADMIN)-> select ID, Name from Patient_2 TRAINING.ADMIN(ADMIN)-> order by 1; ID | NAME ----+------ 1 | ABC 2 | BCD (2 rows)
Netezza EXCEPT/MINUS
The EXCEPT/MINUS operation finds the difference between the two sub-queries and return the result contains the rows from the first SELECT statement. We can use either EXCEPT or MINUS in the statements.
Netezza EXCEPT/MINUS Examples
Below is the example that demonstrates Netezza EXCEPT and MINUS SET operator usage
TRAINING.ADMIN(ADMIN)=> select ID, Name from Patient_1 TRAINING.ADMIN(ADMIN)-> EXCEPT TRAINING.ADMIN(ADMIN)-> select ID, Name from Patient_2 TRAINING.ADMIN(ADMIN)-> order by 1; ID | NAME ----+------ 3 | CDE 4 | DEF 5 | EFG (3 rows) TRAINING.ADMIN(ADMIN)=> select ID, Name from Patient_1 TRAINING.ADMIN(ADMIN)-> MINUS TRAINING.ADMIN(ADMIN)-> select ID, Name from Patient_2 TRAINING.ADMIN(ADMIN)-> order by 1; ID | NAME ----+------ 3 | CDE 4 | DEF 5 | EFG (3 rows)
Netezza Set Operator Precedence Ordering
INTERSECT takes higher precedence than the other set operations. UNION and EXCEPT/MINUS have the same precedence order. You can override the preccedence ordering by using parenthesis.
Netezza Set Operator Limitations
- If the names of the both columns in SELECT statement match, SQL displays that column name in the result. If the corresponding column names differ, SQL uses the column name from the first query in the set statement. However, you can rename the column by using AS clause in the either SELECT query.
- You can specify GROUP BY and HAVING only in individual queries. You cannot use them on results set.
- You can specify an optional ORDER BY clause only in the final query in the set statement. The ordering will apply for the result set.
- You can specify an optional LIMIT clause after the ORDER BY. LIMIT will apply to only result set.