SQL AND, OR, NOT

Learn how to combine multiple conditions in SQL using the AND, OR, and NOT logical operators.

What are AND, OR, and NOT?

SQL provides the AND, OR, and NOT operators to combine or reverse conditions. These operators are commonly used with the WHERE clause to filter database records.

AND requires all conditions to be true, OR requires at least one condition to be true, while NOT reverses the result of a condition.

Basic AND Syntax

The AND operator returns records only when all specified conditions are true.

WITH products(name, category, price) AS (
  VALUES
    ('Camera', 'Electronics', 45000),
    ('Tripod', 'Accessories', 5000),
    ('Laptop', 'Electronics', 65000),
    ('Headphones', 'Electronics', 8000)
)
SELECT name, price
FROM products
WHERE category = 'Electronics'
  AND price > 10000;

AND with Multiple Conditions

You can use more than two conditions with AND. Every condition must be satisfied for a row to be returned.

WITH students(name, marks, attendance, grade) AS (
  VALUES
    ('Aarav', 88, 92, 'A'),
    ('Meera', 76, 95, 'B'),
    ('Rohan', 91, 84, 'A'),
    ('Diya', 82, 78, 'B'),
    ('Kabir', 95, 97, 'A')
)
SELECT name, marks, attendance
FROM students
WHERE marks >= 80
  AND attendance >= 90
  AND grade = 'A';

Basic OR Syntax

The OR operator returns a record when at least one of the specified conditions is true.

WITH employees(name, department) AS (
  VALUES
    ('Neha', 'Design'),
    ('Arjun', 'Sales'),
    ('Vikram', 'Finance'),
    ('Sara', 'Marketing'),
    ('Ira', 'Sales')
)
SELECT name, department
FROM employees
WHERE department = 'Sales'
   OR department = 'Design';

OR with Numbers

OR can also be used to compare numerical values.

WITH books(title, pages) AS (
  VALUES
    ('Blue Planet', 220),
    ('Ancient Roads', 410),
    ('Future City', 350),
    ('Ocean Life', 180),
    ('Hidden Forest', 500)
)
SELECT title, pages
FROM books
WHERE pages < 200
   OR pages > 400;

Basic NOT Syntax

The NOT operator reverses the result of a condition.

WITH devices(name, status) AS (
  VALUES
    ('Router A', 'Online'),
    ('Router B', 'Offline'),
    ('Router C', 'Online'),
    ('Router D', 'Maintenance')
)
SELECT name, status
FROM devices
WHERE NOT status = 'Offline';

NOT with IN

NOT IN is useful when you want to exclude several specific values from the results.

WITH cities(name, zone) AS (
  VALUES
    ('Jaipur', 'North'),
    ('Pune', 'West'),
    ('Kochi', 'South'),
    ('Patna', 'East'),
    ('Surat', 'West')
)
SELECT name, zone
FROM cities
WHERE zone NOT IN ('West', 'East');

AND with OR

AND and OR can be combined to create more specific filtering conditions. Parentheses are recommended to clearly define the intended logic.

WITH courses(course, category, fee) AS (
  VALUES
    ('Robotics', 'Technology', 12000),
    ('Painting', 'Arts', 7000),
    ('Web Design', 'Technology', 9000),
    ('Music', 'Arts', 14000),
    ('Astronomy', 'Science', 11000)
)
SELECT course, category, fee
FROM courses
WHERE (category = 'Technology' AND fee < 10000)
   OR category = 'Arts';

Using Parentheses

Parentheses control the order in which conditions are evaluated and make complex expressions easier to understand.

WITH movies(title, genre, rating) AS (
  VALUES
    ('Sky World', 'Sci-Fi', 8.5),
    ('Last Road', 'Drama', 7.2),
    ('Deep Space', 'Sci-Fi', 7.8),
    ('Silent River', 'Drama', 8.7),
    ('Night Chase', 'Action', 8.1)
)
SELECT title, genre, rating
FROM movies
WHERE (genre = 'Sci-Fi' OR genre = 'Action')
  AND rating >= 8.0;

Combining AND, OR, and NOT

All three logical operators can be combined when a query requires multiple levels of filtering.

WITH products(name, category, price, stock) AS (
  VALUES
    ('Laptop', 'Electronics', 70000, 12),
    ('Camera', 'Electronics', 45000, 0),
    ('Chair', 'Furniture', 8000, 20),
    ('Desk', 'Furniture', 15000, 5),
    ('Tablet', 'Electronics', 30000, 8)
)
SELECT name, category, price, stock
FROM products
WHERE (category = 'Electronics' AND price > 25000)
  AND NOT stock = 0;

AND, OR, and NOT with LIKE

Logical operators can also be combined with pattern matching using LIKE.

WITH products(name, category) AS (
  VALUES
    ('Smartphone Pro', 'Mobile'),
    ('Smartphone Mini', 'Mobile'),
    ('Wireless Mouse', 'Computer'),
    ('Smart Lamp', 'Home'),
    ('Bluetooth Speaker', 'Audio')
)
SELECT name, category
FROM products
WHERE name LIKE 'Smart%'
  AND NOT category = 'Home';

AND, OR, and NOT with IN

IN can be combined with logical operators to include or exclude groups of values.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aditi', 'Sales', 52000),
    ('Rahul', 'IT', 75000),
    ('Nisha', 'HR', 48000),
    ('Karan', 'Finance', 68000),
    ('Pooja', 'IT', 62000)
)
SELECT name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance')
  AND NOT salary < 60000;

Logical Operator Precedence

SQL evaluates logical operators in a specific order. In general, NOT is evaluated before AND, and AND is evaluated before OR.

WITH students(name, subject, score) AS (
  VALUES
    ('Aman', 'Math', 85),
    ('Riya', 'Science', 78),
    ('Dev', 'Math', 92),
    ('Sara', 'Science', 88),
    ('Kunal', 'History', 95)
)
SELECT name, subject, score
FROM students
WHERE subject = 'Math'
   OR subject = 'Science'
  AND score >= 85;

Using Parentheses for Clear Logic

Using parentheses makes the intended logic explicit and avoids confusion when combining multiple logical operators.

WITH students(name, subject, score) AS (
  VALUES
    ('Aman', 'Math', 85),
    ('Riya', 'Science', 78),
    ('Dev', 'Math', 92),
    ('Sara', 'Science', 88),
    ('Kunal', 'History', 95)
)
SELECT name, subject, score
FROM students
WHERE (subject = 'Math' OR subject = 'Science')
  AND score >= 85;

AND, OR, NOT Comparison

Operator	Purpose	Example
AND	All conditions must be true	`price > 1000 AND stock > 0`
OR	At least one condition must be true	`category = 'Book' OR category = 'Music'`
NOT	Reverses a condition	`NOT status = 'Closed'`
NOT IN	Excludes specified values	`city NOT IN ('Delhi','Pune')`

Advantages of AND, OR, and NOT

Allows multiple filtering conditions in a single query.
Helps create precise database searches.
AND can restrict results using multiple requirements.
OR can match any one of several conditions.
NOT can exclude unwanted records.
Parentheses can be used to build complex logical expressions.

Best Practices

Use parentheses when combining AND and OR.
Keep complex conditions easy to read and understand.
Use AND when every condition must be satisfied.
Use OR when any one of several conditions can match.
Use NOT when you need to exclude a condition.
Test complex logical expressions with sample data before using them on large tables.

The AND, OR, and NOT operators are essential for creating flexible SQL conditions. Mastering these operators allows you to filter database records with much greater precision.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.