**Oracle Database**

➤ EMP DEPT Tables Queries

➤ Set Operators in Oracle

➤ CREATE command

➤ RENAME command

➤ DROP command

➤ Default Value in Column

➤ ABS() in Oracle SQL

➤ CEIL() in Oracle SQL

➤ SYSDATE in Oracle

➤ Trunc Oracle date

➤ TO_CHAR() in Oracle

➤ GROUP BY Clause

➤ Having Clause

➤ ORDER BY Clause

➤ Constraints in Oracle

➤ Rename Constraint

➤ Disable Constraint

➤ Drop Constraint

➤ NOT NULL Constraint

➤ UNIQUE Constraint

➤ PRIMARY KEY Constraint

➤ FOREIGN KEY Constraint

➤ CHECK Constraint

➤ Unlock User in SQL Plus

➤ Find SID in Oracle database

➤ Check Database Version

➤ Check Database Size

➤ Error ORA 01031

**Set Operators in Oracle with Examples | **Set operators in the Oracle database are used to join the results of two or more SELECT statements from single or multiple tables.

The set operators in the Oracle database are also called **vertical Joins**. Oracle has the following set operators:-

**UNION****UNION ALL****INTERSECT****MINUS**

## Oracle Set Operators

**UNION Set Operator**:- UNION set operator returns the combined result in the sorted order (ascending by default), and it excludes the duplicate results. Only one result is displayed for the duplicate results. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the UNION Set for them will be {a,b,d,x}

**UNION ALL Set Operator**:- UNION ALL set operator is similar to UNION set operator but it includes the duplicate results in the final result. The UNION ALL set operator gives better performance as compared to the UNION set operator. Because resources are not used in filtering duplicates and sorting the result set. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the UNION ALL Set for them will be {a,b,x,a,a,b,c,c,d}

**INTERSECT Set Operator**:- INTERSECT set operator in Oracle returns the common values in sorted order (ascending by default). It produces an unduplicated result. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the INTERSECT Set for them will be {a,b}

**MINUS Set Operator**:- The MINUS set operator in Oracle returns the results that are in the result of the first query but not in the result of the second query. It produces non-duplicate results in ascending order by default. For example:- f(x) = {a,b,x} and g(x) = {a,a,b,c,c,d} then the INTERSECT Set for them will be {x}

The UNION and INTERSECT set operators are commutative, which means the order of queries is not important, for different orders we will get the same output.

We can combine multiple queries using the set operators, and the set operator can be part of the subqueries. All set operators have **equal precedence** and when SQL statements contain multiple set operators then evaluation starts from **left to right** or **top to bottom**. But the order may differ if we use parentheses explicitly.

## Example on Set Operators in Oracle Database

To practice Set operators we will use EMP and DEPT table of Oracle database given under SCOTT user. Display EMP table,

`SELECT empno, ename, job, deptno FROM emp;`

Output:-

```
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7566 JONES MANAGER 20
7654 MARTIN SALESMAN 30
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7788 SCOTT ANALYST 20
7839 KING PRESIDENT 10
7844 TURNER SALESMAN 30
7876 ADAMS CLERK 20
7900 JAMES CLERK 30
7902 FORD ANALYST 20
7934 MILLER CLERK 10
14 rows selected.
```

Display DEPT table,

`SELECT deptno, dname FROM dept;`

Output:-

```
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
```

From the EMP table, display the jobs where DEPTNO = 10,

`SELECT job FROM emp WHERE deptno = 10;`

Output:-

JOB

———

MANAGER

PRESIDENT

CLERK

From the EMP table, display the jobs where DEPTNO = 20,

`SELECT job FROM emp WHERE deptno = 20;`

Output:-

JOB`---------`

CLERK

MANAGER

ANALYST

CLERK

ANALYST

For better understanding purposes, write the above two results (jobs for the deptno=10, and 20) on the paper and then observe the result after using Set operators.

### 1. UNION Set Operator Example

For multiple select queries, the **UNION set** operator returns the combined result in sorting order and removes all duplicate values. The UNION set operator doesn’t ignore the NULL value.

```
SELECT job FROM emp WHERE deptno = 10
UNION
SELECT job FROM emp WHERE deptno = 20;
```

Output:-

JOB`---------`

ANALYST

CLERK

MANAGER

PRESIDENT

### 2. UNION ALL Set Operator Example

**UNION ALL** set operator gives combined results of all select queries, and it also includes the duplicate results in the final result.

```
SELECT job FROM emp WHERE deptno = 10
UNION ALL
SELECT job FROM emp WHERE deptno = 20;
```

Output:-

JOB`---------`

MANAGER

PRESIDENT

CLERK

CLERK

MANAGER

ANALYST

CLERK

ANALYST

8 rows selected.

### 3. INTERSECT Set Operator Example

The **INTERSECT **set operator in Oracle returns the common value and produces an unduplicated result.

```
SELECT job FROM emp WHERE deptno = 10
INTERSECT
SELECT job FROM emp WHERE deptno = 20;
```

Output:-

JOB`---------`

CLERK

MANAGER

### 4. MINUS Set Operator Example

The **MINUS** set operator in Oracle returns the results that are in the result of the first query but not in the result of the second query. It produces an unduplicated result.

```
SELECT job FROM emp WHERE deptno = 10
MINUS
SELECT job FROM emp WHERE deptno = 20;
```

Output:-

JOB`---------`

PRESIDENT

## More on the Return value of Set Operators

Whenever we are using set operators then the corresponding expressions or columns must match in number and must belong to the same data type group (such as numeric or character) else we get errors. Oracle database will not perform implicit conversion if corresponding columns in the component queries belong to the different data type groups.

```
SELECT job FROM emp WHERE deptno = 10
UNION
SELECT empno FROM emp WHERE deptno = 10;
```

Output:-

SELECT job FROM emp WHERE deptno = 10

*

ERROR at line 1:

ORA-01790: expression must have same datatype

as corresponding expression

Here, the JOB column belongs to the varchar2 data type but the EMPNO column belongs to the number data type. Therefore, both are not compatible and give errors.

If component queries select character data, then the data type of the return values is determined as follows:-

- If both queries select the values of the CHAR data type of equal length then the return value will have a CHAR data type of the same length. But if they have different lengths of CHAR datatype then the return value is VARCHAR2 with the larger CHAR value.
- If anyone query has select values of VARCHAR2 datatype then the return value has VARCHAR2 datatype.

When component queries select numeric data, then the data type of the return values is determined by numeric precedence:

- If at least one query has select values of BINARY_DOUBLE type then the datatype of the return value is BINARY_DOUBLE.
- If no query has select values of BINARY_DOUBLE type, but any query selects values of type BINARY_FLOAT then the returned values have datatype BINARY_FLOAT.

## Matching the SELECT statement

In Oracle, we can also retrieve data from multiple queries by using set operators, when corresponding expressions do not belong to the same data type also. In this case, we must use appropriate type conversion functions.

To match the column list explicitly, NULL columns are inserted at the missing positions so as to match the count and data type of selected columns in each SELECT statement. For number columns, zero can also be substituted to match the type of columns selected in the query. Example:-

```
SELECT deptno FROM emp
UNION
SELECT dname FROM dept;
```

Output:-

SELECT deptno FROM emp

*

ERROR at line 1:

ORA-01790: expression must have same datatype

as corresponding expression

The deptno is of number data type but dname is of varchar2 data type. Solution:-

```
SELECT deptno, TO_CHAR(NULL) "deptnames" FROM EMP
UNION
SELECT TO_NUMBER(NULL), dname FROM DEPT;
```

Output:-

```
DEPTNO deptnames
---------- --------------
10
20
30
ACCOUNTING
OPERATIONS
RESEARCH
SALES
7 rows selected.
```

It can be also written as,

```
SELECT deptno, TO_CHAR('---') "deptnames" FROM EMP
UNION
SELECT TO_NUMBER('0'), dname FROM DEPT;
```

Output:-

```
DEPTNO deptnames
---------- --------------
0 ACCOUNTING
0 OPERATIONS
0 RESEARCH
0 SALES
10 ---
20 ---
30 ---
7 rows selected.
```

By default set operators always return the first select query column name or alias name as the column heading. Examples:-

```
SELECT dname FROM DEPT
UNION
SELECT ename FROM EMP;
```

Output:-

```
DNAME
--------------
ACCOUNTING
ADAMS
……….
………..
```

## ORDER BY Clause Used with SET Operators

We can also use the ORDER BY clause with set operators, **but it should be used only with the last SELECT statement.** Example:-

```
SELECT empno, ename, job FROM emp
WHERE deptno = 10
UNION
SELECT empno, ename, job FROM emp
WHERE deptno = 20
ORDER BY empno;
```

Output:-

```
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7566 JONES MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7876 ADAMS CLERK
7902 FORD ANALYST
7934 MILLER CLERK
8 rows selected.
```

## Limitations of Set Operators in Oracle Database

The set operators have the following limitations,

- Set operators can’t be applied on columns having type BLOB, CLOB, BFILE, VARRAY, or nested table.
- Only the UNION ALL set operator is valid on LONG columns.
- Set operators can’t be used in SELECT statements containing TABLE collection expressions.
- If the select list preceding the set operator contains an expression, then we must provide a column alias for the expression in order to refer to it in the ORDER BY clause.
- We can’t also specify the FOR UPDATE clause with the set operators.
- We can’t specify the ORDER BY clause in the subquery of these operators.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!