Node.js - MySQL LEFT JOIN

In this post, we will discuss how to perform LEFT 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
				

LEFT JOIN

LEFT JOIN join two or more tables by resulting all rows from table1 and only matched rows from table2 with respect to matched rows from table1.

Syntax:

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

connection_data.connect(function(error) {
connection_data.query("SELECT columns... FROM table_name1 LEFT 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:

LEFT JOIN Example 1:-

Let's perform left join based on vid column from village and id column from field.

// 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) {	
// Left Join
  connection_data.query("SELECT village.vid,village.vname,village.people,field.district,field.type FROM village 
  LEFT 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'
  },
  RowDataPacket {
    vid: 6,
    vname: 'patna',
    people: 100,
    district: null,
    type: null
  },
  RowDataPacket {
    vid: 8,
    vname: 'bapatla',
    people: 40,
    district: null,
    type: null
  }
]

We can see that all the records from village table were returned and 6,8 Id's are not present in field table. so for these records, null is returned in field table columns.

LEFT JOIN Example 2:-

Let's perform left join based on vid column from village and id column from field with people greater than 50.

// 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) {
// Left Join
  connection_data.query("SELECT village.vid,village.vname,village.people,field.district,field.type FROM 
  village LEFT JOIN field ON village.vid = field.id WHERE 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'
  },
  RowDataPacket {
    vid: 6,
    vname: 'patna',
    people: 100,
    district: null,
    type: null
  }
]

Summary

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