MySQL - OR Operator

MySQL OR Operator

MySQL does not have a built-in Boolean data type. Instead, Boolean values are represented using numeric data types, where zero is used as false and any non-zero value is used as true.

The MySQL OR operator is a logical operator that combines two or more Boolean expressions and returns 1, 0, or NULL:

A AND B

Here, A and B are operands.

• The OR operator will return true (1) only if either A or B, or both, is non-zero and not Null.

• If both A and B are false, the OR operator will return false (0).

• If either A or B is NULL, the OR operator will return NULL.

The following table below demonstrates the possible outcomes of using the OR operator to combine true (1), false (0), and null values:

Example

The logical OR operator will return true (1) if both A and B are not NULL, and if either A or B is non-zero.

SELECT 1 OR 1, 1 OR 0, 0 OR 1;

Output

The output for the program above is produced as given below −

Example

The OR operator returns false (0) if both A and B are false (0).

SELECT 0 OR 0;

Output

When we execute the above query, the output is obtained as follows −

Example

If A is true (1) and B is NULL, the OR operator will return 1.

If A is false (0) and B is NULL, the OR operator will return NULL.

If both A and B are NULL, the OR operator will return NULL.

SELECT 1 OR NULL, 0 OR NULL, NULL or NULL;

Output

On executing the given program, the output is displayed as follows −

OR operator with WHERE

MySQL's logical OR operator can be used along with the WHERE clause to return the rows that meet any of the specified conditions.

When the OR operator is used, at least one of the conditions must be true for a row to be included in the result set. If none of the conditions are true, an empty set is returned.

Syntax

Following is the syntax of the OR operator with WHERE clause in MySQL −

SELECT column1, column2, ..., columnN
FROM table_name
[WHERE condition1 OR condition2 OR condition3 ...;]

Example

Firstly, let us create a table named CUSTOMERS using the following query −

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

The following INSERT INTO statement adds 7 records into the above-created table −

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

Execute the following query to retrieve all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

Now, we are selecting all the columns from the CUSTOMERS table where SALARY is greater than 5000 or ADDRESS = "Hyderabad".

SELECT * FROM CUSTOMERS
WHERE SALARY > 5000
OR ADDRESS = "Hyderabad";

Output

The output for the program above is produced as given below −

Example

The logical OR operator returns the records only if either of the conditions separated by OR is true.

In the following query, we are providing false values to both operands of the OR operator.

SELECT * FROM CUSTOMERS
WHERE NAME = "Mahesh" OR AGE = 42;

Output

As there are no records present in the CUSTOMERS table with NAME "Mahesh" or AGE is 42, it returns an empty set as an output.

Empty set (0.00 sec)

Multiple OR Operators

We can use MySQL's logical OR operator multiple times to combine multiple conditions. By using multiple OR operators, any rows that meet at least one of the conditions will be included in the result set.

Example

In the following query, we are returning all the records from the CUSTOMERS table where the NAME of the customer ends with 'k', or SALARY is greater than 5000, or AGE is less than 25.

SELECT * FROM CUSTOMERS
WHERE NAME LIKE '%k' OR SALARY > 5000 OR AGE < 25;

Output

On executing the given query, the output is displayed as follows −

OR with UPDATE statement

The MySQL's logical OR operator can be used along with the UPDATE statement to update records of a table based on multiple conditions.

Syntax

Following is the syntax of the OR operator with the UPDATE statement in MySQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition1 OR condition2 OR ...;

Example

In the following query, we are updating the SALARY of CUSTOMERS whose ADDRESS is 'Hyderabad' or whose age is greater than 26

UPDATE CUSTOMERS
SET SALARY = 15000
WHERE ADDRESS = "Hyderabad" OR AGE > 26;

Output

The output for the query above is produced as given below −

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Verification

Execute the below query to verify whether the SALARY of CUSTOMERS is updated or not −

SELECT * FROM CUSTOMERS;

Output

The output for the program above is produced as given below −

OR with DELETE Statement

The MySQL's logical OR operator can be used along with the DELETE statement to remove records from a table based on multiple conditions.

Syntax

Following is the syntax of OR operator with the DELETE statement in MySQL −

DELETE FROM table_name
WHERE condition1 OR condition2 OR condition3 ...

Example

In the following query, we are trying to DELETE records from the CUSTOMERS table where the age is less than 25 or the SALARY is less than or equal to 10000.

DELETE FROM CUSTOMERS
WHERE AGE < 25 OR SALARY <= 10000;

Output

Query OK, 5 rows affected (0.01 sec)

Verification

Execute the following query to verify whether the above operation is successful or not −

OR Operator Using a Client Program

Besides using MySQL queries to perform the OR operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME 
WHERE CONDITION1 OR CONDITION2 OR CONDITION3...";
$mysqli->query($sql);

sql= " SELECT column1, column2, ..., columnN FROM table_name 3 
[WHERE condition1 OR condition2 OR condition3 ...]";   
con.query(sql);

String sql = "SELECT COLUMN1, COLUMN2, ... FROM TABLE_NAME 
[WHERE CONDITION1 OR CONDITION2 OR CONDITION3...]";  
statement.executeQuery(sql);

or_query = "SELECT column1, column2, ... FROM table_name 
WHERE condition1 OR condition2 OR ..."
cursorObj.execute(or_query);

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.
');
$sql = 'SELECT  * FROM tutorials_tbl WHERE tutorial_id = 2 OR tutorial_id = 4 OR tutorial_id = 6';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Title: %s, Author: %s, S_date %s",   
      $row["tutorial_id"],
      $row["tutorial_title"],
      $row["tutorial_author"],
      $row["submission_date"]);
      printf("\n");         
   }
} else {
   printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

Table records:
Id 2, Title: PHP Tut, Author: New Author, S_date 2023-08-12
Id 4, Title: Learn PHP, Author: John Poul, S_date 2023-07-26
Id 6, Title: Learn MySQL, Author: Mahesh, S_date 2023-07-26         

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,'Ramesh', 32, 'Hyderabad', 2000.00),(2,'Khilan', 25, 'Delhi', 1500.00),(3,'kaushik', 23, 'Hyderabad', 2000.00),(4,'Chaital', 25, 'Mumbai', 6500.00),(5,'Hardik', 27, 'Vishakapatnam', 8500.00),(6, 'Komal',22, 'Vishakapatnam', 4500.00),(7, 'Muffy',24, 'Indore', 10000.00);"
  con.query(sql);

  //Using OR Operator
  sql = "SELECT * FROM CUSTOMERS WHERE SALARY > 5000 OR ADDRESS = 'Vishakapatnam';"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});           

Connected!
--------------------------
[
  {
    ID: 4,
    NAME: 'Chaital',
    AGE: 25,
    ADDRESS: 'Mumbai',
    SALARY: '6500.00'
  },
  {
    ID: 5,
    NAME: 'Hardik',
    AGE: 27,
    ADDRESS: 'Vishakapatnam',
    SALARY: '8500.00'
  },
  {
    ID: 6,
    NAME: 'Komal',
    AGE: 22,
    ADDRESS: 'Vishakapatnam',
    SALARY: '4500.00'
  },
  {
    ID: 7,
    NAME: 'Muffy',
    AGE: 24,
    ADDRESS: 'Indore',
    SALARY: '10000.00'
  }
]      

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OrOperator {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM CUSTOMERS WHERE SALARY > 5000 OR ADDRESS = \"Vishakapatnam\"";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()){
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id +", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                         

Table records: 
Id: 4, Name: Chaital, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00      

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object 
cursorObj = connection.cursor()
# Query to retrieve tutorials
or_query = f"""SELECT * FROM CUSTOMERS
WHERE SALARY > 5000 OR ADDRESS = "Vishakapatnam""""
cursorObj.execute(or_query)
# Fetch all rows that meet the criteria
filtered_rows = cursorObj.fetchall()
# Print the filtered rows
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                                      

(2, 'Khilan', 25, 'Kerala', Decimal('8000.00'))
(3, 'kaushik', 23, 'Hyderabad', Decimal('11000.00'))
(5, 'Hardik', 27, 'Vishakapatnam', Decimal('10000.00'))
(6, 'Komal', 29, 'Vishakapatnam', Decimal('7000.00'))
(7, 'Muffy', 24, 'Delhi', Decimal('10000.00'))