MySQL AND, OR, NOT Operators

The AND, OR, and NOT operators are used to combine multiple conditions in the WHERE clause, allowing you to create complex logical expressions for data filtering.

AND Operator

The AND operator displays a record if allconditions separated by AND are TRUE.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Example

-- Employees in Sales department with salary > 50000
SELECT * FROM employees 
WHERE department = 'Sales' 
  AND salary > 50000;

Multiple AND Conditions

-- Complex example with multiple conditions
SELECT 
    name,
    department,
    salary,
    hire_date
FROM employees
WHERE department = 'Engineering'
  AND salary >= 60000
  AND hire_date >= '2023-01-01'
  AND active = 1
ORDER BY salary DESC;

OR Operator

The OR operator displays a record if anyof the conditions separated by OR is TRUE.

Syntax

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

Example

-- Employees in either Sales or Marketing department
SELECT * FROM employees 
WHERE department = 'Sales' 
   OR department = 'Marketing';

Multiple OR Conditions

SELECT * FROM products
WHERE category = 'Electronics'
   OR category = 'Computers'
   OR category = 'Gadgets'
   OR category = 'Mobile Phones';

NOT Operator

The NOT operator displays a record if the condition(s) is NOT TRUE.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Examples

-- Employees NOT in Sales department
SELECT * FROM employees 
WHERE NOT department = 'Sales';

-- Products that are NOT discontinued
SELECT * FROM products 
WHERE NOT discontinued = 1;

-- Alternative syntax (more readable)
SELECT * FROM employees 
WHERE department <> 'Sales';

SELECT * FROM products 
WHERE discontinued != 1;

Combining AND, OR, NOT

You can combine these operators to create complex logical conditions. Use parentheses to control the order of evaluation.

Without Parentheses (Default precedence: NOT > AND > OR)

SELECT * FROM employees
WHERE department = 'Sales' 
   OR department = 'Marketing' 
  AND salary > 50000;
-- This evaluates as: Sales OR (Marketing AND salary > 50000)

With Parentheses (Explicit precedence)

SELECT * FROM employees
WHERE (department = 'Sales' OR department = 'Marketing') 
  AND salary > 50000;
-- This evaluates as: (Sales OR Marketing) AND salary > 50000

Complex Examples

-- Find employees meeting complex criteria
SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    department,
    salary,
    hire_date,
    CASE 
        WHEN salary > 80000 THEN 'High Earner'
        WHEN salary BETWEEN 50000 AND 80000 THEN 'Mid Earner'
        ELSE 'Entry Level'
    END AS salary_category
FROM employees
WHERE (
    (department = 'Engineering' AND salary >= 70000)
    OR 
    (department = 'Sales' AND salary >= 60000)
    OR
    (department = 'Marketing' AND salary >= 55000)
)
AND NOT employment_status = 'Terminated'
AND hire_date >= '2022-01-01'
AND (bonus_eligible = 1 OR commission_rate > 0)
ORDER BY department, salary DESC;

-- Advanced product search with multiple conditions
SELECT 
    product_name,
    category,
    price,
    stock_quantity,
    supplier_id,
    CASE 
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        WHEN stock_quantity < 50 THEN 'Medium Stock'
        ELSE 'In Stock'
    END AS inventory_status,
    CASE 
        WHEN price < 20 THEN 'Budget'
        WHEN price BETWEEN 20 AND 100 THEN 'Standard'
        ELSE 'Premium'
    END AS price_tier
FROM products
WHERE (
    (category = 'Electronics' AND price BETWEEN 50 AND 500)
    OR
    (category = 'Home Appliances' AND price < 300)
    OR
    (category = 'Books' AND price < 50)
)
AND NOT discontinued = 1
AND (stock_quantity > 0 OR backorder_allowed = 1)
AND (
    rating >= 4.0 
    OR 
    (review_count >= 100 AND rating >= 3.5)
)
AND (
    warranty_months >= 12 
    OR 
    (supplier_id IN (SELECT supplier_id FROM suppliers WHERE rating = 'A'))
)
ORDER BY 
    CASE 
        WHEN stock_quantity < 10 THEN 0 
        ELSE 1 
    END,
    price ASC;

Truth Tables

Best Practices

• Always use parentheses when combining AND and OR for clarity
• Use NOT judiciously - sometimes != or <> is more readable
• Consider using IN() instead of multiple OR conditions
• Evaluate conditions from most restrictive to least for performance