Skip to main content
MySQL Basics
CHAPTER 09 Beginner

SQL Operators and Conditions

Updated: May 16, 2026
5 min read

# CHAPTER 9

SQL Operators and Conditions

1. Introduction

Business rules are rarely simple. An HR manager doesn't ask for "all employees in Sales." They ask for "all employees in Sales OR Marketing, who were hired BETWEEN 2020 and 2023, AND who are NOT currently managers." To translate these complex, multi-layered human requests into database logic, we must master Boolean Operators and Conditional SQL clauses. In this chapter, we will learn how to chain conditions together to create laser-focused queries.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Combine multiple WHERE conditions using AND and OR.
  • Use the IN operator to replace massive OR chains.
  • Select ranges of data using BETWEEN.
  • Reverse logic using the NOT operator.
  • Safely query empty data using IS NULL.

3. The AND / OR Operators

These operators allow you to string multiple conditions together.
  • AND: Every single condition must be True.
  • OR: Only one of the conditions needs to be True.
sql
1234567 
-- Find an employee who is in IT and makes a lot of money. BOTH must be true.
SELECT * FROM employees 
WHERE department = 'IT' AND salary > 90000;

-- Find employees who work in EITHER Sales or HR.
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'HR';

4. The IN Operator (The OR Shortcut)

If you want to find employees in Sales, HR, IT, and Exec, typing OR department = '...' four times is tedious and prone to typos. The IN operator allows you to check against a list of values instantly.
sql
123 
-- This is much cleaner than using multiple ORs!
SELECT * FROM employees 
WHERE department IN ('Sales', 'HR', 'IT', 'Exec');

5. The BETWEEN Operator

When searching for dates or numerical ranges, you could write: WHERE salary >= 50000 AND salary <= 100000. However, the BETWEEN operator makes this much more readable.
sql
1234567 
-- Find mid-level salaries (Note: BETWEEN is inclusive of the numbers provided!)
SELECT * FROM employees 
WHERE salary BETWEEN 50000 AND 100000;

-- Find employees hired in the year 2023
SELECT * FROM employees 
WHERE hire_date BETWEEN &#039;2023-01-01' AND '2023-12-31';

6. The NOT Operator

Sometimes it is easier to tell MySQL what you *don't* want. The NOT operator reverses the logic of any condition.
sql
1234567 
-- Find everyone except the Executives
SELECT * FROM employees 
WHERE department NOT IN (&#039;Exec');

-- Find everyone who makes outside the normal range
SELECT * FROM employees 
WHERE salary NOT BETWEEN 40000 AND 150000;

7. The Danger of NULL

In database theory, NULL does not mean "Zero" or "Blank text". NULL means Unknown or Missing. Because NULL is a concept of nothingness, you cannot do math on it. If John's salary is NULL, and you query WHERE salary = NULL, MySQL will return zero results. You cannot say "Does $50,000 equal Unknown?"

To find missing data, you must use the special IS NULL or IS NOT NULL operators!

sql
12345678 
-- WRONG: This will crash or return zero results.
-- SELECT * FROM employees WHERE email = NULL;

-- CORRECT: Find users who forgot to enter an email address
SELECT * FROM employees WHERE email IS NULL;

-- Find users who successfully provided an email
SELECT * FROM employees WHERE email IS NOT NULL;

8. Mini Project: Complex HR Query

The CEO wants a list of all non-management employees who were hired in 2022 and work in either IT or Marketing. We must chain everything together!
sql
12345 
SELECT first_name, department, hire_date 
FROM employees 
WHERE is_manager = 0 
  AND hire_date BETWEEN &#039;2022-01-01' AND '2022-12-31'
  AND department IN (&#039;IT', 'Marketing');

9. Common Mistakes

  • Order of Operations (PEMDAS): Just like in math, AND takes precedence over OR. If you mix them without using parentheses, the query will return chaotic results.
*Wrong:* WHERE dept = 'IT' OR dept = 'HR' AND salary > 50000 (This finds all IT people regardless of salary, plus HR people making over 50k). *Correct:* WHERE (dept = 'IT' OR dept = 'HR') AND salary > 50000 (This correctly applies the salary rule to both departments!).

10. Best Practices

  • Always use IN over OR for exact matches: It is cleaner to read, and modern MySQL engines are highly optimized to process an IN list much faster than a long chain of OR conditions.

11. Exercises

  1. 1. Write a query to find all products that are in_stock = 1 and have a price between $10 and $50.
  1. 2. Why does the query SELECT * FROM users WHERE phone_number = NULL fail to return users with missing phone numbers?

12. MCQ Quiz with Answers

Question 1

To ensure that the OR logic is evaluated before the AND logic in a complex WHERE clause, what must you use?

Question 2

Which operator should be used as a cleaner, faster alternative to stringing together multiple OR statements checking the exact same column?

13. Interview Questions

  • Q: Explain the mathematical concept of NULL in SQL. Why must you use IS NULL instead of the = operator?
  • Q: Describe how you would troubleshoot a query containing both AND and OR operators that is returning more rows than expected.

14. FAQs

Q: Can I use BETWEEN with letters instead of numbers? A: Yes! WHERE last_name BETWEEN 'A' AND 'M' will return everyone whose last name starts with the first half of the alphabet!

15. Summary

By mastering operators, you transition from asking simple questions to conducting deep, multi-variable investigations. You can safely navigate missing data with IS NULL, condense logic with IN and BETWEEN, and strictly control evaluation order using parentheses.

16. Next Chapter Recommendation

We know how to filter individual rows. But what if we don't want the individuals? What if the boss asks, "What is the total sum of all salaries we pay?" or "What is the average price of our products?" In Chapter 10: Aggregate Functions in MySQL, we will learn how to calculate mathematical summaries across millions of rows instantly.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter Sorting, Limiting, and Searching Data
Next Chapter Aggregate Functions in MySQL

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6