Previous
Written by: rJTutorial Team | Last Updated:

SQL Logical Operators

SQL logical operators are the building blocks of complex database queries. While simple comparison operators (like = or >) allow you to check a single condition, logical operators let you chain these conditions together to form sophisticated business logic. They evaluate expressions and return a boolean value: TRUE, FALSE, or UNKNOWN (usually handled as NULL).

Developer Tip: Logical operators are essential when building search filters or reporting dashboards where users need to narrow down data based on multiple criteria simultaneously.

List of SQL Logical Operators

Operator Description Example
AND Returns TRUE only if all separated conditions are true. status = 'Active' AND stock > 0
OR Returns TRUE if at least one of the conditions is true. city = 'London' OR city = 'Paris'
NOT Inverts the boolean value of the condition (True becomes False). NOT category = 'Discontinued'

 

Key Features of Logical Operators

1. AND Operator

The AND operator acts as a strict filter. It ensures that a row is included in your result set only when it satisfies every single condition you’ve specified. It is most commonly used to narrow down results.

Real-World Example: Imagine you are building an e-commerce backend and need to find high-value customers who live in a specific region for a marketing campaign.

SELECT customer_id, email, total_spent 
FROM customers  
WHERE total_spent > 5000 AND region = 'North America';
  • Only customers who meet both the spending threshold and the location requirement will appear in the list.
Best Practice: When using multiple AND conditions, place the condition that is most likely to be false or filters the most rows first. Many SQL engines use "short-circuit" evaluation, which can slightly improve performance.

2. OR Operator

The OR operator is more inclusive. It expands your results by allowing rows to pass the filter if any of the conditions are met. This is ideal for selecting records that might fall into one of several different categories.

Real-World Example: A store manager wants to see a list of all products that are either low on stock or are currently marked as "On Sale."

SELECT product_name, stock_level, promo_status  
FROM inventory  
WHERE stock_level < 10 OR promo_status = 'On Sale';
  • If a product has 5 items in stock, it shows up. If it has 100 items but is "On Sale," it also shows up.
Common Mistake: Beginners often use OR when they actually need AND, or vice-versa. Always double-check your logic: AND narrows your search, while OR widens it.

3. NOT Operator

The NOT operator is used to exclude specific records. It negates a condition, meaning it returns rows where the condition is not met. It is often paired with other operators like IN, LIKE, or BETWEEN.

Real-World Example: You want to fetch a list of all employees except those who work in the 'Human Resources' or 'Legal' departments.

SELECT name, position, department  
FROM employees  
WHERE NOT department = 'HR';
  • This returns every employee, provided their department is anything other than 'HR'.
Watch Out: Be careful with NOT and NULL values. In SQL, NOT (NULL) is still NULL, not TRUE. This can lead to unexpected missing rows if your columns contain null values.

 

Combining Logical Operators

In production environments, you will rarely use just one operator. You can chain AND, OR, and NOT to handle complex requirements. However, the order of evaluation becomes critical here.

Best Practice: Always use parentheses () when combining AND and OR. This makes your code readable for other developers and ensures the SQL engine executes the logic exactly as you intended.

Example 1: Combining AND and OR

Suppose you want to find developers in the 'IT' department who are either highly paid or very experienced.

SELECT name, salary, experience_years  
FROM employees  
WHERE department = 'IT' 
AND (salary > 80000 OR experience_years > 10);
  • Without the parentheses, the query would look for (IT employees with high salaries) OR (anyone with more than 10 years experience, regardless of department). The parentheses ensure the 'IT' requirement applies to everyone.

Example 2: Using NOT with OR

SELECT product_id, price  
FROM products  
WHERE NOT (price < 10 OR price > 100);
  • This query excludes cheap items (under 10) and luxury items (over 100), effectively returning only mid-range products. This is logically equivalent to writing WHERE price >= 10 AND price <= 100.

 

Logical Operator Precedence

SQL evaluates logical operators in a specific hierarchy if you don't use parentheses. This is similar to "PEMDAS" in mathematics:

  1. NOT (Evaluated first)
  2. AND (Evaluated second)
  3. OR (Evaluated last)

Example of Precedence Risk:

SELECT * FROM orders 
WHERE status = 'Shipped' OR status = 'Pending' AND total > 100;

Because AND is evaluated before OR, SQL reads this as: "Give me all Pending orders over 100, OR any Shipped order regardless of price." If you wanted all Shipped or Pending orders that are over 100, this query would fail without parentheses around the OR conditions.

Developer Tip: Even if you know the precedence rules by heart, use parentheses anyway. It removes ambiguity for the next developer who has to maintain your code.

 

Summary

Mastering SQL logical operators is the key to accurate data retrieval. Use AND to ensure multiple requirements are met, OR to allow for flexibility between options, and NOT to filter out what you don't need. By combining these with parentheses, you can translate complex business rules into clean, efficient SQL queries.

Previous