Node.js - MySQL Union

In this post, we will discuss how to perform UNION, UNION ALL operations on MySQL Data in XAMPP Server using Node.js script.

It is important to install mysql package in node.js.

Command to install the mysql package:

npm install mysql
				

UNION() Function:

UNION() is used to return the values from two or more tables without duplicates.

UNION ALL()

UNION ALL() is used to return the values from two or more tables with duplicates.

We have to note that

1. Column names may not be same but they are in same order with respect to datatypes.
2. Total number of columns while selecting the columns with UNION must be same.

Steps for Node.js script:

Now let's see steps

1. First start your XAMPP Server (Both Apache and MySQL).
2. Open Notepad or any text-editor and write the Node.js script
3. In that script, first we have to load the mysql package using the below syntax
var mysql_package = require('mysql');
4. Create the connection using the server,username and password.
Copied

var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"database_name"
});

5. Write the sql query that uses UNION/UNION ALL.
Copied

//UNION
connection_data.connect(function(error) {
connection_data.query("SELECT columns from  from table_name1 UNION SELECT 
columns from  from table_name2", function (error, result) {
    console.log(result);
  });
});
//UNION ALL
connection_data.connect(function(error) {
connection_data.query("SELECT columns from  from table_name1 UNION ALL 
SELECT columns from  from table_name2", function (error, result) { 
    console.log(result);
  });
});

6. Now type the following command in your command prompt to run the script.
node file_name.js

Consider the village1 table with the following records:

Consider the village2 table with the following records:

Example 1:- UNION

Let's select all the columns from the tables - village1 and village2 and perform UNION.

// Load the mysql package
var mysql_package = require('mysql');
// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});
connection_data.connect(function(error) {	
// Write SQL query to return the rows by performing union on 
//two tables  village1 and village2.
connection_data.query("SELECT name,district from village1 
UNION select name,district from village2", function (error, result) {	  
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket { name: 'kakumanu', district: 'guntur' },
  RowDataPacket { name: 'kommuru', district: 'bapatla' },
  RowDataPacket { name: 'pnp', district: 'hyd' },
  RowDataPacket { name: 'bkpalem', district: 'np.sagar' },
  RowDataPacket { name: 'ujjain', district: 'ujjain' }
]

So, we can see that all rows were uniquely returned without duplicates.

Example 2:- UNION ALL

Let's select all the columns from the tables - village1 and village2 and perform UNION ALL.

// Load the mysql package
var mysql_package = require('mysql');
// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});
connection_data.connect(function(error) {	
// Write SQL query to return the rows by performing union 
//all on two tables  village1 and village2.
  connection_data.query("SELECT name,district from village1 UNION ALL 
  select name,district from village2", function (error, result) {  
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket { name: 'kakumanu', district: 'guntur' },
  RowDataPacket { name: 'kakumanu', district: 'guntur' },
  RowDataPacket { name: 'kakumanu', district: 'guntur' },
  RowDataPacket { name: 'kommuru', district: 'bapatla' },
  RowDataPacket { name: 'pnp', district: 'hyd' },
  RowDataPacket { name: 'pnp', district: 'hyd' },
  RowDataPacket { name: 'bkpalem', district: 'np.sagar' },
  RowDataPacket { name: 'ujjain', district: 'ujjain' }
]

So, we can see that all rows along with duplicates.

Summary

In this post, we seen how to use mysql UNION(),UNION ALL() functions in Node.js Script with each one example.