MySQL WHERE Clause

The WHERE clause in MySQL is used to filter records based on specific conditions. It allows you to fetch, update, or delete only the rows that meet the given criteria.

Why Use the WHERE Clause in MySQL?

The WHERE clause helps you:

1. Filter Records: Retrieve only the rows that match specific conditions.
2. Update Selected Rows: Modify only those rows that meet certain criteria.
3. Delete Specific Rows: Remove rows without affecting the entire table.
4. Optimize Queries: Minimize the amount of data fetched, improving performance.

Prerequisites

MySQL Installed: Ensure MySQL is installed and running.

Node.js Installed: Install Node.js if you haven’t already.

MySQL Package Installed: Install the MySQL module using:

npm install mysql

Setting Up the Node.js Project

Initialize the Project:

npm init -y

Install the MySQL Package:

npm install mysql

Create a File: Create a file named app.js.

Connecting to the MySQL Database

Before using the WHERE clause, you need to establish a connection to the database.

Example: Connect to MySQL

const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs_demo', // Replace with your database name
});

// Connect to the database
connection.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL:', err);
        return;
    }
    console.log('Connected to MySQL successfully!');
});

Using the WHERE Clause in Node.js

The WHERE clause is used in various SQL operations like SELECT, UPDATE and DELETE. Let’s explore each operation with examples.

Example 1: Fetch Data with WHERE Clause

Retrieve records that meet specific criteria.

const sql = 'SELECT * FROM users WHERE age > ?';
const age = 25;

connection.query(sql, [age], (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Users older than 25:', results);
});

// Close the connection
connection.end();

Explanation:

• age > ?: The placeholder ? is replaced with the value 25.
• Parameterized Query: Prevents SQL injection by securely passing the variable.

Example 2: Fetch Data with Multiple Conditions

You can use logical operators like AND and OR to apply multiple conditions.

const sql = 'SELECT * FROM users WHERE age > ? AND name = ?';
const values = [30, 'John Doe'];

connection.query(sql, values, (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Filtered results:', results);
});

// Close the connection
connection.end();

Explanation:

• AND Operator: Combines conditions; both must be true for the row to match.

Updating Records with WHERE Clause

The WHERE clause ensures that only the specified rows are updated.

Example 3: Update Data with WHERE Clause

const sql = 'UPDATE users SET age = ? WHERE id = ?';
const values = [35, 1]; // Set age to 35 where id is 1

connection.query(sql, values, (err, result) => {
    if (err) {
        console.error('Error updating data:', err);
        return;
    }
    console.log('Rows updated:', result.affectedRows);
});

// Close the connection
connection.end();

Explanation:

• SET age = ?: Updates the age column for the specified user ID.
• affectedRows: Indicates the number of rows that were updated.

Deleting Records with WHERE Clause

The WHERE clause is critical when deleting records to avoid accidental data loss.

Example 4: Delete Data with WHERE Clause

const sql = 'DELETE FROM users WHERE id = ?';
const id = 2;

connection.query(sql, [id], (err, result) => {
    if (err) {
        console.error('Error deleting data:', err);
        return;
    }
    console.log('Rows deleted:', result.affectedRows);
});

// Close the connection
connection.end();

Explanation:

• WHERE id = ?: Deletes only the row with the specified ID.
• Always use a WHERE clause in DELETE queries to avoid deleting all records accidentally.

Using LIKE for Pattern Matching

The LIKE operator is used to search for patterns in text columns.

Example 5: Fetch Data with LIKE

const sql = 'SELECT * FROM users WHERE name LIKE ?';
const namePattern = '%John%'; // Matches names containing "John"

connection.query(sql, [namePattern], (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Users matching pattern:', results);
});

// Close the connection
connection.end();

Explanation:

• %John%: Matches any string containing “John”.
• LIKE: Useful for flexible pattern matching.

Sorting and Filtering Together

You can combine WHERE with ORDER BY to filter and sort data.

Example 6: Fetch and Sort Data

const sql = 'SELECT * FROM users WHERE age > ? ORDER BY name ASC';
const age = 20;

connection.query(sql, [age], (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Filtered and sorted users:', results);
});

// Close the connection
connection.end();

Real-World Use Case

Imagine building a search functionality for a web application where users can filter results based on age, name or other criteria.

Example: Dynamic User Search

const searchUsers = (name, minAge) => {
    const sql = 'SELECT * FROM users WHERE name LIKE ? AND age > ?';
    const values = [`%${name}%`, minAge];

    connection.query(sql, values, (err, results) => {
        if (err) {
            console.error('Error searching users:', err);
            return;
        }
        console.log('Search results:', results);
    });
};

// Example usage
searchUsers('John', 25);