## TOPICS (Click to Navigate)

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

## SQL Set Operator UNION and UNION ALL / Supported Set Operators in various DBMSs / UNION and UNION ALL Set Operators with Examples

Let us use the following tables for explaining the concept of the UNION set operator;
 Regno Name Phone 10BS0123 Madhavan 9965235412 10BC0234 Jerry 8569326541 11BM0023 Malar 9658236542 11BC0003 Kumar 9653268954
Table 1 - UG_Student
 Regno Name Phone 10MS0434 Noel 9856452314 10MS0012 Kumar 9653268954 11MC0070 Ramkumar 9965235412
Table 2 - PG_Student
UNION is used to join two or more tables vertically, i.e, they combine records (tuples) of two or more tables. For example, consider the query given below;

(SELECT Name, Phone FROM UG_Student) UNION (SELECT Name, Phone FROM PG_Student);
----- Query 1

The result of this query will be;
 Name Phone Madhavan 9965235412 Jerry 8569326541 Malar 9658236542 Kumar 9653268954 Noel 9856452314 Ramkumar 9965235412
Table 3 - Result of  "(SELECT Name, Phone FROM UG_Student) UNION (SELECT Name, Phone FROM PG_Student);"

The UNION operator can be used in two ways;
1. UNION [Distinct] - here Distinct keyword is not required.
2. UNION ALL
The first one eliminates duplicates automatically. That is, UNION eliminates the duplicate rows, if any present. For example, observe the result of Query 1 shown in Table 3. We have 4 records in Table 1 and 3 records in Table 2. Then, table 3 should have 7 records. The record (‘Kumar’, 9653268954), presents in both tables Table 1 and 2. Hence, UNION eliminates one of the records.
The later one, UNION ALL includes the duplicates in its result. As an example, see the Query 2 and the result given in Table 4 given below;
(SELECT Name, Phone FROM UG_Student) UNION ALL (SELECT Name, Phone FROM PG_Student);
----- Query 2
 Name Phone Madhavan 9965235412 Jerry 8569326541 Malar 9658236542 Kumar 9653268954 Noel 9856452314 Ramkumar 9965235412 Kumar 9653268954
Table 4 - Result of  "(SELECT Name, Phone FROM UG_Student) UNION ALL (SELECT Name, Phone FROM PG_Student);"

See in this table that (‘Kumar’, 9653268954) is duplicated. That is, the record occurs two times.

#### Well. What is the result of the following query?

(SELECT Name FROM UG_Student) UNION (SELECT Name, Phone FROM PG_Student);

The result of the above query will be,

ERROR at line 1:
ORA-01789: query block has incorrect number of result columns

Yes. It is an error. The reason is, the SELECT clauses of Expression 1 and Expression 2 do not have same number of attributes. See the required basic conditions here.

## Support for SQL Set Operator UNION in Various DBMSs:

#### UNION operator in MySQL

UNION operator is supported in MySQL. It can be used the same way as we stated above.

#### UNION operator in Oracle

Oracle supports UNION operator as discussed above. The above examples can be treated as examples for Oracle UNION operator.

#### UNION operator in DB2

DB2 supports UNION operator. Above examples can be treated as examples for DB2 too.

## Featured Content

### Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...