SQL OR Operator – Syntax, Examples

SQL OR Operator

The SQL OR operator is used to filter records based on multiple conditions, where at least one condition must be true for the row to be included in the result set.

The OR operator is commonly used in the WHERE clause to broaden the criteria, retrieving records that match any of the specified conditions.

In this tutorial, we will go through OR Operator in SQL, its syntax, and how to use this operator in forming conditions in SQL statements, with well detailed examples.

Syntax of SQL OR Operator

The basic syntax of the SQL OR operator in a WHERE clause is as follows:

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

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to retrieve from the table.
• FROM: Specifies the table from which to retrieve data.
• WHERE: Introduces the conditions used to filter the data.
• OR: Combines multiple conditions. Each condition separated by OR can be true for a row to be included in the results.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the OR operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a table named students:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

---

Examples: Using OR Operator in Queries

Now, let’s explore different scenarios of using the OR operator with this table.

---

1. Selecting Rows with Two Conditions

To select students who are either in the 9th grade or live in “Northside”:

SELECT * FROM students
WHERE grade = '9th' OR locality = 'Northside';

This query returns rows where either condition is true, displaying records for Alice, Bob, Charlie, and Eva.

---

2. Using OR with Numeric Comparison

To select students who are either 14 years old or in the 10th grade:

SELECT * FROM students
WHERE age = 14 OR grade = '10th';

This query returns students who are either 14 years old or in the 10th grade, displaying records for Alice, Charlie, and David.

---

3. Using OR with Multiple Conditions

To select students who are in the 8th grade, 9th grade, or live in “Southend”:

SELECT * FROM students
WHERE grade = '8th' OR grade = '9th' OR locality = 'Southend';

This query includes any student who meets at least one of these conditions, returning records for Alice, Bob, Charlie, David, and Eva.

---

4. Combining OR with AND for Complex Queries

To select students who are in the 9th grade or in the 8th grade but live in “Northside”:

SELECT * FROM students
WHERE grade = '9th' OR (grade = '8th' AND locality = 'Northside');

This query returns students in the 9th grade and also includes students in the 8th grade who live in Northside. The results include records for Alice, Bob, Charlie, and Eva.

---

5. Using OR with Different Data Types

To select students who are named “Alice” or are 15 years old:

SELECT * FROM students
WHERE name = 'Alice' OR age = 15;

This query retrieves records where either the name is “Alice” or the age is 15. It returns records for Alice, Bob, and Eva.

---

FAQs for SQL OR

1. What does the SQL OR operator do?

The OR operator allows you to combine multiple conditions in a SQL query, where at least one condition must be true for a row to be included in the result set.

2. Can I use OR with other operators in SQL?

Yes, you can use OR with other operators like AND, NOT, and comparison operators to create complex conditions in a query.

3. How many conditions can I combine with OR?

There’s no strict limit to the number of conditions you can combine with OR, but too many conditions may make the query harder to read and understand.

4. What happens if all conditions in an OR statement are false?

If all conditions in an OR expression are false, the row is excluded from the result set, as no conditions are met.

5. Can OR be used with different data types in conditions?

Yes, you can use OR with conditions involving different data types, such as numbers, strings, and dates, as long as each condition evaluates to true or false.

Related

SQL AND Operator