Collective Operators

Operators are used for collective action on the results of two or more SELECT queries. It is important that all questions on which we will use the collective operators pay the samy types of lines, such as: (number, string, string, date).

UNION Operator

Collective UNION operator is used to create a sum of two sets of rows (SELECT query results). To get all mutually different rows returned by two SELECT commands, type:

SELECT JOB
FROM EMP
WHERE DEPTNO = 30
UNION
SELECT JOB
FROM EMP
WHERE DEPTNO = 10

Operation UNION (like other collective operators) will automatically eliminate the duplicates. To get through all the rows from both tables, use UNION ALL operator.

INTERSECT operator

Collective INTERSECT operator (intersection of sets) to select the rows common to both the results of SELECT queries.

SELECT JOB
FROM EMP
WHERE DEPTNO = 30
INTERSECT
SELECT JOB
FROM EMP
WHERE DEPTNO = 20

MINUS Operator

Collective operator MINUS (set difference) to select the rows returned by the first order, is not returned by the second order.

SELECT JOB
FROM EMP
WHERE DEPTNO = 30
MINUS
SELECT JOB
FROM EMP
WHERE DEPTNO = 20

Request may consist of more than two SELECT queries combined collective operators. In such forfeiture, priority is given INTERSECT operator, then operators UNION and MINUS (equal priority). In doubtful cases is best to use parentheses.

The ORDER BY clause in queries with the operators of collective

The questions from the collective operators can use the ORDER BY clause. It always refers to the result of the query and can be used only once. It is always placed at the end of the query. Since the selection list SELECT name may be different in the ORDER BY clause specifies the column name is not only its position.

SELECT empno, ename, SAL
FROM EMP
UNION
SELECT ID, NAME, Salary
FROM EMP_HISTORY
ORDER BY 2;

The results of this query will be sorted according to the second column, or by the names of the employees.