MySQL - OR Operator

MySQL - The OR Logical Operator

Understanding the OR Operator

The MySQL OR operator is a logical operator that combines two or more boolean conditions. It returns true (represented as 1 in MySQL) if at least one of the conditions is true. It only returns false (0) if all conditions are false.

• If A is true OR B is true, the result is true (1).
• If A is false OR B is false, the result is false (0).
• If a condition is NULL, the outcome depends on the other conditions. true OR NULL is true, while false OR NULL is NULL.

The following table demonstrates the behavior of the OR operator:

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

Using OR in the WHERE Clause

The most common use of the OR operator is within a WHERE clause to retrieve rows that match at least one of several conditions.

Example

First, let’s create a sample CUSTOMERS table.

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(100),
    SALARY DECIMAL(10, 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);

Now, let’s select customers who are either from ‘Delhi’ OR have a salary greater than 6000.

SELECT ID, NAME, ADDRESS, SALARY
FROM CUSTOMERS
WHERE ADDRESS = 'Delhi' OR SALARY > 6000;

Output

ID	NAME	ADDRESS	SALARY
2	Khilan	Delhi	1500.00
4	Chaitali	Mumbai	6500.00
5	Hardik	Bhopal	8500.00

Common Pitfall: OR vs. AND Precedence

A frequent source of bugs is forgetting that the AND operator has higher precedence than OR. This means AND conditions are evaluated before OR conditions. Always use parentheses () to explicitly group your conditions and ensure your logic is evaluated as intended.

Example

Suppose you want to find customers who are (from Mumbai OR Bhopal) AND have a salary over 7000.

-- INCORRECT LOGIC without parentheses
-- This finds (customers from Mumbai) OR (customers from Bhopal with salary > 7000)
SELECT NAME, ADDRESS, SALARY
FROM CUSTOMERS
WHERE ADDRESS = 'Mumbai' OR ADDRESS = 'Bhopal' AND SALARY > 7000;

-- CORRECT LOGIC with parentheses
SELECT NAME, ADDRESS, SALARY
FROM CUSTOMERS
WHERE (ADDRESS = 'Mumbai' OR ADDRESS = 'Bhopal') AND SALARY > 7000;

The correct query correctly identifies only Hardik, whereas the incorrect one would also include Chaitali from Mumbai, regardless of her salary.

Using OR in UPDATE and DELETE Statements

The OR operator works just as effectively in UPDATE and DELETE statements to target specific rows.

UPDATE Example

Give a bonus to all customers who are under 25 OR from Hyderabad.

UPDATE CUSTOMERS
SET SALARY = SALARY + 500
WHERE AGE < 25 OR ADDRESS = 'Hyderabad';
-- This affects Kaushik (age 23) and Komal (from Hyderabad).

DELETE Example

Remove customers who are older than 30 OR have a salary less than 2000.

DELETE FROM CUSTOMERS
WHERE AGE > 30 OR SALARY < 2000;
-- This affects Ramesh (age 32) and Khilan (salary 1500).

OR Operator Using a Client Program

When building queries with OR in application code, always use prepared statements to pass parameters securely.

PHP
NodeJS
Java
Python



This example uses `mysqli` with prepared statements to find customers matching one of two cities.

```php
<?php
$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $dbname = 'TUTORIALS';
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

$city1 = 'Delhi';
$city2 = 'Mumbai';
$sql = 'SELECT NAME, ADDRESS FROM CUSTOMERS WHERE ADDRESS = ? OR ADDRESS = ?';

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('ss', $city1, $city2);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    printf("Name: %s, Address: %s\n", $row['NAME'], $row['ADDRESS']);
}
$stmt->close();
$mysqli->close();
?>

This mysql2/promise example finds customers younger than a certain age or with a salary above a threshold.

const mysql = require('mysql2/promise');

async function findCustomers(maxAge, minSalary) {
    let connection;
    try {
        connection = await mysql.createConnection({ host: 'localhost', user: 'root', password: 'password', database: 'TUTORIALS' });
        const sql = 'SELECT NAME, AGE, SALARY FROM CUSTOMERS WHERE AGE < ? OR SALARY > ?';
        const [rows] = await connection.execute(sql, [maxAge, minSalary]);
        console.log('Found customers:', rows);
    } catch (error) {
        console.error('Query failed:', error);
    } finally {
        if (connection) await connection.end();
    }
}

findCustomers(25, 6000);

This modern JDBC example uses a PreparedStatement to securely insert parameters into an OR query.

import java.sql.*;

public class OrOperatorExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        String sql = "SELECT NAME, AGE, SALARY FROM CUSTOMERS WHERE AGE < ? OR SALARY > ?";

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

            pstmt.setInt(1, 25); // age < 25
            pstmt.setBigDecimal(2, new java.math.BigDecimal("6000.00")); // salary > 6000

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.printf("Name: %s, Age: %d, Salary: %.2f%n",
                    rs.getString("NAME"), rs.getInt("AGE"), rs.getBigDecimal("SALARY"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This Python example uses mysql-connector-python and passes parameters as a tuple to execute().

import mysql.connector

def find_customers(max_age, min_salary):
    config = {'user': 'root', 'password': 'password', 'host': 'localhost', 'database': 'TUTORIALS'}
    query = "SELECT NAME, AGE, SALARY FROM CUSTOMERS WHERE AGE < %s OR SALARY > %s"

    try:
        with mysql.connector.connect(**config) as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute(query, (max_age, min_salary))
                for row in cursor.fetchall():
                    print(row)
    except mysql.connector.Error as err:
        print(f"Error: {err}")

find_customers(25, 6000)