SQL - SPLessons

SQL Union

Chapter 39

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Union

SQL Union

shape Description

SQL Union operator is utilized to combine more than one result set obtained from SELECT statement. It removes the duplicate row between various SELECT statement. SQL UNION ALL administrator is utilized to join all the result set without eliminating duplicates. There must be same number of sections in both the select articulation and in the both select proclamation must have segments of comparative datatype.

shape Conceptual
figure


shape Syntax

The syntax for UNION operator is as follows:
SELECT column1, column2…..columnN FROM <table_name>,….. WHERE <search_condition> UNION SELECT column1, column2…..columnN FROM <table_name>,….. WHERE <search_condition>;

Table name => The accurate table in the database.

Column name =>The condition that can be performed in a column by using union operator.

shape Examples

The below example describes the UNION operator.

sql> Select * from customer;
+-------------+---------------+--------------+---------------+
| customer_id | customer_name | city         |  country      |
+-------------+---------------+--------------+---------------+
| 1001        | James         | Maria Anders | United states |
| 1002        | Mike          | Trujillo     | Germany       |
| 1003        | Kate          | Mataderos    | France        |
+-------------+---------------+--------------+---------------+
3 rows in set (0.00 sec)

sql> select * from orders;
+----------+-------------+--------------+
| order_id | customer_id | city         |
+----------+-------------+--------------+
| 1103     | 21          | Edmold       |
| 1103     | 21          | Edmold       |
| 1104     | 22          | Maria Anders |
| 1105     | 23          | Mataderos    |
+----------+-------------+--------------+
4 rows in set (0.00 sec)

sql> select customer_id from customer UNION select city from orders;
+--------------+
| customer_id  |
+--------------+
|   1001       |
|   1002       |
|   1003       |
|    21        |
|    22        |
|    23        |
+--------------+
6 rows in set (0.11 sec)

sql> select city from customer UNION select city from orders;
+--------------+
| city         |
+--------------+
| Maria Anders |
| Trujillo     |
| Mataderos    |
| Edmold       |
+--------------+
4 rows in set (0.11 sec)

In the above example, the union operator will joins the table based on the condition that assigned to a column.

Summary

shape Key Points

  • SQL Union Operator – Is utilized to join more than one outcome set obtained from SELECT statement.