Node.js MySQL - Comparison Operators

In this post, we will discuss about different comparison operators used inside sql query with WHERE Clause.

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

Command to install the mysql package:

npm install mysql
				

Comparison Operators

There are 6 types of comparison operators used to specify the conditions in WHERE Clause.

1. Less than operator (<)
2. Less than or equal to operator (<=)
3. Greater than operator (>)
4. Greater than or equal to operator (>=)
5. Equal to(=)
6. Not Equal to(<>)

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 WHERE Clause with Comparison operators.
Copied

connection_data.connect(function(error) {
  connection_data.query("SELECT * FROM table_name WHERE 
  column comparison_operator value", 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 details table with the following records:

Comparision Example 1:- Lessthan & Lessthan or equal to

Node.js Script

1. Return the rows from details table with price less than 20.
2. Return the rows from details table with price less than or equal to 40.

// 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 from details table 
//with price less than 20.
  connection_data.query("SELECT * FROM details WHERE price<20",
  function (error, result) {
 console.log("Rows from details table with price less than 20");
//Display the records one by one
    console.log(result);
  }); 
  // Write sql query to return the rows from details table 
  //with price less than or equal to 40.
  connection_data.query("SELECT * FROM details 
  WHERE price <=40", function (error, result) {
 console.log("Rows from details table with price less than or equal to 40");

//Display the records one by one
    console.log(result);
  });
});

Output:

Rows from details table with price less than 20
[
  RowDataPacket {
    id: 1,
    name: 'facility1',
    price: 12,
    rate: 34,
    other1: 6.78,
    other2: 67.89
  }
]
Rows from details table with price less than or equal to 40
[
  RowDataPacket {
    id: 1,
    name: 'facility1',
    price: 12,
    rate: 34,
    other1: 6.78,
    other2: 67.89
  },
  RowDataPacket {
    id: 1,
    name: 'facility2',
    price: 34,
    rate: 34,
    other1: 1.78,
    other2: 0.9
  },
  RowDataPacket {
    id: 1,
    name: 'facility3',
    price: 23,
    rate: 34,
    other1: 6.18,
    other2: 0
  }
]

Comparision Example 2:- Greater than & Greater than or equal to

1. Return the rows from details table with price greater than 20.
2. Return the rows from details table with price greater than or equal to 40.

// 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 from details 
	//table with price greater than 20.
  connection_data.query("SELECT * FROM details WHERE price>20", 
  function (error, result) {
 console.log("Rows from details table with price greater than 20");
//Display the records one by one
    console.log(result);
  }); 
  // Write sql query to return the rows from details table 
  //with price greater than or equal to 40.
  connection_data.query("SELECT * FROM details WHERE price >=40",
function (error, result) {
console.log("Rows from details table with price greater than or equal to 40");
//Display the records one by one
    console.log(result);
  });
});

Output:

Rows from details table with price greater than 20
[
  RowDataPacket {
    id: 1,
    name: 'facility2',
    price: 34,
    rate: 34,
    other1: 1.78,
    other2: 0.9
  },
  RowDataPacket {
    id: 1,
    name: 'facility3',
    price: 23,
    rate: 34,
    other1: 6.18,
    other2: 0
  },
  RowDataPacket {
    id: 1,
    name: 'facility4',
    price: 45,
    rate: 34,
    other1: 36.76,
    other2: 67.9
  }
]
Rows from details table with price greater than or equal to 40
[
  RowDataPacket {
    id: 1,
    name: 'facility4',
    price: 45,
    rate: 34,
    other1: 36.76,
    other2: 67.9
  }
]

Comparision Example 3:- Equal to & Not equal to

1. Return the rows from details table where price is 20
2. Return the rows from details table where price is not equal to 20

// 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 from details 
	//table with price equal to 20.
  connection_data.query("SELECT * FROM details 
  WHERE price=20", function (error, result) {
 console.log("Rows from details table where price equals 20");
//Display the records one by one
    console.log(result);
  });
  // Write sql query to return the rows from details table 
  //with price not equal to 20.
  connection_data.query("SELECT * FROM details WHERE price <> 20", 
  function (error, result) {
  console.log("Rows from details table where price not equal to 20");
//Display the records one by one
    console.log(result);
  });
});

Output:

Rows from details table where price equals 20
[]
Rows from details table where price not equal to 20
[
  RowDataPacket {
    id: 1,
    name: 'facility1',
    price: 12,
    rate: 34,
    other1: 6.78,
    other2: 67.89
  },
  RowDataPacket {
    id: 1,
    name: 'facility2',
    price: 34,
    rate: 34,
    other1: 1.78,
    other2: 0.9
  },
  RowDataPacket {
    id: 1,
    name: 'facility3',
    price: 23,
    rate: 34,
    other1: 6.18,
    other2: 0
  },
  RowDataPacket {
    id: 1,
    name: 'facility4',
    price: 45,
    rate: 34,
    other1: 36.76,
    other2: 67.9
  }
]

Summary

In this post, we seen how to use comparison operators with WHERE Clause in MySQL XAMPP Server using Node.js script.