Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

UNION Operator

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

UNION Operator
MySQL UNION Operator
The UNION operator in MySQL combines the data (without duplicate records) from multiple tables.
We can use UNION if we want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set.
To use the UNION operator on multiple tables, all these tables must be union compatible. And they are said to be union compatible if and only if they meet the following criteria 
The same number of columns selected with the same datatype.
These columns must also be in the same order.
They need not have same number of rows.
Once these criterion are met, the UNION operator returns the rows from multiple tables as a resultant table which is void of all duplicate values from these tables.
UNION is available as of MySQL 4.0. This section illustrates how to use it.
Syntax
The basic syntax of UNION operator in MySQL is as follows 
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Example
Let us first create the prospective customers table named PROSPECT using the following CREATE TABLE query 
CREATE TABLE PROSPECT (
   FNAME CHAR(20) NOT NULL,
   LNAME CHAR(20),
   ADDRESS VARCHAR(100) NOT NULL
);
Now, we insert records into this table using INSERT statement below 
INSERT INTO PROSPECT VALUES
('Peter', 'Jones', '482 Rush St., Apt. 402'),
('Bernice', 'Smith', '916 Maple Dr.');
The PROSPECT table is created as 
FNAME
LNAME
ADDRESS
Peter
Jones
482 Rush St., Apt. 402
Bernice
Smith
916 Maple Dr.
ACTIVE Table −
We then create an active customers table named ACTIVE using the following CREATE TABLE query 
CREATE TABLE ACTIVE (
   FNAME CHAR(20) NOT NULL,
   LNAME CHAR(20),
   ADDRESS VARCHAR(100) NOT NULL
);
Using the following INSERT statement, insert records into the ACTIVE table 
INSERT INTO ACTIVE VALUES
('Grace', 'Peterson', '16055 Seminole Ave.'),
('Bernice', 'Smith', '916 Maple Dr.'),
('Walter', 'Brown', '8602 1st St.');
The ACTIVE table is created as 
FNAME
LNAME
ADDRESS
Grace
Peterson
16055 Seminole Ave.
Bernice
Smith
916 Maple Dr.
Walter
Brown
8602 1st St.
Now, you want to create a single mailing list by merging names and addresses from all the tables. UNION provides a way to do this.
The following query illustrates how to select names and addresses from the tables all at once 
SELECT FNAME, LNAME, ADDRESS FROM PROSPECT
UNION
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE;
Output
Following output is obtained 
FNAME
LNAME
ADDRESS
Peter
Jones
482 Rush St., Apt. 402
Bernice
Smith
916 Maple Dr.
Grace
Peterson
16055 Seminole Ave.
Walter
Brown
8602 1st St.
As you can see, duplicates are avoided in the result-set.
UNION with WHERE clause
We can use the WHERE clause with UNION operator to filter the results of each SELECT statement before combining them.
Syntax
Following is the syntax for using the WHERE clause with UNION operator 
SELECT column1, column2, column3
FROM table1
WHERE column1 = 'value1'
UNION
SELECT column1, column2, column3
FROM table2
WHERE column1 = 'value2';
Example
Let us use the same tables from the previous example to retrieve combined records using UNION operator with WHERE clause 
SELECT FNAME, LNAME, ADDRESS FROM PROSPECT WHERE LNAME = 'Jones' 
UNION 
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE WHERE LNAME = 'Peterson';
Output
Following output is obtained 
FNAME
LNAME
ADDRESS
Peter
Jones
482 Rush St., Apt. 402
Grace
Peterson
16055 Seminole Ave.
UNION with ORDER BY clause
When we use UNION with ORDER BY clause, it combines the sorted result sets of all SELECT statements and produces a single sorted result set.
Syntax
Following is the basic syntax to use UNION operator with ORDER BY clause 
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2
ORDER BY column_name;
Example
Let us try to sort the table records in ascending order with respect to values in the 'lname' column of result set, using the following query 
SELECT FNAME, LNAME, ADDRESS FROM PROSPECT 
UNION 
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE 
ORDER BY LNAME;
Output
Following output is obtained 
FNAME
LNAME
ADDRESS
Walter
Brown
8602 1st St.
Peter
Jones
482 Rush St., Apt. 402
Grace
Peterson
16055 Seminole Ave.
Bernice
Smith
916 Maple Dr.
UNION with Aliases
We can use aliases in a MySQL statement of UNION operator to give a table or column a temporary name, which can be useful when working with multiple tables or columns with similar names.
When using UNION with aliases, it's important to note that the column aliases are determined by the first SELECT statement. Therefore, if you want to use different aliases for the same column in different SELECT statements, you need to use column aliases in all SELECT statements to ensure consistent column names in the final result set.
Syntax
Following is the syntax for using Union with Aliases 
SELECT column1 AS alias1, column2 AS alias2
FROM table1
UNION
SELECT column3 AS alias1, column4 AS alias2
FROM table2;
Example
In this following example, we are trying to combine two tables using aliases to represent the fields in result-set obtained 
SELECT FNAME AS Firstname, 
LNAME AS Lastname, ADDRESS AS Address 
FROM PROSPECT UNION 
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE; 
Output
Following output is obtained 
Firstname
Lastname
Address
Peter
Jones
482 Rush St., Apt. 402
Bernice
Smith
916 Maple Dr.
Grace
Peterson
16055 Seminole Ave.
Walter
Brown
8602 1st St.
UNION ALL Operator
If you want to select all records, including duplicates, follow the first UNION keyword with ALL 
SELECT fname, lname, ADDRESS  FROM prospect
UNION ALL
SELECT fname, lname, ADDRESS  FROM active;
Output
Following output is obtained 
FNAME
LNAME
ADDRESS
Peter
Jones
482 Rush St., Apt. 402
Bernice
Smith
916 Maple Dr.
Grace
Peterson
16055 Seminole Ave.
Bernice
Smith
916 Maple Dr.
Walter
Brown
8602 1st St.
UNION Operator Using Client Program
In addition to applying the UNION Operator in MySQL table directly in MySQL server, we can also apply the UNION operation on a MySQL table using a client program.
Syntax
Following are the syntaxes of the UNION Operator in MySQL table in various programming languages 
PHPNodeJSJavaPython
To combine tables using UNION operator through a PHP program, we need to execute the SQL statement with UNION operator using the mysqli function named query() as follows 
$sql = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) 
FROM table2";
$mysqli->query($sql);
Example
Following are the implementations of this operation in various programming languages 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "SELECT fname, lname, addr FROM prospect  UNION  SELECT first_name, last_name, address FROM customer  UNION  SELECT company, '', street FROM vendor;";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("First Name %s, Last Name: %s, Address %s",   
      $row["fname"],
      $row["lname"],
      $row["addr"],);
      printf("\n");         
   }
} else {
   printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close();
Output
The output obtained is as follows 
Table records:
First Name Peter, Last Name: Jones, Address 482 Rush St., Apt. 402
First Name Bernice, Last Name: Smith, Address 916 Maple Dr.
First Name Grace, Last Name: Peterson, Address 16055 Seminole Ave.
First Name Walter, Last Name: Brown, Address 8602 1st St.
First Name ReddyParts, Inc., Last Name: , Address 38 Industrial Blvd.
First Name Parts-to-go, Ltd., Last Name: , Address 213B Commerce Park.
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿
﻿

FNAME	LNAME	ADDRESS
Grace	Peterson	16055 Seminole Ave.
Bernice	Smith	916 Maple Dr.
Walter	Brown	8602 1st St.

FNAME	LNAME	ADDRESS
Peter	Jones	482 Rush St., Apt. 402
Bernice	Smith	916 Maple Dr.
Grace	Peterson	16055 Seminole Ave.
Walter	Brown	8602 1st St.

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

UNION Operator

MySQL UNION Operator

Syntax

Example

Output

UNION with WHERE clause

Syntax

Example

Output

UNION with ORDER BY clause

Syntax

Example

Output

UNION with Aliases

Syntax

Example

Output

UNION ALL Operator

Output

UNION Operator Using Client Program

Syntax

Example

Output