Advanced Database Management System - Tutorials and Notes: UNION Set Operator

Tuesday, 10 June 2014

UNION Set Operator


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.

Related Topics


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...

All time most popular contents