Union, Union all, Intersect and Minus

Sometimes you need to combine information of a similar type from more than one table. The set operators UNION, UNION ALL INTERSECT AND MINUS are used. to achieve this.

Consider these two queries and their results:
SQL > SELECT PART FROM ODR_LIST

PART

PLUG
PUMP.
TAILPIPE
PUMP

SQL > SELECT PART FROM ORDER_LIST ,

PART

SRANKSHAFT

TAILPIPE

TAILPIPE,

The following examples combine the two query results with each of the set operators.

<b> Union</b>

This statement combines the results with the UNION operator, which eliminates duplicated selected rows:

SQL > SELECT PART FROM ORDER LIST UNION

2 SELECT PART FROM ORDER LIST2

PART

PLUG

PUMP

TAILPIPE

CRANKSHAFT
<b>Union All</b>

This statement combines the results with the UNION ALL operators which does not eliminate duplicate selected rows:

SQL > SELECT PART FROM ORDER_LIST UNION ALL

2 SELECT PART FROM ORDER LIST2

PART

PLUG
PUMP
TAILPIPE
PUMP
CHANKSHAFT
TAILPIPE
TAILPIPE

Note that the UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows.
<b> Intersect</b>

This statement combines the result with INTERSECT operator which returns only those returned by both queries:

SQL > SELECT PART FROM ORDER LIST INTERSECT

2 SELECT PART FROM ORDER LIST2

PART

TAILPIPE
<b>Minus</b>

This statement combines the result with the MINUS operator which returns only those rows returned by the first query but not in the second.
SQL > SELECT PART FROM ORDER LIST MINUS

2 SELECT PART FROM ORDER LIST2

PART

PLUG
PUMP

The UNION, UNION ALL, INTERSECT and MINUS operators put certain restrictions on the queries they operate on. The queries must select the same number of columns, and the corresponding columns must be of the same type.

Domain Name Search