跳至主要内容

MySQL OR Keyword

The `OR` keyword in MySQL is used to combine multiple conditions in SQL statements, allowing for more complex queries. It returns true if any of the specified conditions are true.

Usage

The `OR` keyword is typically used in a `WHERE` clause to filter records based on multiple conditions. It is useful when you want to retrieve rows that meet at least one of several criteria.

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

In this syntax, `OR` connects `condition1` and `condition2`, returning rows where either condition is true.

Examples

1. Basic OR Condition

sql
SELECT *
FROM products
WHERE category = 'Electronics' OR category = 'Appliances';

This query retrieves all rows from the `products` table where the category is either 'Electronics' or 'Appliances'.

2. Combining AND and OR

sql
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' AND (city = 'New York' OR city = 'Los Angeles');

Here, the query selects employees from the 'Sales' department who are located in either 'New York' or 'Los Angeles'.

3. Using OR with Multiple Conditions

sql
SELECT order_id, order_date
FROM orders
WHERE status = 'Pending' OR status = 'Processing' OR status = 'Shipped';

This example fetches orders with a status of 'Pending', 'Processing', or 'Shipped', demonstrating the use of `OR` with multiple conditions.

Tips and Best Practices

  • Use parentheses for clarity. When combining `OR` with `AND`, use parentheses to explicitly define the order of evaluation.
  • Optimize conditions order. Place the most restrictive conditions first to enhance performance. Note that `OR` can affect query performance, especially if not used with indexed columns.
  • Boolean Logic and Data Types. `OR` in MySQL operates with boolean logic, returning `TRUE` if at least one condition is `TRUE`. Be mindful of how `OR` interacts with `TRUE`, `FALSE`, and `NULL` values.
  • Handling NULLs. Ensure that your conditions account for possible `NULL` values, as they can affect the truthiness of your conditions. For example: 
    sql
SELECT id
FROM users
WHERE age > 30 OR age IS NULL;
    This retrieves users who are either older than 30 or have an unspecified age.
  • Avoid excessive use. Overusing `OR` can lead to complex queries that are hard to read and maintain; consider using other strategies like `IN` for better clarity. For example: 
    sql
SELECT order_id
FROM orders
WHERE status IN ('Pending', 'Processing', 'Shipped');
    This query simplifies the use of multiple `OR` conditions.
  • Indexes and Performance. To mitigate potential slowdowns, ensure that columns used in `OR` conditions are properly indexed.