Teradata Learning - Set Operators (02/24/2013)

UNION operator:

UNION operator allows results from multiple queries to be combined into a single result set.

Following are rules for using UNION operator:

1. All the selects combined using UNION should have same number of columns/expressions.

2. Corresponding columns/expressions must have compatible data types.

3. 1st select would have any FORMAT or TITLE clauses if used.

4. The last select statement contains the ORDER BY clause for entire results. For order by we should use numeric designators.

Example:

SELECT

first_name,

Last_name (TITLE 'lastnm')

From employee_table

UNION

Select

Manager_name,

Manager_Last_name

From manager_table

ORDER BY 1;

In the above example :

• All the selects should have 2 columns only.
• Corresponding columns like first_name and Manager_name should be data type compatible.
• TITLE was applied only to the column in 1st select.
• ORDER BY clause uses the numeric designator 1. We cannot use column name because we wouldn’t know which  one to use - first_name or Manager_name

By default UNION removes duplicates .ie if two rows in the UNION result set are exactly identical then it will automatically remove one row. To allow duplicates we need to use UNION ALL.

INTERSECT:

Intersect returns rows that are common within multiple result sets.

Select query 1

INTERSECT

Select query 2

Above will return rows that are common with in the result sets of both the queries.

Following are rules for using INTERSECT operator:

1. All the selects combined using INTERSECT should have same number of columns/expressions.

2. Corresponding columns/expressions must have compatible data types.

3. 1st select would have any FORMAT or TITLE clauses if used.

4. The last select statement contains the ORDER BY clause for entire results. For order by we should use numeric designators.

EXCEPT(MINUS):

The EXCEPT operator subtracts the contents of one set from the contents of another.

The EXCEPT operator is ANSI intermediate compliant. It will be flagged as non-entry level by the SQL flagger:

*** SQL warning 5811  MINUS of queries is not in entry level ANSI.

This flagged message refers to "MINUS" because the Teradata-compatible synonym for EXCEPT is MINUS. Use the EXCEPT operator rather than MINUS because although both provide the same functionality, EXCEPT is ANSI compliant.

Following are rules for using EXCEPT operator:

1. All the selects combined using EXCEPT should have same number of columns/expressions.

2. Corresponding columns/expressions must have compatible data types.

3. 1st select would have any FORMAT or TITLE clauses if used.

4. The last select statement contains the ORDER BY clause for entire results. For order by we should use numeric designators.

• SET operator additional Rules:

1. Each SELECT must have FROM table/view name.

Following query errors out:

select date  --> No FROM table

UNION

SELECT SALE_DATE from SALES_TABLE;

SELECT Failed. 3888:  A SELECT for a UNION,INTERSECT or MINUS must reference a table. 

2. By default SET operators will remove duplicates. If we don’t want duplicates to be removed we should use ALL (ex: UNION ALL). Note that ALL applies to operators including INTERSECT,MINUS,EXCEPT

3. GROUP BY clause if used in any of the selects applies only to that select and not to result set as whole

Select employee_name,salary from employee

UNION ALL

Select dept_name,sum(salary) from employee group by 1;

In the above example group by 1 applies only to the 2nd select query.

4. ORDER BY if used on the last query applies to entire result set.

5. SET operators cannot be used with WITH and WITH BY feature.

6. Following is the order of precedence if multiple set operators are used in a query:

INTERSECT

UNION

EXCEPT from LEFT TO RIGHT