Mastering SQL Query Conditions: WHERE, BETWEEN, IN, AND/OR, NULL Checks

Filtering Data with Query Conditions

In everyday database work you often need to retrieve rows that satisfy specific criteria. SQL provides a rich set of clauses and operators to express these filters.

1. WHERE Clause

The WHERE clause appears after FROM and restricts the result set to rows that meet one or more predicates. Example returning only employees named “Liu Bei”:

SELECT * FROM employee WHERE emp_name = '刘备';

WHERE is also called a predicate.

2. Selection (Relational Algebra)

In relational algebra, filtering rows is called selection . It produces a new table containing only the rows that satisfy the condition.

3. Comparison Operators

MySQL supports standard comparison operators for numbers, strings, and dates (e.g., =, <, >, <=, >=, <>). See the illustration below for the full list.

4. BETWEEN Operator

Use BETWEEN to find rows whose values lie within a range. The range is inclusive of both endpoints.

SELECT * FROM employee WHERE salary BETWEEN 10000 AND 15000;

The query returns employees whose monthly salary is between 10,000 and 15,000 inclusive.

5. IN Operator

IN

matches a column against a list of values. Example retrieving employees named “Liu Bei”, “Guan Yu” or “Zhang Fei”:

SELECT * FROM employee WHERE emp_name IN ('刘备','关羽','张飞');

Any row matching at least one value in the list is returned.

6. NULL Checks

NULL represents missing or unknown data. Direct equality comparisons with NULL are illegal; instead use IS NULL or IS NOT NULL.

Incorrect example (produces a syntax error): SELECT * FROM employee WHERE manager = NULL; Correct usage: SELECT * FROM employee WHERE manager IS NULL; Result of IS NULL is true when the expression evaluates to NULL; IS NOT NULL returns true otherwise.

7. Compound Conditions

7.1 Logical AND

Both sides must be true for the whole expression to be true.

SELECT emp_name, sex, salary FROM employee WHERE sex = '女' AND salary > 10000;

7.2 Logical OR

At least one side true makes the whole expression true.

SELECT emp_name, sex, salary FROM employee WHERE emp_name = '刘备' OR emp_name = '关羽' OR emp_name = '张飞';

7.3 Short‑Circuit Evaluation

MySQL stops evaluating once the final result is determined, which can avoid runtime errors such as division by zero.

-- AND short‑circuit (left side false, right side not evaluated)
SELECT * FROM employee WHERE 1 = 0 AND 1/0 = 1;

-- OR short‑circuit (left side true, right side not evaluated)
SELECT * FROM employee WHERE 1 = 1 OR 1/0 = 1;

7.4 Logical NOT

Negates a predicate. It can be combined with other operators, e.g., NOT BETWEEN, NOT IN, NOT LIKE, NOT EXISTS, IS NOT NULL.

SELECT emp_id, emp_name FROM employee WHERE emp_name NOT IN ('刘备','关羽','张飞');

8. Operator Precedence

When mixing AND and OR, AND has higher precedence. Use parentheses to control evaluation order. Example finding employees in HR (dept_id = 2) or Finance (dept_id = 3) who have a bonus:

SELECT emp_name, dept_id, bonus FROM employee WHERE (dept_id = 2 OR dept_id = 3) AND bonus IS NOT NULL;