Question

Using SET operations, display a list of customers that are interested in a car (prospect table) of the same make and model which they already own.

Answer 1

The set operations are UNION, UNION ALL, INTERSECT and MINUS.

Let us use each one of them for the above question.

UNION operator- The UNION set operator returns the combined results of the two SELECT statements and returns the duplicates from result.

query:

SELECT custname, carmake, carmodel

FROM prospect

UNION

SELECT custname, carmake, carmodel

FROM prospect;

UNION ALL operator- The UNION ALL set operator is similar to UNION operator but it retains the duplicates in the final result

query:

SELECT DISTINCT custname, carmake, carmodel

FROM prospect

UNION ALL

SELECT DISTINCT custname, carmake, carmodel

FROM prospect;

INTERSECT operator - The INTERSECT lists only records that are common to both the SELECT queries.

query:

SELECT custname, carmake, carmodel

FROM prospect

INTERSECT

SELECT custname, carmake, carmodel

FROM car;

MINUS operator - The MINUS set operator removes the second query's results from the output if they are also found in the first query's results.

SELECT custname, carmake, carmodel

FROM prospect

MINUS

SELECT custname, carmake, carmodel

FROM car;