MySQL - AND Operator

MySQL - The AND Operator

The AND operator is a fundamental logical operator in MySQL used to combine multiple conditions in a query. It allows you to filter data with a high degree of precision, ensuring that all specified criteria are met.

How the MySQL AND Operator Works

The AND operator combines two or more boolean expressions. An expression in MySQL is considered true if it evaluates to a non-zero, non-NULL number, and false if it evaluates to 0. The AND operator returns a result based on the following rules:

• It returns true (1) only if all expressions it connects are true.
• It returns false (0) if at least one of the expressions is false.
• It returns NULL if all expressions are not false, but at least one is NULL.

The following truth table summarizes the behavior of the AND operator:

A	B	A AND B
TRUE	TRUE	TRUE (1)
TRUE	FALSE	FALSE (0)
FALSE	TRUE	FALSE (0)
FALSE	FALSE	FALSE (0)
TRUE	NULL	NULL
FALSE	NULL	FALSE (0)
NULL	NULL	NULL

Example: Basic Logical Checks

You can test the AND operator directly with a SELECT statement.

-- Both are true, so the result is 1 (true)
SELECT 1 AND 1; -- Result: 1

-- One is false, so the result is 0 (false)
SELECT 1 AND 0; -- Result: 0

-- One is NULL, and the other is true, so the result is NULL
SELECT 1 AND NULL; -- Result: NULL

-- One is NULL, but the other is false, so the result is 0 (false)
SELECT 0 AND NULL; -- Result: 0

Using AND with the WHERE Clause

The most common use of the AND operator is within the WHERE clause of a SELECT statement to filter rows from a table. A row is only included in the result set if it satisfies all conditions connected by AND.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND ...;

Example: Filtering Data

First, let’s set up a CUSTOMERS table for our examples.

CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(20) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(25),
    SALARY DECIMAL(18, 2)
);

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

Now, let’s find all customers who are 25 years old AND live in ‘Mumbai’.

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE AGE = 25 AND ADDRESS = 'Mumbai';

Output

Only one record matches both conditions:

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00

Pro-Tip: Operator Precedence

In MySQL, AND has a higher precedence than OR. This means AND conditions are evaluated before OR conditions. To avoid ambiguity and ensure your logic is correct, it’s a best practice to use parentheses () when mixing AND and OR operators.

-- This finds customers in Mumbai who are 25, OR any customer with a salary > 8000
SELECT NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE (AGE = 25 AND ADDRESS = 'Mumbai') OR SALARY > 8000;

-- This is different! It finds customers who are 25, AND (are from Mumbai OR have a salary > 8000)
SELECT NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE AGE = 25 AND (ADDRESS = 'Mumbai' OR SALARY > 8000);

Using AND with UPDATE and DELETE Statements

The AND operator is also crucial for performing precise data modification, ensuring you only update or delete the exact records you intend to.

Example: Targeted UPDATE

Let’s give a salary increase to the customer named ‘Komal’ who lives in ‘Hyderabad’.

UPDATE CUSTOMERS
SET SALARY = 5000.00
WHERE NAME = 'Komal' AND ADDRESS = 'Hyderabad';

After running this query, only the record for Komal will be updated. This precision prevents accidentally updating other customers who might also be named ‘Komal’ but live elsewhere.

Example: Safe DELETE

Now, let’s remove the customer named ‘Khilan’ from ‘Delhi’.

DELETE FROM CUSTOMERS
WHERE NAME = 'Khilan' AND ADDRESS = 'Delhi';

Security Warning: Always use a WHERE clause with UPDATE and DELETE statements. Forgetting it will cause the operation to apply to all rows in the table. Using AND to create highly specific conditions is a key safety practice.

Using the AND Operator in Application Code

When building applications, you’ll frequently construct queries with multiple conditions. It is critically important to use prepared statements (or parameterized queries) to prevent SQL injection attacks. This method separates the SQL command from the data, treating user input as values rather than executable code.

Example Client Programs

The following examples show how to safely execute a SELECT query with an AND operator, using modern best practices and prepared statements. Remember to store credentials securely using environment variables.

Node.js
Python
Java
PHP



This example uses `mysql2/promise` and passes user-provided values as an array to the `execute` method.

```javascript
const mysql = require('mysql2/promise');
require('dotenv').config();

const pool = mysql.createPool({ /* ... connection config ... */ });

async function findCustomers(age, address) {
    let connection;
    try {
        connection = await pool.getConnection();
        const sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE = ? AND ADDRESS = ?;";

        // The '?' are placeholders. The array provides the values safely.
        const [rows] = await connection.execute(sql, [age, address]);

        if (rows.length > 0) {
            console.log(`Found ${rows.length} customer(s) matching criteria:`)
            console.table(rows);
        } else {
            console.log("No customers found matching the criteria.");
        }
    } catch (error) {
        console.error("Query failed:", error);
    } finally {
        if (connection) connection.release();
    }
}

// Example usage
findCustomers(25, 'Mumbai');

This Python example uses mysql-connector-python. The execute method takes a tuple of parameters to safely bind them to the placeholders in the query.

import mysql.connector
import os
from dotenv import load_dotenv

load_dotenv()

def find_customers(age, address):
    try:
        with mysql.connector.connect(
            host=os.getenv("DB_HOST"),
            user=os.getenv("DB_USER"),
            password=os.getenv("DB_PASSWORD"),
            database=os.getenv("DB_NAME")
        ) as connection:
            sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE = %s AND ADDRESS = %s"
            params = (age, address)

            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(sql, params)
                results = cursor.fetchall()

                if results:
                    print(f"Found {len(results)} customer(s):");
                    for row in results:
                        print(f"- ID: {row['ID']}, Name: {row['NAME']}, Age: {row['AGE']}")
                else:
                    print("No customers found.")

    except mysql.connector.Error as e:
        print(f"Database error: {e}")

# Example usage
find_customers(25, 'Mumbai')

This Java example uses a PreparedStatement, which is the standard way to execute parameterized queries in JDBC. The set... methods are used to bind values to the ? placeholders.

import java.sql.*;

public class FindCustomers {
    public static void find(int age, String address) {
        String url = "jdbc:mysql://" + System.getenv("DB_HOST") + "/" + System.getenv("DB_NAME");
        String user = System.getenv("DB_USER");
        String password = System.getenv("DB_PASSWORD");

        String sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE = ? AND ADDRESS = ?";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, age);       // Bind the first '?' to the age variable
            pstmt.setString(2, address); // Bind the second '?' to the address variable

            try (ResultSet rs = pstmt.executeQuery()) {
                if (!rs.isBeforeFirst()) {
                    System.out.println("No customers found.");
                    return;
                }
                while (rs.next()) {
                    System.out.printf("ID: %d, Name: %s, Age: %d\n",
                        rs.getInt("ID"),
                        rs.getString("NAME"),
                        rs.getInt("AGE"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        find(25, "Mumbai");
    }
}

This PHP example uses mysqli with prepared statements. Placeholders ? are used in the query, and bind_param is used to securely attach the variables.

<?php
require_once __DIR__ . '/vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($_ENV['DB_HOST'], $_ENV['DB_USER'], $_ENV['DB_PASSWORD'], $_ENV['DB_NAME']);

function findCustomers($mysqli, $age, $address) {
    $sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE = ? AND ADDRESS = ?";

    try {
        $stmt = $mysqli->prepare($sql);
        // 'is' means integer, string. This tells MySQL the data types of the parameters.
        $stmt->bind_param('is', $age, $address);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result->num_rows > 0) {
            echo "Found customer(s):\n";
            while ($row = $result->fetch_assoc()) {
                printf("- ID: %d, Name: %s, Age: %d\n", $row['ID'], $row['NAME'], $row['AGE']);
            }
        } else {
            echo "No customers found.\n";
        }
        $stmt->close();
    } catch (mysqli_sql_exception $e) {
        echo "Error: " . $e->getMessage() . "\n";
    }
}

// Example usage
findCustomers($mysqli, 25, 'Mumbai');
$mysqli->close();
?>