Node.js - MySQL Where

---

---

In MySQL, the WHERE clause can be used in SELECT, DELETE and UPDATE queries. The WHERE clause allows you to specify a search condition for the rows returned by a query. When using mysql module with a Node.js application, the query() method of connection object executes a query string. To apply filter on a SELECT or UPDATE or DELETE queries, the WHERE clause is used. In this chapter, various examples of usage of WHERE clause in a Node.js application are explained.

The following shows the syntax of the WHERE clause in a SELECT statement −

SELECT 
   select_list
FROM
   table_name
WHERE
   search_condition;

The search_condition is a combination of one or more expressions using the logical operator AND, OR and NOT.

Various operators used in WHERE clause are −

In MySQL, a predicate is a Boolean expression that evaluates to TRUE, FALSE, or UNKNOWN. The SELECT statement will include any row that satisfies the search_condition in the result set.

Logical operators

In the following Node.js application code, the SELECT query string passed to the query() method uses WHERE clause to fetch the employee records whose salary exceeds Rs. 25000.

Example

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",

   database: "mydb"
});

var qry =`SELECT name,salary FROM employee WHERE salary>25000;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
  
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(results);
   });

   con.end();
});

Output

[
  RowDataPacket { name: 'Anil', salary: 30000 },
  RowDataPacket { name: 'Meena', salary: 27000 }
]

The filter condition in the WHERE clause may be a compound logical expression, where the individual logical expressions that use the comparison operators (>, <, >=.<=, ==, =) are combined with either AND, OR or NOT operators.

Let us change the SELECT query in the query() method to apply the condition on salary field such that it is more than 25000 and less than 30000.

Example

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`SELECT name,salary FROM employee WHERE salary>25000 and salary>30000;`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
  
   con.query(qry, function (err, results) {
      if (err) throw err;
      console.log(results);
   });

   con.end();
});

Output

[ RowDataPacket { name: 'Meena', salary: 27000 } ]

BETWEEN

The BETWEEN checks if a certain field value falls between the given range. The following query string fetched employees whose salary lies between 27000 and 30000. In the above code, change the qry variable to the following expression, keep rest of the code same.

Example

var qry =`SELECT name,salary FROM employee WHERE salary BETWEEN 27000 and 30000;`;

Output

[
  RowDataPacket { name: 'Anil', salary: 30000 },
  RowDataPacket { name: 'Meena', salary: 27000 }
]

LIKE

The LIKE operator evaluates to TRUE if a value matches a specified pattern. To form a pattern, you use the % and _ wildcards. The % wildcard matches any string of zero or more characters while the _ wildcard matches any single character.

Change the query string variable to the following.

var qry =`SELECT name,salary FROM employee WHERE name LIKE '%n%';`;

The resultset will include names containing the alphabet 'n' anywhere in the name.

[
  RowDataPacket { name: 'Anil', salary: 30000 },
  RowDataPacket { name: 'Meena', salary: 27000 }
]

IN

MySQL also support IN operator along with WHERE clause. The IN operator returns TRUE if a value matches any value in a list.

value IN (value1, value2,...)

Change the query string in the code to following.

var qry =`SELECT name,salary FROM employee WHERE name IN ('Anil', 'Tina', 'Ravi');`;

This will return the resultset wherein the rows with names are found in the given list.

[
  RowDataPacket { name: 'Ravi', salary: 25000 },
  RowDataPacket { name: 'Anil', salary: 30000 }
]