Chapter 08 of 35

SQL Operators (AND, OR, IN, BETWEEN, LIKE)

In the WHERE Clause chapter we filtered rows with a single condition such as dept_id = 20 or salary > 90000. Real questions are rarely that simple:

  • "Engineering employees and earning above 1 lakh"

  • "Employees in Mumbai, Delhi or Kochi"

  • "Orders placed between 1 and 29 February"

  • "Customers whose name starts with 'A'"

This chapter covers the operators that make these questions easy: the logical operators AND, OR and NOT, and the special operators IN, BETWEEN and LIKE. We will also see why operator precedence and parentheses matter so much, using a real query that gives the wrong answer.

All examples use the shared company_db sample database, with real MySQL 8.4 output.


Logical Operators

Logical operators combine conditions. For now we will treat each condition as simply TRUE or FALSE. (When NULL is involved there is a third value, UNKNOWN; the full three-valued truth tables are in the NULL Values chapter.)

A

B

A AND B

A OR B

NOT A

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

FALSE

TRUE

FALSE

FALSE

TRUE

FALSE

TRUE

TRUE

FALSE

FALSE

FALSE

FALSE

TRUE

MySQL represents TRUE as 1 and FALSE as 0, so you can check this yourself:

SELECT 1 AND 0, 1 OR 0, NOT 1, 1 XOR 1;
+---------+--------+-------+---------+
| 1 AND 0 | 1 OR 0 | NOT 1 | 1 XOR 1 |
+---------+--------+-------+---------+
|       0 |      1 |     0 |       0 |
+---------+--------+-------+---------+
1 row in set (0.00 sec)

(XOR, "exactly one is true", is a MySQL extension and rarely used in practice.)


AND: All Conditions Must Be True

Engineering employees (department 10) earning more than 1,00,000:

SELECT name, dept_id, salary
FROM employees
WHERE dept_id = 10 AND salary > 100000;
+--------------+---------+-----------+
| name         | dept_id | salary    |
+--------------+---------+-----------+
| Aarav Sharma |      10 | 150000.00 |
+--------------+---------+-----------+
1 row in set (0.00 sec)

Priya and Rohan are in department 10 but earn 95,000, so the second condition fails for them. Sneha earns 1,10,000 but is in department 20, so the first fails. Only Aarav passes both.

AND narrows the result. Each extra AND condition can only keep the same rows or remove more.

OR: At Least One Condition Must Be True

SELECT name, city
FROM employees
WHERE city = 'Mumbai' OR city = 'Delhi';
+--------------+--------+
| name         | city   |
+--------------+--------+
| Sneha Kapoor | Mumbai |
| Vikram Singh | Mumbai |
| Karan Patel  | Delhi  |
+--------------+--------+
3 rows in set (0.00 sec)

OR widens the result. Each extra OR condition can only keep the same rows or add more.

A common beginner mistake is to translate "employees in Mumbai and Delhi" into city = 'Mumbai' AND city = 'Delhi'. A single row's city cannot be two values at once, so that query always returns nothing. In English we say "and", but in logic we mean OR.

MySQL also accepts && for AND and || for OR, but both are deprecated; for example, using || raises warning 1287: "'|| as a synonym for OR' is deprecated". In standard SQL, || means string concatenation, so always write the words AND and OR.

NOT: Reverse a Condition

SELECT name, salary FROM employees WHERE NOT salary > 60000;
+--------------+----------+
| name         | salary   |
+--------------+----------+
| Ananya Das   | 58000.00 |
| Meera Nair   | 48000.00 |
| Ishita Gupta | 55000.00 |
+--------------+----------+
3 rows in set (0.00 sec)

NOT salary > 60000 is the same as salary <= 60000, and the second form is easier to read. NOT is most useful together with the special operators: NOT IN, NOT BETWEEN, NOT LIKE.

One warning: NOT does not bring back NULL rows. WHERE NOT dept_id = 10 returns the 6 employees of departments 20, 30 and 40, but not Ishita, whose department is NULL. The reason (NOT UNKNOWN is still UNKNOWN) is explained in the NULL Values chapter.


Operator Precedence: AND Before OR

This is the most important idea in this chapter, and a favourite interview trap.

Question: find employees in Engineering (10) or Sales (20) who earn more than 1,00,000.

A natural first attempt:

SELECT name, dept_id, salary
FROM employees
WHERE dept_id = 10 OR dept_id = 20 AND salary > 100000;
+--------------+---------+-----------+
| name         | dept_id | salary    |
+--------------+---------+-----------+
| Aarav Sharma |      10 | 150000.00 |
| Priya Iyer   |      10 |  95000.00 |
| Rohan Mehta  |      10 |  95000.00 |
| Sneha Kapoor |      20 | 110000.00 |
+--------------+---------+-----------+
4 rows in set (0.00 sec)

Priya and Rohan earn 95,000, so why are they here? Because AND has higher precedence than OR, just like * binds tighter than + in maths (2 + 3 * 4 = 14, not 20). MySQL actually read the query as:

dept_id = 10   OR   (dept_id = 20 AND salary > 100000)
└── everyone ──┘    └─── only rich Sales employees ───┘
    in dept 10

Every department-10 employee passes the left side, no matter the salary. The fix is to add parentheses to group the OR:

SELECT name, dept_id, salary
FROM employees
WHERE (dept_id = 10 OR dept_id = 20) AND salary > 100000;
+--------------+---------+-----------+
| name         | dept_id | salary    |
+--------------+---------+-----------+
| Aarav Sharma |      10 | 150000.00 |
| Sneha Kapoor |      20 | 110000.00 |
+--------------+---------+-----------+
2 rows in set (0.00 sec)

Let's trace Priya's row through both versions:

Priya: dept_id = 10, salary = 95000

Without parentheses:
  dept_id = 10                    → TRUE
  dept_id = 20 AND salary > 100000 → FALSE AND FALSE → FALSE
  TRUE OR FALSE                   → TRUE    (kept, wrong!)

With parentheses:
  (dept_id = 10 OR dept_id = 20)  → TRUE OR FALSE → TRUE
  salary > 100000                 → FALSE
  TRUE AND FALSE                  → FALSE   (dropped, correct)

The precedence order of the operators in this chapter, from highest (evaluated first) to lowest:

Level

Operators

1 (highest)

*, /, DIV, %

2

+, -

3

Comparisons: =, <>, <, >, <=, >=, IS, LIKE, IN

4

BETWEEN

5

NOT

6

AND

7

XOR

8 (lowest)

OR

Rule: whenever a WHERE clause mixes AND and OR, add parentheses, even if you know the precedence. It costs nothing and makes your intent obvious to the next reader.

One more example: delivered orders that are either from customer 101 or above 5,000.

SELECT order_id, customer_id, amount, status
FROM orders
WHERE status = 'Delivered' AND (customer_id = 101 OR amount > 5000);
+----------+-------------+---------+-----------+
| order_id | customer_id | amount  | status    |
+----------+-------------+---------+-----------+
|     1001 |         101 | 2500.00 | Delivered |
|     1004 |         103 | 7300.00 | Delivered |
+----------+-------------+---------+-----------+
2 rows in set (0.00 sec)

Order 1007 (customer 101, 5,600) is not here because it is Pending, and the AND requires Delivered.


Special Operators

IN, BETWEEN and LIKE are comparison operators with a special shape: they test a value against a list, a range and a pattern. Each one can be written with the basic operators from the WHERE Clause chapter, but much more readably.

IN: Match Any Value in a List

Writing city = 'Mumbai' OR city = 'Delhi' OR city = 'Kochi' gets long. IN is the shorthand:

SELECT name, city
FROM employees
WHERE city IN ('Mumbai', 'Delhi', 'Kochi');
+--------------+--------+
| name         | city   |
+--------------+--------+
| Sneha Kapoor | Mumbai |
| Vikram Singh | Mumbai |
| Karan Patel  | Delhi  |
| Meera Nair   | Kochi  |
+--------------+--------+
4 rows in set (0.00 sec)

x IN (a, b, c) means exactly x = a OR x = b OR x = c.

Because IN is a single comparison, it also avoids the precedence trap from the previous section. Our earlier question becomes:

SELECT name, dept_id, salary
FROM employees
WHERE dept_id IN (10, 20) AND salary > 100000;
+--------------+---------+-----------+
| name         | dept_id | salary    |
+--------------+---------+-----------+
| Aarav Sharma |      10 | 150000.00 |
| Sneha Kapoor |      20 | 110000.00 |
+--------------+---------+-----------+
2 rows in set (0.00 sec)

The values in the list should have the same type as the column. Numbers in a numeric list do not need identical formatting:

SELECT order_id, amount FROM orders WHERE amount IN (950, 800.00, 2500);
+----------+---------+
| order_id | amount  |
+----------+---------+
|     1001 | 2500.00 |
|     1005 |  950.00 |
|     1008 |  800.00 |
+----------+---------+
3 rows in set (0.00 sec)

The list can also be the result of another query, for example WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Mumbai'). That powerful form is covered in the Subqueries chapter.

NOT IN

Orders that are neither delivered nor cancelled:

SELECT order_id, status
FROM orders
WHERE status NOT IN ('Delivered', 'Cancelled');
+----------+---------+
| order_id | status  |
+----------+---------+
|     1005 | Shipped |
|     1007 | Pending |
|     1008 | Pending |
+----------+---------+
3 rows in set (0.00 sec)

x NOT IN (a, b) means x <> a AND x <> b. Now try it on a column that contains NULL:

SELECT name, dept_id FROM employees WHERE dept_id NOT IN (10, 20);
+-------------+---------+
| name        | dept_id |
+-------------+---------+
| Karan Patel |      30 |
| Meera Nair  |      30 |
| Arjun Reddy |      40 |
+-------------+---------+
3 rows in set (0.00 sec)

Ishita (department NULL) is again missing. If you want her too, say so explicitly:

SELECT name, dept_id
FROM employees
WHERE dept_id NOT IN (10, 20) OR dept_id IS NULL;
+--------------+---------+
| name         | dept_id |
+--------------+---------+
| Ishita Gupta |    NULL |
| Karan Patel  |      30 |
| Meera Nair   |      30 |
| Arjun Reddy  |      40 |
+--------------+---------+
4 rows in set (0.00 sec)

There is an even nastier trap when the list itself contains a NULL: then NOT IN returns no rows at all. It is one of the most famous SQL bugs, and we will demonstrate it in the NULL Values chapter.


BETWEEN: Match a Range

Employees earning from 58,000 to 82,000:

SELECT name, salary
FROM employees
WHERE salary BETWEEN 58000 AND 82000;
+--------------+----------+
| name         | salary   |
+--------------+----------+
| Vikram Singh | 62000.00 |
| Ananya Das   | 58000.00 |
| Karan Patel  | 70000.00 |
| Arjun Reddy  | 82000.00 |
+--------------+----------+
4 rows in set (0.00 sec)

Ananya (exactly 58,000) and Arjun (exactly 82,000) are included. BETWEEN is inclusive on both ends:

x BETWEEN low AND high means exactly x >= low AND x <= high.

Running WHERE salary >= 58000 AND salary <= 82000 returns the same four rows.

Order Matters: Low First

SELECT name, salary FROM employees WHERE salary BETWEEN 82000 AND 58000;
Empty set (0.00 sec)

No error, just no rows, because it means salary >= 82000 AND salary <= 58000, which nothing can satisfy. MySQL does not swap the bounds for you.

NOT BETWEEN

SELECT name, salary FROM employees WHERE salary NOT BETWEEN 58000 AND 82000;
+--------------+-----------+
| name         | salary    |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer   |  95000.00 |
| Rohan Mehta  |  95000.00 |
| Sneha Kapoor | 110000.00 |
| Meera Nair   |  48000.00 |
| Ishita Gupta |  55000.00 |
+--------------+-----------+
6 rows in set (0.00 sec)

NOT BETWEEN low AND high means x < low OR x > high, so the boundary values are excluded.

BETWEEN with Dates

Orders placed in February 2024:

SELECT order_id, order_date, amount
FROM orders
WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29';
+----------+------------+---------+
| order_id | order_date | amount  |
+----------+------------+---------+
|     1003 | 2024-02-02 | 1200.00 |
|     1004 | 2024-02-14 | 7300.00 |
+----------+------------+---------+
2 rows in set (0.00 sec)

This works because order_date is a DATE column (no time part). With a DATETIME or TIMESTAMP column there is a classic bug. The date '2024-03-31' is treated as 2024-03-31 00:00:00, midnight at the start of 31 March, so anything later that day falls outside the range:

SELECT CAST('2024-03-31 10:30:00' AS DATETIME)
       BETWEEN '2024-03-01' AND '2024-03-31' AS in_march;
+----------+
| in_march |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

An order at 10:30 AM on 31 March is reported as "not in March". The safe pattern for any time range is a half-open interval: include the start, exclude the next period's start.

SELECT CAST('2024-03-31 10:30:00' AS DATETIME) >= '2024-03-01'
   AND CAST('2024-03-31 10:30:00' AS DATETIME) <  '2024-04-01' AS in_march;
+----------+
| in_march |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

The half-open form also saves you from remembering whether February has 28 or 29 days: "February" is simply >= '2024-02-01' AND < '2024-03-01'.

BETWEEN with Text

BETWEEN also works on strings, in dictionary order, but the upper bound often surprises people:

SELECT name FROM employees WHERE name BETWEEN 'A' AND 'K';
+--------------+
| name         |
+--------------+
| Aarav Sharma |
| Ananya Das   |
| Arjun Reddy  |
| Ishita Gupta |
+--------------+
4 rows in set (0.00 sec)

Karan Patel is not included. In dictionary order, 'K' comes before 'Karan Patel' (a shorter string that is a prefix sorts first), so 'Karan Patel' <= 'K' is false. To get names starting with A through K, use name >= 'A' AND name < 'L'.


LIKE: Pattern Matching

= matches a whole value exactly. LIKE matches a pattern built with two wildcards:

Wildcard

Matches

Example

Matches / does not match

%

Any sequence of characters, including none

'A%'

'Aarav', 'A' / 'Priya'

_

Exactly one character

'_r%'

'Priya', 'Arjun' / 'Rohan'

Starts With

SELECT name FROM employees WHERE name LIKE 'A%';
+--------------+
| name         |
+--------------+
| Aarav Sharma |
| Ananya Das   |
| Arjun Reddy  |
+--------------+
3 rows in set (0.00 sec)

Ends With

SELECT name FROM employees WHERE name LIKE '%a';
+--------------+
| name         |
+--------------+
| Aarav Sharma |
| Rohan Mehta  |
| Ishita Gupta |
+--------------+
3 rows in set (0.00 sec)

Contains

SELECT name FROM employees WHERE name LIKE '%Sharma%';
+--------------+
| name         |
+--------------+
| Aarav Sharma |
+--------------+
1 row in set (0.00 sec)

Using _ for Exact Positions

Names whose second letter is 'r':

SELECT name FROM employees WHERE name LIKE '_r%';
+-------------+
| name        |
+-------------+
| Priya Iyer  |
| Arjun Reddy |
+-------------+
2 rows in set (0.00 sec)

The _ consumes exactly one character ('P' or 'A'), then 'r' must follow, then % accepts the rest. Emails with exactly five characters before the @:

SELECT name, email FROM employees WHERE email LIKE '_____@%';
+--------------+-------------------+
| name         | email             |
+--------------+-------------------+
| Aarav Sharma | aarav@company.com |
| Priya Iyer   | priya@company.com |
| Rohan Mehta  | rohan@company.com |
| Sneha Kapoor | sneha@company.com |
| Karan Patel  | karan@company.com |
| Arjun Reddy  | arjun@company.com |
+--------------+-------------------+
6 rows in set (0.00 sec)

vikram (6), ananya (6) and ishita (6) do not match. Meera has no email at all (NULL), and NULL LIKE anything is UNKNOWN.

LIKE Without Wildcards

SELECT name FROM employees WHERE name LIKE 'Priya';
Empty set (0.00 sec)

With no % or _, the pattern must match the entire value, so LIKE 'Priya' behaves like = 'Priya'. You probably wanted 'Priya%'.

Case Sensitivity

Like =, LIKE follows the column's collation, which is case-insensitive by default in MySQL. name LIKE 'a%' returns the same three names as 'A%'. A fun consequence:

SELECT name FROM employees WHERE name NOT LIKE '%a%';
Empty set (0.00 sec)

Every one of our names contains an 'a' or an 'A', and both count. Use COLLATE utf8mb4_bin (see the WHERE Clause chapter) if case must matter.

NOT LIKE

SELECT name FROM employees WHERE name NOT LIKE 'A%';
+--------------+
| name         |
+--------------+
| Priya Iyer   |
| Rohan Mehta  |
| Sneha Kapoor |
| Vikram Singh |
| Karan Patel  |
| Meera Nair   |
| Ishita Gupta |
+--------------+
7 rows in set (0.00 sec)

Searching for a Literal % or _

What if the text itself contains % or _, like "Discount 50%" or "file_1"? Put an escape character in front of the wildcard. MySQL's default escape character is the backslash; you can choose another one with ESCAPE:

SELECT 'Discount 50%'    LIKE '%50\%%'              AS a,
       'Discount 50 off' LIKE '%50\%%'              AS b,
       'Discount 50 off' LIKE '%50%'                AS c,
       'file_1'          LIKE 'file!_%' ESCAPE '!'  AS d,
       'fileX1'          LIKE 'file!_%' ESCAPE '!'  AS e;
+---+---+---+---+---+
| a | b | c | d | e |
+---+---+---+---+---+
| 1 | 0 | 1 | 1 | 0 |
+---+---+---+---+---+
1 row in set (0.00 sec)
  • '%50\%%' means: anything, then "50%", then anything. Only the text with a real % sign matches (a = 1, b = 0).

  • Without escaping, '%50%' just means "contains 50" (c = 1).

  • 'file!_%' ESCAPE '!' requires a real underscore after "file", so "fileX1" fails (e = 0). Without the escape, _ would accept the X.

LIKE and Performance

A pattern with a fixed prefix, like 'A%', can use an index on the column, because all matching values sit next to each other in sorted order (like finding all words starting with "A" in a dictionary). A pattern starting with a wildcard, like '%Sharma%', cannot: MySQL must check every row. For large-scale text search, databases provide full-text indexes instead. We will revisit this in the Indexes chapter.

Also, use LIKE for text only. hire_date LIKE '2019%' happens to work in MySQL (the date is converted to the string '2019-07-01' first), but it is slow and non-portable. Use a range: hire_date >= '2019-01-01' AND hire_date < '2020-01-01'.


Putting It All Together

Employees in Mumbai or Bengaluru, earning 60,000 to 1,20,000, whose name contains 'a':

SELECT name, city, salary
FROM employees
WHERE city IN ('Mumbai', 'Bengaluru')
  AND salary BETWEEN 60000 AND 120000
  AND name LIKE '%a%';
+--------------+-----------+-----------+
| name         | city      | salary    |
+--------------+-----------+-----------+
| Rohan Mehta  | Bengaluru |  95000.00 |
| Sneha Kapoor | Mumbai    | 110000.00 |
| Vikram Singh | Mumbai    |  62000.00 |
+--------------+-----------+-----------+
3 rows in set (0.00 sec)

Aarav lives in Bengaluru too, but his 1,50,000 salary is outside the range. Notice how readable this is: one condition per line, each starting with AND.


Quick Reference

Operator

Meaning

Equivalent to

a AND b

Both true

-

a OR b

At least one true

-

NOT a

Reverse

-

x IN (1, 2, 3)

x is one of the values

x = 1 OR x = 2 OR x = 3

x NOT IN (1, 2)

x is none of the values

x <> 1 AND x <> 2

x BETWEEN 1 AND 5

In range, inclusive

x >= 1 AND x <= 5

x NOT BETWEEN 1 AND 5

Outside range

x < 1 OR x > 5

x LIKE 'A%'

Starts with A

-

x LIKE '%A'

Ends with A

-

x LIKE '%A%'

Contains A

-

x LIKE '_A%'

Second character is A

-


Common Mistakes

Mistake

What goes wrong

Fix

Mixing AND/OR without parentheses

AND binds first; extra rows appear

Always parenthesise the OR part

city = 'Mumbai' AND city = 'Delhi'

Always empty

Use OR or IN

BETWEEN 82000 AND 58000

Always empty

Lower bound first

BETWEEN on DATETIME with end date

Misses the last day after 00:00:00

>= start AND < next_start

Forgetting wildcards in LIKE

Behaves like =

Add % where needed

Using * or ? as wildcards

No match (those are file-system wildcards)

SQL uses % and _

Expecting NOT IN / NOT LIKE to return NULL rows

NULL rows silently missing

Add OR col IS NULL


Interview Questions

Which has higher precedence, AND or OR?

AND. a OR b AND c is evaluated as a OR (b AND c). Use parentheses when mixing them.

Is BETWEEN inclusive or exclusive?

Inclusive on both ends: x BETWEEN a AND b is x >= a AND x <= b. If a > b the result is always empty.

What is the difference between % and _ in LIKE?

% matches any number of characters (including zero); _ matches exactly one character.

How do you search for a literal % character with LIKE?

Escape it: LIKE '%50\%%' (backslash is MySQL's default escape), or choose an escape character with LIKE '%50!%%' ESCAPE '!'.

IN vs multiple ORs: is there a difference?

Logically they are identical. IN is shorter, avoids precedence mistakes, and MySQL optimises a constant IN list efficiently (it sorts the list and uses a binary search). IN can also take a subquery.

Why is LIKE '%abc' slow on a large table?

A leading wildcard means the matching values are not contiguous in an index, so MySQL cannot do an index range scan and must check every row. LIKE 'abc%' can use an index.

Why is BETWEEN risky for DATETIME ranges?

A date literal such as '2024-03-31' means 2024-03-31 00:00:00, so rows later on the last day are excluded. Use col >= '2024-03-01' AND col < '2024-04-01'.


Summary

  • AND requires all conditions (narrows); OR requires at least one (widens); NOT reverses.

  • AND is evaluated before OR. Use parentheses whenever you mix them.

  • IN is shorthand for several OR equalities; NOT IN for several <> joined by AND.

  • BETWEEN is inclusive, needs the low value first, and should be replaced by a half-open range for date-times.

  • LIKE uses % (any characters) and _ (one character), is case-insensitive by default in MySQL, and needs escaping for literal wildcards.

  • None of these operators return rows whose column is NULL (unless you test for NULL explicitly).

That last point keeps coming up. In the next chapter, NULL Values, we will finally understand what NULL really is and how to handle it correctly.