CHAPTER 09 Intermediate

SQL Operators and Conditional Statements

Updated: May 16, 2026

6 min read

# CHAPTER 9

SQL Operators and Conditional Statements

1. Introduction

Business rules are rarely simple. An HR system does not just search for "managers". It searches for "managers in the IT department who were hired between 2020 and 2022, but exclude those who are currently on leave." To process this level of complex logic, PostgreSQL relies on logical operators to chain multiple conditions together. In this chapter, we will learn how to write multi-layered boolean queries that filter data with surgical precision.

2. Learning Objectives

By the end of this chapter, you will be able to:

Combine multiple conditions using AND and OR.

Exclude data using the NOT operator.

Filter data against arrays using the IN operator.

Search within ranges using the BETWEEN operator.

Identify missing data using IS NULL.

Write if/then conditional logic using the CASE statement.

3. The `AND` and `OR` Operators

When you have multiple conditions in your WHERE clause, you chain them together.

AND: Both conditions MUST be true.

OR: Only ONE condition has to be true.

sql

1234567

-- Find IT employees making over 80,000
SELECT first_name FROM employees 
WHERE department = &#039;IT' AND salary > 80000;

-- Find employees who work in either IT or HR
SELECT first_name FROM employees 
WHERE department = &#039;IT' OR department = 'HR';

4. The `IN` Operator

If you need to find employees in IT, HR, Sales, and Marketing, writing OR department = '...' four times is exhausting. The IN operator checks if a value exists inside a provided list.

sql

123

-- A much cleaner way to write multiple OR statements!
SELECT first_name FROM employees 
WHERE department IN (&#039;IT', 'HR', 'Sales', 'Marketing');

5. The `BETWEEN` Operator

Used for ranges, particularly numbers and dates. BETWEEN is inclusive, meaning the start and end values are included in the results.

sql

1234567

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

-- Find products priced between $50 and $100
SELECT product_name FROM products 
WHERE price BETWEEN 50 AND 100;

6. Handling the Unknown: `IS NULL`

In database theory, NULL does not mean "Zero" or "Empty String". NULL means Unknown/Missing. Because it is unknown, you cannot use math on it! WHERE phone_number = NULL will fail. You must use IS NULL or IS NOT NULL.

sql

123

-- Find employees who have NOT provided a phone number
SELECT first_name FROM employees 
WHERE phone_number IS NULL;

7. Conditional Logic: The `CASE` Statement

What if you want to categorize data directly inside your query? The CASE statement is the SQL equivalent of an if/else block in programming.

sql

12345678910

-- Let&#039;s label our employees as Junior, Mid, or Senior based on salary
SELECT 
    first_name, 
    salary,
    CASE
        WHEN salary >= 100000 THEN &#039;Senior Level'
        WHEN salary >= 60000 THEN &#039;Mid Level'
        ELSE &#039;Junior Level'
    END AS "experience_level"
FROM employees;

*Output: The database creates a brand new, temporary column named "experience_level" and injects the correct label for each row!*

8. Mini Project: Complex Access Control

An admin wants a list of active users who are either from New York or California, registered over a year ago, but explicitly exclude any banned accounts.

sql

123456

SELECT username 
FROM users 
WHERE is_active = TRUE 
AND is_banned = FALSE
AND state IN (&#039;NY', 'CA')
AND registration_date < &#039;2023-01-01';

9. Common Mistakes

Parentheses in Logic: When mixing AND and OR, you must use parentheses to group your logic, just like in algebra.

*Wrong:* WHERE dept = 'IT' OR dept = 'HR' AND salary > 50000 (This means: ANYONE in IT, plus HR people over 50k). *Right:* WHERE (dept = 'IT' OR dept = 'HR') AND salary > 50000 (This means: HR and IT people, but both must be over 50k).

10. Best Practices

Prefer IN over OR: Whenever you find yourself writing column = X OR column = Y, replace it with column IN (X, Y). It is significantly easier to read and often optimized better by the PostgreSQL query planner.

11. Exercises

1. Write a query to find all products that are NOT in the 'Electronics' or 'Clothing' categories. (Hint: combine NOT with IN).

2. Write a query to find all employees whose terminationdate is unknown.

12. SQL Challenges
Write a CASE statement query that selects product names and creates a new column called stockstatus. If quantity is 0, it should say 'Out of Stock'. If quantity is between 1 and 10, it should say 'Low Stock'. Otherwise, 'In Stock'.

sql

1234567

SELECT product_name,
    CASE 
        WHEN quantity = 0 THEN &#039;Out of Stock'
        WHEN quantity BETWEEN 1 AND 10 THEN &#039;Low Stock'
        ELSE &#039;In Stock'
    END AS stock_status
FROM products;

13. MCQ Quiz with Answers

Question 1

Why must you use `IS NULL` instead of `= NULL` when searching for missing data?

Question 2

In a `CASE` statement, what happens if none of the `WHEN` conditions are met and no `ELSE` clause is provided?

14. Interview Questions

Q: Explain the mathematical precedence of AND versus OR in a WHERE clause, and why parentheses are critical when mixing them.

Q: Describe how a CASE statement functions and provide an example of a business reporting scenario where it would be utilized.

15. FAQs

Q: Can I use BETWEEN for letters/strings? A: Yes! WHERE name BETWEEN 'A' AND 'C' will return names starting with A and B. However, be careful, as it stops exactly at 'C', meaning it will not include 'Charlie'.

16. Summary

By mastering logical operators, you have upgraded your SQL from simple data fetching to complex algorithmic filtering. You can now build highly specific segments of data, handle missing values gracefully, and inject dynamic conditional logic directly into your outputs using CASE.

17. Next Chapter Recommendation

So far, every query has returned individual rows of data. But what if the CEO wants to know the *Total Revenue* of the company, or the *Average Salary* of the IT department? In Chapter 10: Aggregate Functions in PostgreSQL, we will learn how to compress millions of rows into a single mathematical answer.

Browse All 21+ Subject Areas

Quick Links

Visual Algorithm Labs

Frontend Dev

The Future of Web Architecture in 2026

Practice Quizzes

SQL Operators and Conditional Statements #

1. Introduction #

2. Learning Objectives #

3. The AND and OR Operators #

4. The IN Operator #

5. The BETWEEN Operator #

6. Handling the Unknown: IS NULL #

7. Conditional Logic: The CASE Statement #

8. Mini Project: Complex Access Control #

9. Common Mistakes #

10. Best Practices #

11. Exercises #

12. SQL Challenges #

13. MCQ Quiz with Answers #

Why must you use IS NULL instead of = NULL when searching for missing data?

In a CASE statement, what happens if none of the WHEN conditions are met and no ELSE clause is provided?

14. Interview Questions #

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #

Finish this Chapter

Discussion

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6

Send Feedback / Bug

Feedback Submitted!

SQL Operators and Conditional Statements

1. Introduction

2. Learning Objectives

3. The `AND` and `OR` Operators

4. The `IN` Operator

5. The `BETWEEN` Operator

6. Handling the Unknown: `IS NULL`

7. Conditional Logic: The `CASE` Statement

8. Mini Project: Complex Access Control

9. Common Mistakes

10. Best Practices

11. Exercises

12. SQL Challenges

13. MCQ Quiz with Answers

Why must you use `IS NULL` instead of `= NULL` when searching for missing data?

In a `CASE` statement, what happens if none of the `WHEN` conditions are met and no `ELSE` clause is provided?

14. Interview Questions

15. FAQs

16. Summary

17. Next Chapter Recommendation