Node.js - MySQL INNER JOIN

In this post, we will discuss how to perform Inner Join on MySQL tables in XAMPP Server.

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

Command to install the mysql package:

npm install mysql
				

INNER JOIN

INNER JOIN join two or more tables by joining only matched values in a column.

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 INNER JOIN
Copied

connection_data.connect(function(error) {
connection_data.query("SELECT columns... FROM table_name1 INNER JOIN 
table_name2 ON table_name1.column = table_name2.column", 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 village table with the following records:

Consider the field table with the following records:

INNER JOIN Example 1:-

Let's perform inner join based on vid column from village and id column from field. If both the values in these columns matches, then these rows will be returned.

// 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) {
// Inner Join
  connection_data.query("SELECT village.vid,village.vname,village.people,field.district,field.type 
  FROM village INNER JOIN field ON village.vid = field.id", function (error, result) {
	 let text = ""; 
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    vid: 1,
    vname: 'Kakumanu',
    people: 110,
    district: 'guntur',
    type: 'black'
  },
  RowDataPacket {
    vid: 1,
    vname: 'Kakumanu',
    people: 110,
    district: 'guntur',
    type: 'red'
  },
  RowDataPacket {
    vid: 1,
    vname: 'Kakumanu',
    people: 110,
    district: 'guntur',
    type: 'black'
  },
  RowDataPacket {
    vid: 1,
    vname: 'Kakumanu',
    people: 110,
    district: 'guntur',
    type: 'black'
  },
  RowDataPacket {
    vid: 3,
    vname: 'delhi',
    people: 30,
    district: 'kadapa',
    type: 'red'
  },
  RowDataPacket {
    vid: 3,
    vname: 'delhi',
    people: 30,
    district: 'kadapa',
    type: 'sand'
  },
  RowDataPacket {
    vid: 5,
    vname: 'gogulamudi',
    people: 67,
    district: 'ongole',
    type: 'red'
  }
]

We can see that records with id - 1,3 and 5 were matched. so they were returned.

Example 2:- Inner Join with WHERE Clause

Let's perform inner join based on vid column from village and id column from field. If both the values in these columns matches, then these rows will be returned.

// 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) {	
// Inner Join
  connection_data.query("SELECT village.vid,village.vname,village.people,field.district,field.type 
  FROM village INNER JOIN field ON village.vid = field.id WHERE village.people>50", function (error, result) {
	 let text = ""; 
//Display the records one by one
    console.log(result);

  });
});

Output:

[
  RowDataPacket {
    vid: 1,
    vname: 'Kakumanu',
    people: 110,
    district: 'guntur',
    type: 'black'
  },
  RowDataPacket {
    vid: 1,
    vname: 'Kakumanu',
    people: 110,
    district: 'guntur',
    type: 'red'
  },
  RowDataPacket {
    vid: 1,
    vname: 'Kakumanu',
    people: 110,
    district: 'guntur',
    type: 'black'
  },
  RowDataPacket {
    vid: 1,
    vname: 'Kakumanu',
    people: 110,
    district: 'guntur',
    type: 'black'
  },
  RowDataPacket {
    vid: 5,
    vname: 'gogulamudi',
    people: 67,
    district: 'ongole',
    type: 'red'
  }
]

We can see that records with id - 1,3 and 5 were matched, But people is greater than 50. so row with id-3 is not selected.

Summary

In this post, we seen how to perform INNER JOIN using Node.js in MySQL XAMPP Server. We also seen WHERE Clause along with INNER JOIN.