Logo
×

MySQL Union

MySQL contains the same categories of data in a different table. Sometimes, you require multiple tables to collect data together. MySQL union is a function to combine two table's data on a single page.The union in MySQL removes duplicate rows from the tables and returns unique data in a result set. The union displays the output of more than one table. MySQL union working procedure starts with the SELECT statement. The "UNION" keyword requires at least two tables for their combination. You can also select a column list from several tables.

When the MySQL union shows the entire row's of the two tables in a single set, it is called a "UNION ALL" command. This command works with several conditions and clauses in the MySQL query. The "UNION ALL" does not remove duplicate data from the table.

Rules of the MySQL Union

  • The union must need the same name and number of the column of the table.
  • The column list of the tables must be in the same order for the union query.
  • The table columns have to be similar for the combined similar type of data on one page.
  • The SELECT and UNION statements are necessary to display table output.
  • MySQL clauses use for showing output as per the required condition.

Syntax of the MySQL Union function

MySQL union for two table's syntax shows below.

SELECT column_1, column_2 FROM first_table
UNION
SELECT column_1, column_2 FROM second_table;

Syntax of the multiple MySQL Union function

MySQL union for two table's syntax shows below.

SELECT column_1, column_2 FROM first_table
UNION
SELECT column_1, column_2 FROM second_table
UNION
SELECT column_1, column_2 FROM third_table;

Syntax of the MySQL Union ALL function

MySQL union for two table's syntax shows below.

SELECT column_1, column_2 FROM first_table
UNION ALL
SELECT column_1, column_2 FROM second_table;

The setting of the MySQL Union

  • Creates the first table with required columns and their data type.
  • Insert values in the first table as per requirement.
  • Show the first table's data using the output query.
  • Create the second table with required columns and their data type.
  • Insert values in the second table as per requirement.
  • Show the second table's data using the output query.

Examples of the UNION and UNION ALL

Example1:In this example, you can use a single union function for combining two tables. This function uses technology and a study table. Here, both table has same column name and its data types.

mysql> SELECT department_id, name, admissions FROM Technology
UNION
SELECT department_id, name, admissions FROM STUDY;

Output 

MySQL Union

This output shows combine information of the technology and study tables. The first four rows represent the technology table. The last three rows represent the study table. You get three columns and seven rows using the union function.

Example2: This example uses multiple union functions for combining more than two tables. You can combine more than two tables using the union function and removes duplicate functions. Here, the example combines technology, study, and arts tables.

mysql> SELECT department_id, name, admissions FROM Technology
UNION
SELECT department_id, name, admissions FROM STUDY
UNION
SELECT department_id, name, admissions FROM ARTS;

Output 

MySQL Union

The above image displays eleven rows and three columns of the three tables. You can see table data as per the table's position with the union function. This function removes duplicate information and displays the original value of the tables. The first table, which is technology, contains the first few rows of the table.

Example3: This example uses the ORDER BY clause with the union function. This clause comes with the "ASC" keyword for ascending order. The "name" columns display in the required order.

mysql> SELECT department_id, name, admissions FROM Technology
UNION
SELECT department_id, name, admissions FROM STUDY
ORDER BY name ASC;

Output 

MySQL Union

This output image returns table information in the ascending order of the name column. This clause applies to the two tables simultaneously and combines information from the first alphabet to the last alphabet.

Example4:MySQL "union all" query works with a required column of the table. This function gives three columns of the technology and study tables. You do not need any clause and condition for the combined two table’s information.

mysql> SELECT department_id, name, admissions FROM Technology
UNION ALL
SELECT department_id, name, admissions FROM STUDY;

Output 

MySQL Union

The "union all" function's output returns all information of the given table. The above image returns all similar and different value contains in the table.

Example5:Execute the following query to get the working procedure of the union function. This function works on an entire table of the database. It returns unique and combined information of the tables.

mysql> SELECT * FROM Technology
UNION 
SELECT * FROM STUDY;

Output

MySQL Union

You can able to display different information using the union function. The first table shows all information but the second table shows only three rows of the table. The second table removes the last row, similar to the first table.

Example6: This example works with multiple "union all" functions in the MySQL query. The union all function combines more than two tables in one table.

mysql> SELECT department_id, name, admissions FROM Technology
UNION ALL
SELECT department_id, name, admissions FROM STUDY
UNION ALL
SELECT department_id, name, admissions FROM ART;

Output

MySQL Union

Here, you can see three different table's data in one image. You can categories the different tables using department id columns. The output image returns the entire information of the technology, arts, and study tables.

Example7: This example works with the "union all" function and the "order by" clause without specifying any order.

mysql> SELECT department_id, name, admissions FROM Technology
UNION ALL
SELECT department_id, name, admissions FROM STUDY
ORDER BY name;

Output 

MySQL Union

The union all function returns table data with an order of the name column. This order by clause sets ascending by default.

Example8:The "union all" function applies with the select all (*) columns of the table. This function works on the entire table format and data.

mysql> SELECT * FROM technology 
UNION ALL
SELECT * FROM study;

Output

MySQL Union

Differences between UNION and JOIN in MySQL

The following table shows similarities and differences between UNION and JOIN.

UNIONJOIN
The union combines the result set of the multiple tables.The join combines the many tables' data using conditions.
MySQL union combines multiple table data into a new row of the table.MySQL join combines multiple table data into a new row of the table.
The selected columns of the table are the same.The selected columns of the table are similar or different.
The union removes duplicate data and display distinct data.The join displays distinct and duplicate data.

Differences between UNION and UNION ALL

The following table shows similarities and differences between UNION and UNION ALL.

UNIONUNION ALL
The union combines the result set of the multiple tables.The union all combines the result set of the multiple tables.
The union removes duplicate data from the tables.The union ALL removes duplicate data from the tables.
The union is lower than UNION ALL function.The UNION ALL is higher than the UNION function.

Related Topics

MySQL Environmental Setup

The MySQL Environmental Setup MySQL is free, open-source, and cross-platform software, which can be downloaded from its official website. MySQL management system installs on Linux, macOS, and windows. MySQL requires a framework...

6 minutes read.

MySQL SOUNDEX() function

In this context, we will learn how we can use the MySQL SOUNDEX() function with proper syntax and good examples. Introduction of MySQL SOUNDEX() function SOUNDEX() function in MySQL is used to...

3 minutes read.

MySQL variable

The MySQL variable is essential for storing data in the table. The variable declares data with a specific name or label to avoid confusion. This data label is used in...

6 minutes read.

MySQL Show index

This query helps to access and retrieve table information. This syntax displays the required index or all indexes of the table. It also displays the index type as per the...

3 minutes read.

MySQL CURRENT_DATE() Function

In this context, we will learn how we can use the MySQL CURRENT_DATE() function to get the current date, and we will see in the two formats, such as strings...

2 minutes read.

MySQL Subquery

The subquery is a MySQL query used to manage data operations. Mainly subquery helps to retrieve data with the necessary condition. It creates a nested query with two different queries....

5 minutes read.

MySQL EXPORT_SET() function

In this context, we will learn how we can use the MySQL EXPORT_SET() function with proper syntax and good examples. Introduction of MySQL EXPORT_SET() function This function returns a string and shows...

3 minutes read.

MySQL DAY() function

In this context, we will learn how we can use the MySQL DAY() function with proper syntax and good examples. Introduction of MySQL DAY() function This function in MySQL is used to...

2 minutes read.

MySQL Triggers

MySQL trigger is a function of the stored procedure to respond to the system program. This function responds and runs any data table event automatically. You can use it for...

5 minutes read.

MySQL RIGHT() Function

In this context, we will learn how we can use the MySQL RIGHT() Function with proper syntax and good examples. Introduction of MySQL RIGHT() function The RIGHT() function in MySQL is used...

3 minutes read.

MySQL INNER JOIN

MySQL inner join connects two tables by using their common columns. It is the basic join of the MySQL system. It is used to returns only those results from the...

4 minutes read.

MySQL Date and Time function

The date function displays day, year, month, time, and current date. It shows the date and time as per the requirement of the applications. The data either store on the...

8 minutes read.

MySQL Aggregate function

The aggregation function works on the multiple values and returns single value output.  It makes the advanced and complex operation simple. The summation (SUM), MAX, AVG, MIN, COUNT functions are...

5 minutes read.

MySQL Full text index

Full text index The full-text index in the table isused to search the full text of the data. This index assigns to the table using a "FULLTEXT" keyword. First, the table...

3 minutes read.

MySQL SEC_TO_TIME() function

In this context, we will learn how we can use the MySQL SEC_TO_TIME() function with proper syntax and good examples. Introduction of MySQL SEC_TO_TIME() function This function in MySQL is used to...

2 minutes read.

MySQL CEIL() function

In this context, we will learn how we can use the MySQL CEIL () function with proper syntax and good examples. Introduction of MySQL CEIL() function Input is taken by the CEIL()...

3 minutes read.

MySQL FIND_IN_SET() function

In this context, we will learn how we can use the MySQL MOD() function with proper syntax and good examples. Introduction of MySQL MOD() function MySQL provides a built-in string function called...

4 minutes read.

MySQL: Entity-Relationship Model

Entity-Relationship Model Entity-Relationship model or E R model is used to create a relationship between different attributes or entities. It describes the structure of the database with the help of the...

5 minutes read.

MySQL NATURAL JOIN

The natural join combines multiple tables using a column with the same name and data type. This operation combines a row of the two tables using common columns. This join...

4 minutes read.

MySQL COALESCE function

This statement displays the first non-null value of the table data. The COALESCE function returns a null value when all values of the table are null or do not find...

3 minutes read.