MySQL – UNION Operator


MySQL – UNION Operator

Table of content

”;


Previous

Next

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 −

PHP
NodeJS
Java
Python

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);

To combine tables using UNION operator through a PHP program, we need to execute the SQL statement with UNION operator using the mysql2 function named query() as follows −

sql= " SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2";
con.query(sql);

To combine tables using UNION operator through a PHP program, we need to execute the SQL statement with UNION operator using the JDBC type 4 driver function named executeQuery() as follows −

String sql = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2";
statement.executeQuery(sql);

To combine tables using UNION operator through a PHP program, we need to execute the SQL statement with UNION operator using the MySQL Connector/Python function named execute() as follows −

union_query = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2"
cursorObj.execute(union_query);

Example

Following are the implementations of this operation in various programming languages −

PHP
NodeJS
Java
Python 
$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.

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating PROSPECT table
  sql = "CREATE TABLE PROSPECT( fname varchar(400), lname varchar(400), addr varchar(200));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO PROSPECT (fname, lname, addr) VALUES (''peter'', ''Jones'', ''482 Rush St., Apt. 402''), (''Bernice'', ''Smith'', ''916 Maple Dr.'');"
  con.query(sql);

  //Creating CUSTOMER table
  sql = "CREATE TABLE CUSTOMER( last_name varchar(400), first_name varchar(400), address varchar(200));"
  con.query(sql);
  
  //Inserting Records
  sql = "INSERT INTO CUSTOMER (last_name, first_name, address) VALUES (''Peterson'', ''Grace'', ''16055 Seminole Ave.''), (''Smith'', ''Bernice'', ''916 Maple Dr.''), (''Brown'', ''Walter'', ''8602 1st St.'');"
  con.query(sql);

  //Creating vendor table
  sql = "CREATE TABLE vendor( company varchar(400), street varchar(400));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO vendor (company, street) VALUES (''ReddyParts, Inc.'', ''38 Industrial Blvd.''), (''Parts-to-go, Ltd.'', ''213B Commerce Park.'');"
  con.query(sql);

  //Using UNION
  sql = "SELECT fname, lname, addr FROM prospect UNION SELECT first_name, last_name, address FROM customer UNION SELECT company, '''', street FROM vendor;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { fname: ''peter'', lname: ''Jones'', addr: ''482 Rush St., Apt. 402'' },
  { fname: ''Bernice'', lname: ''Smith'', addr: ''916 Maple Dr.'' },
  { fname: ''Grace'', lname: ''Peterson'', addr: ''16055 Seminole Ave.'' },
  { fname: ''Walter'', lname: ''Brown'', addr: ''8602 1st St.'' },
  { fname: ''ReddyParts, Inc.'', lname: '''', addr: ''38 Industrial Blvd.'' },
  { fname: ''Parts-to-go, Ltd.'', lname: '''', addr: ''213B Commerce Park.'' }
]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class UnionOperator {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT fname, lname, addr FROM prospect UNION SELECT first_name, last_name, address FROM customer UNION SELECT company, '''', street FROM vendor";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String fname = rs.getString("fname");
                String lname = rs.getString("lname");
                String addr = rs.getString("addr");
                System.out.println("First Name: " + fname + ", Last Name: " + lname + ", Address: " + addr);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Output

The output obtained is as shown below −

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.        

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
union_query = f"""
SELECT fname, lname, addr FROM prospect
    UNION
    SELECT first_name, last_name, address FROM customer
    UNION
    SELECT company, '''', street FROM vendor;
"""
cursorObj.execute(union_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

(''Peter'', ''Jones'', ''482 Rush St., Apt. 402'')
(''Bernice'', ''Smith'', ''916 Maple Dr.'')
(''Grace'', ''Peterson'', ''16055 Seminole Ave.'')
(''Walter'', ''Brown'', ''8602 1st St.'')
(''ReddyParts, Inc.'', '''', ''38 Industrial Blvd.'')
(''Parts-to-go, Ltd.'', '''', ''213B Commerce Park.'')



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *