Node.js MySQL Not

In this post, we will discuss how to return rows based on the condition specified inside WHERE Clause using NOT Operator.

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

Command to install the mysql package:

npm install mysql
				

NOT Operator:

NOT operator return rows when the condition is not true.

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 NOT Operator to specify the condition. It will take two parameters. The first parameter is the SQL Query and the second parameter will handle the result.
Copied

connection_data.connect(function(error) {
connection_data.query("SELECT column/s FROM table_name WHERE NOT condition)", 
function (error, result) { 
    console.log(result);
  });
});

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

It will return the records one by one in the following format:

[
  RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},

 RowDataPacket {
    column1: value,
    column2: value,
    .....,
	.....
},
.....
.....

Here, each RowDataPacket represent a row.

Consider the village table with the following records:

Example 1:- NOT with OR

Node.js Script

1. To select rows which are not with village_name as 'delhi' or people greater than 20.
2. To select rows which are not with with distcict as 'guntur' or people less 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) {
	

// Write SQL query to select rows that are not with village_name 
//as delhi or people greater than 20.
  connection_data.query("SELECT * FROM village WHERE NOT 
  village_name='delhi' OR people>20", function (error, result) {
 
 console.log("Rows except village_name as delhi or people greater than 20");
 
//Display the records one by one
    console.log(result);
  });
  
	// Write SQL query to select rows that are not with 
	//distcict as guntur or people less than 50.
  connection_data.query("SELECT * FROM village WHERE NOT 
  distcict='guntur' OR  people < 50", function (error, result) {
 
 console.log("Rows except distcict as guntur or people less than 50");
 
//Display the records one by one
    console.log(result);
  });
});

Output:

Rows except village_name as delhi or people greater than 20
[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  },
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  }
]
Rows except distcict as guntur or people less than 50
[
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  }
]

Here we used OR operator with NOT to select rows that are not true.

Example 2:- NOT with AND

Node.js Script

1. To select rows which are not with village_name as 'delhi' and people greater than 20.
2. To select rows which are not with with distcict as 'guntur' and people less 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) {
	

// Write SQL query to select rows that are not with 
//village_name as delhi and people greater than 20.
  connection_data.query("SELECT * FROM village WHERE 
  NOT village_name='delhi' AND people>20", function (error, result) {
 
 console.log("Rows except village_name as delhi and people greater than 20");
 
//Display the records one by one
    console.log(result);
  });
  
	// Write SQL query to select rows that are 
	//not with distcict as guntur and people less than 50.
  connection_data.query("SELECT * FROM village WHERE NOT 
  distcict='guntur' AND  people < 50", function (error, result) {
 
 console.log("Rows except distcict as guntur and people less than 50");
 
//Display the records one by one
    console.log(result);
  });
});

Output:

Rows except village_name as delhi and people greater than 20
[
  RowDataPacket {
    village_name: 'Kakumanu',
    distcict: 'Guntur',
    people: 110
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  }
]
Rows except distcict as guntur and people less than 50
[
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  }
]

Here we used AND operator with NOT to select rows that are not true.

NOT equal Example 3:-

Node.js Script to select rows such that village_name is not equal to kakumanu.

// 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 select rows that 
//are not with village_name as Kakumanu
  connection_data.query("SELECT * FROM village 
  WHERE NOT village_name='Kakumanu'", function (error, result) {
 
 console.log("Rows except village_name-Kakumanu ");
 
//Display the records one by one
    console.log(result);
 
  });
});

Output:

Rows except village_name-Kakumanu
[
  RowDataPacket {
    village_name: 'delhi',
    distcict: 'delhi',
    people: 30
  },
  RowDataPacket {
    village_name: 'patna',
    distcict: 'patna',
    people: 100
  },
  RowDataPacket {
    village_name: 'bapatla',
    distcict: 'guntur',
    people: 40
  },
  RowDataPacket {
    village_name: 'gogulamudi',
    distcict: 'guntur',
    people: 67
  }
]

Here we used = operator that selects village_name as Kakumanu, But NOT operator makes this condition as false and return remaining rows except Kakumanu.

Summary

So we seen how to use NOT Operator on a MySQL Table in XAMPP Server using Node.js. It can be possible to specify multiple conditions along with NOT Operator using AND,OR, other operators.