3

MYSQL: UNION – tutorial with examples

 3 years ago
source link: https://thispointer.com/mysql-union-tutorial-with-examples/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

MYSQL: UNION EXAMPLE

Before we look into the real-world example from MySQL workbench, let us focus on a small concept to make things clear.

Let’s say we have Table1 and Table2 with some data.

UNION-MYSQL-207x300.png
figure2
PRO TIP : UNION clause adds the results vertically.

See the UNION RESULTS in figure2, we got five records with duplicates removed, and row with data <2,R> is the common data result set.

We were moving on to a real-world example now. In the below example, we have two tables carrying the data of two different companies A and B and we will be writing a union query to merge the data of a few columns from both to get the results.

table-company-A.png
table employees_companyA
table-companyB.png
table employees_companyB
SELECT first_name , emp_category FROM employees_companyA
UNION
SELECT first_name , category FROM employees_companyB
 SELECT first_name , emp_category FROM employees_companyA
 UNION
 SELECT first_name , category FROM employees_companyB

Output:-

unionResults.png
results of union query

The record that was common has been added only once <Veronika,
Intern>.

Pay attention to the column names as we can see that the second column name ‘emp_category‘ in the results is taken from the first query ( refer table ’employees_companyA’ ). Let us see what happens on reversing the order of queries and running it again.

SELECT first_name , category FROM employees_companyB
UNION
SELECT first_name , emp_category FROM employees_companyA
 SELECT first_name , category FROM employees_companyB
 UNION
 SELECT first_name , emp_category FROM employees_companyA

Output:-

ReerseOrder.png
second column name is category

Again the column name ‘category’ has been taken from first select query.

MYSQL: UNION RESTRICTIONS

There are a few points that should be considered when working with the UNION clause in MySQL.

  1. All the queries used with the UNION clause must have the same number of columns.
  2. The data type of the columns used in each query should be the same.
  3. We cannot use the UNION clause in subqueries.
  4. We cannot use the UNION clause with aggregate functions.

MYSQL: UNION DISTINCT

As discussed above, the UNION clause will return the single set of results for the common records, and hence DISTINCT is an optional keyword to be used with UNION in MySQL.
Putting a DISTINCT keyword will make no difference in the results of the query.

For example, again running the queries with UNION clause, will give us the same output as without DISTINCT keyword.

SELECT first_name , emp_category FROM employees_companyA
UNION DISTINCT
SELECT first_name , category FROM employees_companyB
 SELECT first_name , emp_category FROM employees_companyA
 UNION DISTINCT
 SELECT first_name , category FROM employees_companyB

Output:-

unionResults-1.png

MYSQL: UNION ALL

ALL is another keyword which can be used with the UNION clause. The difference it makes to the results is that when ALL is applied to the UNION clause in the query, it will additionally return the duplicate results of multiple queries. Let us see with an example:

Again, we are taking the same tables here but with a different set of data in Table employees_companyB. Table employees_companyB has some more records which are same as in Table employees_companyA (see the records from emp_no 8 to 11).

TableA.png
TableA – employees_companyA
tableB.png
TableB – employees_companyB

Observe the query and results in the output.

SELECT first_name , emp_category FROM employees_companyA
UNION ALL
SELECT first_name , category FROM employees_companyB
 SELECT first_name , emp_category FROM employees_companyA
 UNION ALL
 SELECT first_name , category FROM employees_companyB

Output:-

Results.png
Results of UNION query

MYSQL: UNION ORDER BY

ORDER BY Clause with UNION operator is used to view the results in a particular order. In the below example we will be viewing the results ordered by first_name.

SELECT first_name , emp_category FROM employees_companyA
UNION ALL
SELECT first_name , category FROM employees_companyB
ORDER BY first_name
 SELECT first_name , emp_category FROM employees_companyA
 UNION ALL
 SELECT first_name , category FROM employees_companyB
 ORDER BY first_name

Output:-

OrderBy.png

We hope this article will help you to build a good understanding on MySQL UNION Clause.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK