- 5 years ago
- Zaid Bin Khalid
- 2,572 Views
-
4
In this session, you will successfully learn how to use the SQL UNION operator with syntax and examples.
The UNION Operator.
The Union SQL operator can be drive by combining the resulting sets of two or more Select Statement. Additionally, it removes the duplicate value of rows that are between different Select statements.
However, each Select statement that is used for UNION operation must hold the same number of columnar fields along with the same data types in the resulting sets.
Important Note.
By default, the Union Operator only select distinct values. However, to allow duplicate values, one may use ALL keywords along with Union Operation.
Mention below is the basic rules of using Union operation, where you can combine the resulting sets of two Select queries.
- Firstly, make sure among all questions, the order and number of columns must also be the same.
- Secondly, check the compatibility of the data type for each corresponding column
Hence, if your tables satisfy these criteria, then you can apply the Union operator query on them.
Syntax for Union Operator.
The syntax used for the UNION operator during SQL can be termed as.
SELECT column_list FROM table1_name
UNION SELECT column_list FROM table2_name;
To understand the union operation example efficiently, we can assume some fields hypothetically, such as first_name and last_name that already existed in both employee and Customer tables. However, both tablets do currently not exist in our database table.
+----+------------+-----------+--------+
| id | first_name | last_name | salary |
+----+------------+-----------+--------+
| 1 | Ethan | Hunt | 5000 |
| 2 | Tony | Montana | 6500 |
| 3 | Sarah | Connor | 8000 |
| 4 | Rick | Deckard | 7200 |
| 5 | Martin | Blank | 5600 |
+----+------------+-----------+--------+
Table: employees
+----+------------+-----------+----------+
| id | first_name | last_name | city |
+----+------------+-----------+----------+
| 1 | Maria | Anders | Berlin |
| 2 | Fran | Wilson | Madrid |
| 3 | Dominique | Perrier | Paris |
| 4 | Martin | Blank | Turin |
| 5 | Thomas | Hardy | Portland |
+----+------------+-----------+----------+
Table: customers
Now, this is the time to perform Union Operation to combine the output of two different queries.
The following statement will return the names of first and last names from customers and employee tables.
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM customers;
After the execution of the above statement, the resulting output set might look like.
+---------------+--------------+
| first_name | last_name |
+---------------+--------------+
| Ethan | Hunt |
| Tony | Montana |
| Sarah | Connor |
| Rick | Deckard |
| Martin | Blank |
| Maria | Anders |
| Fran | Wilson |
| Dominique | Perrier |
| Thomas | Hardy |
+---------------+--------------+
Typically, the UNION operator will eliminate the duplicate rows from the combined result set. That’s why the output result of the above query will give only nine rows. Also, you can notice the name “Martin Blank” can appear in both tables, customers, and employees.
So if you are interested in keeping all the values including duplicate than you should use All keyword. This can be expressed as follows.
SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers;
- 5 years ago
- Zaid Bin Khalid
- 2,572 Views
-
4