Home / SQL / AND, OR, NOT

AND, OR, NOT

Overview

The AND, OR, and NOT operators are logical operators used in WHERE clauses to combine or negate conditions. They allow you to create complex filtering criteria.

These operators are essential for building sophisticated queries that can handle multiple conditions simultaneously.

AND Operator

The AND operator displays records if all conditions separated by AND are TRUE. All conditions must be met for a row to be included in the result.

AND Syntax

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

Example 1: Multiple AND Conditions

Find products in specific category with price range

SELECT product_name, price, category
FROM Products
WHERE category = 'Electronics' 
  AND price > 100 
  AND price < 500;

Example 2: AND with Different Operators

Combine multiple conditions

SELECT *
FROM Customers
WHERE age >= 18 
  AND city = 'New York' 
  AND status = 'Active';

AND Logic Truth Table

Condition1  Condition2  Result
──────────────────────────────
TRUE        TRUE        TRUE
TRUE        FALSE       FALSE
FALSE       TRUE        FALSE
FALSE       FALSE       FALSE

OR Operator

The OR operator displays records if any of the conditions separated by OR is TRUE. At least one condition must be met.

OR Syntax

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

Example 1: Multiple OR Conditions

Find customers in multiple cities

SELECT name, city
FROM Customers
WHERE city = 'New York' 
   OR city = 'Los Angeles' 
   OR city = 'Chicago';

Example 2: OR with Different Values

Match any of several conditions

SELECT *
FROM Products
WHERE category = 'Electronics' 
   OR category = 'Computers' 
   OR price > 1000;

OR Logic Truth Table

Condition1  Condition2  Result
──────────────────────────────
TRUE        TRUE        TRUE
TRUE        FALSE       TRUE
FALSE       TRUE        TRUE
FALSE       FALSE       FALSE

NOT Operator

The NOT operator displays records if the condition(s) is NOT TRUE. It negates or reverses the condition.

NOT Syntax

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

Example 1: NOT with Equality

Exclude specific values

SELECT *
FROM Customers
WHERE NOT city = 'New York';

Example 2: NOT with Comparison

Exclude records above threshold

SELECT *
FROM Products
WHERE NOT price > 1000;

Example 3: NOT with LIKE

Exclude pattern matches

SELECT *
FROM Customers
WHERE NOT name LIKE 'John%';

NOT Logic Truth Table

Condition   NOT Condition
─────────────────────────
TRUE        FALSE
FALSE       TRUE

Combining AND, OR, and NOT

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

Example 1: AND with OR

Complex conditions with parentheses

SELECT *
FROM Products
WHERE category = 'Electronics' 
  AND (price < 100 OR price > 500);

This finds Electronics products that are either below $100 OR above $500.

Example 2: Multiple Combinations

Complex filtering

SELECT *
FROM Customers
WHERE (city = 'New York' OR city = 'Los Angeles')
  AND age >= 25
  AND NOT status = 'Inactive';

Operator Precedence

When combining operators, SQL evaluates them in this order:

Parentheses ()
NOT
AND
OR

Important: Always use parentheses to make your intentions clear, even when not strictly necessary.

Precedence Example

-- Without parentheses (ambiguous)
SELECT * FROM Products
WHERE category = 'Electronics' OR category = 'Computers' AND price > 100;

-- With parentheses (clear intent)
SELECT * FROM Products
WHERE (category = 'Electronics' OR category = 'Computers') AND price > 100;

-- Different meaning with different parentheses
SELECT * FROM Products
WHERE category = 'Electronics' OR (category = 'Computers' AND price > 100);

Common Patterns

Pattern 1: Range Exclusions

Exclude a range

SELECT *
FROM Products
WHERE NOT (price >= 50 AND price <= 100);

Pattern 2: Multiple Value Matching

Match multiple values (alternative to IN)

SELECT *
FROM Customers
WHERE city = 'New York' 
   OR city = 'Los Angeles' 
   OR city = 'Chicago'
   OR city = 'Houston';

Pattern 3: Exclusion with Inclusion

Include some, exclude others

SELECT *
FROM Products
WHERE category IN ('Electronics', 'Computers')
  AND NOT discontinued = 1;

Best Practices

Use parentheses: Always use parentheses to clarify complex conditions
Be explicit: Make your logic clear, even if it adds extra parentheses
Test conditions: Test each condition separately before combining
Use IN instead of multiple ORs: When checking multiple values, IN is cleaner
Consider readability: Format complex conditions across multiple lines

Common Mistakes

Missing parentheses: Causing unexpected evaluation order
Using AND when OR is needed: Double-check logic requirements
Confusing NOT with !=: NOT reverses the entire condition
Not testing edge cases: Test with various data combinations

Next Steps

Learn about inserting data with INSERT INTO, or explore other filtering options like IN and BETWEEN.

← Order By Insert Into →