USING SET OPERATORS Database Management System

USING SET OPERATORS Database Management System

USING SET OPERATORS

The SET operators

The SET operators combine the results of two or more component queries into one result. Oueries

containing SET operators are called compound queries.

All SET operators have equal precedence. If a SQL Statement contains multiple SET operators, the Oracle server evaluates them from left (top) to right (bottom) if no parentheses explicitly specify another order. You should use parentheses to specify the order of evaluation explicitly in queries that use the INTERSECT operator with other SET operators.

 

The UNION Operator

The UNION operator returns all rows selected by either query. Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows.

Guidelines

  • The number of columns and the datatypes of the columns being selected must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.
  • UNION operates over all of the columns being selected.
  • NULL values are not ignored during duplicate checking.
  • The IN operator has a higher precedence than the UNION operator.
  • By default, the output is sorted in ascending order of the first column of the SELECT clause.

SELECT Col1, Col2

FROM TableA

UNION SELECT Co|1, Col2

FROM TableB;

Using the UNION ALL Operator

SELECT Co|1, Co|2, Col3

FROM TableA

UNION ALL

SELECT Co|1, Co|2, Co|3

FROM TableB

ORDER BY Co|.1;

The INTERSECT Operator

Use the INTERSECT operator to return all rows Common to multiple queries.

Guidelines

The number of Columns and the datatypes of the Columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.

Reversing the Order of the intersected tables does not alter the result.

INTERSECT does not ignore NULL values.

SELECT Coi1, Col2

FROM TableA                                                             

INTERSECT

SELECT Co|1, Co|2

FROM TableB

 

The MINUS Operator

Use the MINUS operator to return rows returned by the first query that are not present in the second query (the first SELECT statement MINUS the second SELECT statement).

Guidelines

The number of columns and the datatypes of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.

All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator to work

SELECT Co|1,Co|2

FROM TableA

MINUS

SELECT Co|1,Co|2

FROM TableB;