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.