SQL OR Operator

SQL OR Operator

The SQL OR operator is used to combine multiple conditions in a SQL query's WHERE clause. This command is essential for retrieving rows that meet at least one of the specified criteria, allowing for flexible and broad data filtering.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR ...;
  • SELECT: This is the SQL keyword used to specify the columns to retrieve.
  • column1, column2, ...: These specify the names of the columns to retrieve.
  • FROM: This is the SQL keyword used to specify the table.
  • table_name: This specifies the name of the table from which to retrieve the rows.
  • WHERE: This is the SQL keyword used to specify the conditions for filtering the rows.
  • condition1, condition2, ...: These specify the conditions that the rows must meet to be retrieved.

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the OR operator to filter specific rows based on multiple conditions.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Bob', 'Brown', 'bob.brown@example.com', '2023-04-01');

Here, we insert four rows of data into the employees table.

Step 4: Using the OR Operator

This step involves retrieving rows from the employees table where the last_name is 'Doe' or the hire_date is '2023-02-01'.

SELECT * FROM employees
WHERE last_name = 'Doe' OR hire_date = '2023-02-01';

This command will return the rows from the employees table where the last_name is 'Doe' or the hire_date is '2023-02-01'.

You can also use the OR operator with other conditions and comparison operators, such as:

  • = (equal to)
  • != or <> (not equal to)
  • > (greater than)
  • < (less than)
  • >= (greater than or equal to)
  • <= (less than or equal to)
  • LIKE (pattern matching)
  • IN (matching any value in a list)
  • BETWEEN (within a range of values)

For example, to retrieve rows where the last_name is 'Doe' or the email contains 'example.com':

SELECT * FROM employees
WHERE last_name = 'Doe' OR email LIKE '%example.com';

This command will return the rows from the employees table where the last_name is 'Doe' or the email contains 'example.com'.