SQLFiltering Databeginner40 min read

Logical Operators: AND, OR, and NOT

Master SQL logical operators, combine multiple conditions, understand operator precedence and three-valued logic, and write safe, readable filters using parentheses.

In this lesson, you will learn

✓Explain how AND, OR, and NOT evaluate conditions in SQL
✓Combine multiple predicates to filter rows accurately
✓Apply SQL operator precedence: NOT, AND, then OR
✓Use parentheses to control the evaluation of complex conditions
✓Understand TRUE, FALSE, and UNKNOWN in SQL's three-valued logic
✓Avoid common NULL-related mistakes when using NOT and NOT IN
✓Write secure, readable WHERE clauses for realistic business requirements

1. Why Do We Need Logical Operators?

A WHERE clause can contain one condition or several conditions. Logical operators let us combine those conditions to express a business requirement.

Imagine a store wants products that are in stock and cost less than ₹5,000. We need both conditions to be true, so we use AND.

If a company wants customers from Delhi or Mumbai, either location is acceptable, so we use OR.

If we want to exclude discontinued products, we can use NOT or a suitable negated condition.

Logical operators do not change the table. They determine which rows pass the WHERE filter.

A Table for Our Examples

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price NUMERIC(10, 2) NOT NULL,
    stock_quantity INTEGER NOT NULL,
    is_active BOOLEAN NOT NULL
);
 
INSERT INTO products
    (product_id, title, category, price, stock_quantity, is_active)
VALUES
    (1, 'Keyboard', 'Electronics', 2500.00, 15, TRUE),
    (2, 'Office Chair', 'Furniture', 7500.00, 8, TRUE),
    (3, 'Old Mouse', 'Electronics', 500.00, 0, FALSE),
    (4, 'Desk Lamp', 'Home', 1200.00, 20, TRUE),
    (5, 'Premium Monitor', 'Electronics', 18000.00, 5, TRUE),
    (6, 'Notebook', NULL, 100.00, 100, TRUE);

We will use this small PostgreSQL table throughout the lesson. The category of Notebook is NULL, meaning its category is unknown or missing.

2. The AND Operator

AND combines two Boolean conditions. The complete expression is TRUE only when both conditions are TRUE.

For example, a product must be active AND have stock greater than zero. A product that satisfies only one of these conditions is excluded.

Find Active Products That Are in Stock

sql

1
2
3
4
SELECT product_id, title, stock_quantity, is_active
FROM products
WHERE is_active = TRUE
  AND stock_quantity > 0;

Output

Every returned product is active and has positive stock. Old Mouse is excluded because it is inactive and has no stock.

3. The OR Operator

OR combines conditions when either one can satisfy the requirement. The expression is TRUE if at least one operand is TRUE.

Suppose a customer can receive a discount if they are a premium member OR have a coupon. They do not need to satisfy both conditions.

Find Electronics or Furniture

sql

1
2
3
4
SELECT title, category
FROM products
WHERE category = 'Electronics'
   OR category = 'Furniture';

Output

A row is included if its category matches either value. The row with a NULL category does not match either comparison.

4. AND vs OR: Understand the Difference

AND makes a filter more restrictive because every condition must pass. OR makes a filter more inclusive because any one condition can pass.

Consider the conditions price < 2000 and stock_quantity > 0.

With AND, a product must be cheap and in stock. With OR, it can be cheap or in stock, including products that satisfy only one of those requirements.

Compare AND and OR

sql

1
2
3
4
5
6
7
8
9
10
11
-- Both conditions must be true
SELECT title
FROM products
WHERE price < 2000
  AND stock_quantity > 0;
 
-- At least one condition must be true
SELECT title
FROM products
WHERE price < 2000
   OR stock_quantity > 0;

The first query returns products that are both inexpensive and in stock. The second returns inexpensive products even when out of stock, as well as products in stock regardless of price.

5. The NOT Operator

NOT reverses a Boolean expression. NOT TRUE becomes FALSE, and NOT FALSE becomes TRUE. However, NOT UNKNOWN remains UNKNOWN.

You can use NOT with expressions such as IN, BETWEEN, LIKE, and EXISTS. SQL also provides readable alternatives such as NOT IN and NOT BETWEEN.

Find Inactive Products

sql

1
2
3
SELECT title, is_active
FROM products
WHERE NOT is_active;

Output

title | is_active ----------+---------- Old Mouse | false

Because is_active is a non-NULL Boolean column, NOT is_active selects rows where is_active is FALSE.

6. Common Negated Conditions

SQL supports several ways to express the opposite of a condition. Choose the version that best communicates your intention.

NOT IN, NOT BETWEEN, and NOT LIKE

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Exclude selected categories
SELECT title, category
FROM products
WHERE category NOT IN ('Electronics', 'Furniture');
 
-- Find products outside a price range
SELECT title, price
FROM products
WHERE price NOT BETWEEN 1000 AND 5000;
 
-- Find titles that do not begin with 'Old'
SELECT title
FROM products
WHERE title NOT LIKE 'Old%';

NOT IN excludes matching values, NOT BETWEEN excludes values within the inclusive range, and NOT LIKE negates the pattern match. These conditions do not automatically include NULL values.

7. SQL Operator Precedence

Operator precedence tells SQL which operators to evaluate first when an expression contains multiple operators.

For the logical operators in this lesson, the order is:

1. NOT
2. AND
3. OR

This means AND is evaluated before OR, even if OR appears earlier in the written expression. SQL does not simply evaluate all conditions from left to right.

Parentheses can override the default grouping.

An Expression Without Parentheses

sql

1
2
3
4
5
SELECT title
FROM products
WHERE price < 2000
   OR category = 'Electronics'
  AND is_active = TRUE;

SQL groups the condition as price < 2000 OR (category = 'Electronics' AND is_active = TRUE). It does not interpret the expression as (price < 2000 OR category = 'Electronics') AND is_active = TRUE.

8. The Dangerous AND/OR Bug

Suppose a system should show active users whose role is either admin or manager.

A missing pair of parentheses can allow inactive managers through the filter. In a multi-tenant application, the same mistake can accidentally return records outside the intended organization.

The database follows its precedence rules correctly. The bug is that the written condition does not represent the intended business rule.

Incorrect and Correct User Filters

sql

1
2
3
4
5
6
7
8
9
10
11
12
-- Incorrect: inactive managers can also be returned
SELECT user_id, full_name, role, is_active
FROM users
WHERE is_active = TRUE
  AND role = 'admin'
   OR role = 'manager';
 
-- Correct: both roles must belong to active users
SELECT user_id, full_name, role, is_active
FROM users
WHERE is_active = TRUE
  AND (role = 'admin' OR role = 'manager');

The incorrect condition is grouped as (is_active = TRUE AND role = 'admin') OR role = 'manager'. The corrected condition requires is_active = TRUE for both roles.

9. Parentheses: Group Related Conditions

Parentheses explicitly tell SQL which conditions belong together. They also make queries easier for other developers to review.

For example, a product might qualify if it is either a low-priced active product or an active product in the Home category. The exact grouping depends on the business rule.

When a requirement contains words such as 'either', 'both', 'unless', or 'only if', translate those relationships carefully before writing the WHERE clause.

Group Conditions Explicitly

sql

1
2
3
4
5
6
7
SELECT title, category, price, is_active
FROM products
WHERE is_active = TRUE
  AND (
      price < 2000
      OR category = 'Home'
  );

The product must be active and must satisfy at least one condition inside the parentheses: price below 2000 or category equal to Home.

10. SQL Uses Three-Valued Logic

In ordinary beginner logic, we often think a condition must be TRUE or FALSE. SQL comparisons can also produce UNKNOWN when NULL is involved.

For example, category = 'Electronics' is UNKNOWN when category is NULL. SQL uses UNKNOWN because it cannot determine whether the missing category equals Electronics.

A WHERE clause keeps a row only when the final condition is TRUE. Both FALSE and UNKNOWN cause the row to be filtered out.

Compare a Value with NULL

sql

1
2
3
SELECT title
FROM products
WHERE category = 'Electronics';

Output

title ---------------- Keyboard Old Mouse Premium Monitor

Notebook is excluded because its category is NULL, so the equality comparison evaluates to UNKNOWN.

11. Truth Tables for AND, OR, and NOT

Truth tables show how SQL combines TRUE, FALSE, and UNKNOWN. They are useful for debugging complicated conditions.

AND becomes FALSE if either operand is FALSE. If neither operand is FALSE and at least one is UNKNOWN, the result is UNKNOWN.

OR becomes TRUE if either operand is TRUE. If neither operand is TRUE and at least one is UNKNOWN, the result is UNKNOWN.

AND Truth Table

sql

1
2
3
4
5
6
7
-- A AND B
 
-- TRUE    AND TRUE    = TRUE
-- TRUE    AND FALSE   = FALSE
-- TRUE    AND UNKNOWN = UNKNOWN
-- FALSE   AND UNKNOWN = FALSE
-- UNKNOWN AND UNKNOWN = UNKNOWN

A FALSE operand is enough to make AND FALSE. Otherwise, an UNKNOWN operand can make the result UNKNOWN.

12. Handling NULL with IS NULL and IS NOT NULL

Do not use = NULL or <> NULL to test for missing values. In SQL, those comparisons evaluate to UNKNOWN rather than providing the expected NULL check.

Use IS NULL to find missing values and IS NOT NULL to find values that are present.

Find Products Without a Category

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Correct: find missing categories
SELECT title
FROM products
WHERE category IS NULL;
 
-- Correct: find known categories
SELECT title
FROM products
WHERE category IS NOT NULL;
 
-- Incorrect NULL test
SELECT title
FROM products
WHERE category = NULL;

Output

-- First query title ---------- Notebook

The first query finds Notebook. The second excludes it. The third query returns no rows because category = NULL evaluates to UNKNOWN for every row, including rows with a NULL category.

13. The NOT IN and NULL Trap

NOT IN requires special care when its list or subquery result contains NULL.

If a value does not match any listed value but the list contains NULL, SQL cannot determine whether the value should be excluded by a matching list entry. The result becomes UNKNOWN, so the row is filtered out.

This can make a NOT IN subquery return fewer rows than expected, sometimes no rows. It is not true that every NOT IN query always returns zero rows; the outcome depends on the data and comparisons.

Safer Anti-Matching with NOT EXISTS

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Potentially surprising if blocked_categories contains NULL
SELECT p.title
FROM products AS p
WHERE p.category NOT IN (
    SELECT b.category
    FROM blocked_categories AS b
);
 
-- NOT EXISTS avoids a NULL in the subquery
-- turning the entire NOT IN comparison UNKNOWN
SELECT p.title
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_categories AS b
    WHERE b.category = p.category
);

NOT EXISTS asks whether any matching row exists. A NULL in an unrelated blocked_categories row does not automatically make every non-matching category UNKNOWN. However, when p.category itself is NULL, equality still cannot match it, so decide explicitly whether products with missing categories should be included.

14. Combining Logical Operators with IN and BETWEEN

Logical operators can combine with comparison operators and predicates such as IN and BETWEEN. This can make business filters easier to read.

A product can be active, have stock, and belong to one of several categories. IN expresses the category alternatives without repeating several equality comparisons.

A Practical Product Filter

sql

1
2
3
4
5
6
SELECT title, category, price, stock_quantity
FROM products
WHERE is_active = TRUE
  AND stock_quantity > 0
  AND category IN ('Electronics', 'Home')
  AND price BETWEEN 100 AND 20000;

Every result must be active, have stock, belong to Electronics or Home, and have a price between 100 and 20000 inclusive. A NULL category does not satisfy IN.

15. Logical Operators in Access-Control Filters

Logical mistakes can become security vulnerabilities when a query is responsible for limiting which records a user may access.

Suppose every returned document must belong to organization 5, and it must be either public or shared. The organization condition must apply to both alternatives.

Never rely on a filter alone as the entire security design. Authorization should also be enforced in the application and, where appropriate, with database-level controls such as PostgreSQL Row-Level Security.

Keep the Organization Restriction in Scope

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Incorrect: shared documents from other organizations
-- can satisfy the OR condition
SELECT document_id, organization_id, is_public, is_shared
FROM documents
WHERE organization_id = 5
  AND is_public = TRUE
   OR is_shared = TRUE;
 
-- Correct: every result belongs to organization 5
SELECT document_id, organization_id, is_public, is_shared
FROM documents
WHERE organization_id = 5
  AND (
      is_public = TRUE
      OR is_shared = TRUE
  );

The incorrect expression is evaluated as (organization_id = 5 AND is_public = TRUE) OR is_shared = TRUE. The corrected expression requires organization_id = 5 for every returned row.

16. Readable and Maintainable WHERE Clauses

Correct logic is essential, but readable SQL makes mistakes easier to spot. Put each condition on its own line, group alternatives with parentheses, and use indentation consistently.

Avoid adding conditions without checking how they interact with existing AND and OR operators. When a filter becomes difficult to understand, split the requirement into smaller logical parts before writing the final query.

A Readable Multi-Condition Filter

sql

1
2
3
4
5
6
7
8
9
10
SELECT product_id, title, category, price
FROM products
WHERE is_active = TRUE
  AND stock_quantity > 0
  AND (
      category = 'Electronics'
      OR category = 'Home'
  )
  AND price <= 20000
ORDER BY price ASC;

The layout makes the mandatory conditions and the alternative category conditions easy to distinguish. ORDER BY sorts the matching products by price from lowest to highest.

17. Common Mistakes

Logical conditions often look simple but can behave unexpectedly when operators are mixed or NULL values are present.

18. Practice Questions

Try these questions before reading the solutions. Use the products table created at the beginning of the lesson.

19. Practice Solutions

Compare your answers with these solutions. Focus on why each condition belongs in its particular position.

Solutions to the Practice Questions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- Question 1: active products cheaper than 3000
SELECT title, price
FROM products
WHERE is_active = TRUE
  AND price < 3000;
 
-- Question 2: Electronics or Home
SELECT title, category
FROM products
WHERE category = 'Electronics'
   OR category = 'Home';
 
-- Question 3: active and (cheap or in Home)
SELECT title, category, price
FROM products
WHERE is_active = TRUE
  AND (
      price < 2000
      OR category = 'Home'
  );
 
-- Question 4: missing category
SELECT title
FROM products
WHERE category IS NULL;
 
-- Question 5: correct the grouping
SELECT user_id, full_name, role
FROM users
WHERE is_active = TRUE
  AND (
      role = 'admin'
      OR role = 'manager'
  );

Question 5 is corrected by grouping the role alternatives. TRUE AND UNKNOWN evaluates to UNKNOWN. A NULL in a NOT IN list can make otherwise non-matching comparisons UNKNOWN and therefore remove those rows from the result.

20. Multiple-Choice Questions

Choose the best answer for each question. Answers and explanations are included so you can check your understanding.

21. SQL Interview Questions and Answers

Review these short answers, then practice explaining each concept in your own words.

22. Lesson Summary

Logical operators allow SQL to express complex filtering requirements. AND combines mandatory conditions, OR combines alternatives, and NOT negates a condition. SQL evaluates NOT before AND and AND before OR, so parentheses are essential when grouping is important.

Remember that SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. WHERE retains only TRUE rows. Use IS NULL for missing-value checks and be especially careful with NOT IN when NULL can appear in the list or subquery.

The most important habit is to translate a business requirement into explicit, grouped conditions and test cases that should both pass and fail.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Forgetting parentheses when mixing AND and OR

Why it happens: Developers may read a condition from left to right and assume SQL will group it in the same way.

Incorrect Approach

sql

SELECT document_id
FROM documents
WHERE organization_id = 5
  AND is_public = TRUE
   OR is_shared = TRUE;

Correct Solution

sql

SELECT document_id
FROM documents
WHERE organization_id = 5
  AND (
      is_public = TRUE
      OR is_shared = TRUE
  );

Key takeaway: The incorrect query is grouped as (organization_id = 5 AND is_public = TRUE) OR is_shared = TRUE. A shared document from another organization can therefore match. The corrected query applies the organization restriction to every result.

#2 Assuming NOT NULL is a valid way to test a missing value

Why it happens: The word NOT sounds like a universal way to test the opposite of a value, but NULL represents an unknown or missing value.

Incorrect Approach

sql

SELECT title
FROM products
WHERE category = NULL;

Correct Solution

sql

SELECT title
FROM products
WHERE category IS NULL;

Key takeaway: Equality with NULL evaluates to UNKNOWN, not TRUE. Use IS NULL to find missing values.

#3 Ignoring NULL in a NOT IN subquery

Why it happens: A developer expects NOT IN to return every value that does not match a known value in the subquery.

Incorrect Approach

sql

SELECT p.title
FROM products AS p
WHERE p.category NOT IN (
    SELECT b.category
    FROM blocked_categories AS b
);

Correct Solution

sql

SELECT p.title
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_categories AS b
    WHERE b.category = p.category
);

Key takeaway: If the subquery contains NULL, some non-matching NOT IN comparisons evaluate to UNKNOWN. NOT EXISTS avoids that particular problem. Decide separately whether NULL categories in the outer table should be included.

#4 Assuming OR means both conditions must be true

Why it happens: The distinction between mandatory conditions and acceptable alternatives is unclear.

Incorrect Approach

sql

SELECT title
FROM products
WHERE category = 'Electronics'
  AND category = 'Home';

Correct Solution

sql

SELECT title
FROM products
WHERE category = 'Electronics'
   OR category = 'Home';

Key takeaway: A single category value cannot equal both different strings at once. OR is appropriate when either category is acceptable.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

AND returns TRUE only when both operands are TRUE.
OR returns TRUE when at least one operand is TRUE.
NOT reverses TRUE and FALSE, but NOT UNKNOWN remains UNKNOWN.
SQL evaluates NOT before AND, and AND before OR.
Parentheses explicitly define how a group of conditions should be evaluated.
A WHERE clause keeps only rows for which the complete condition evaluates to TRUE.
NULL can make a condition UNKNOWN, so NOT IN and other negated conditions require care.
When filtering sensitive records, group conditions carefully so that every row satisfies the intended access restrictions.

Was this lesson helpful?

Found an error? Report an issue