Skip to content
elephantoo

WHERE, operators, ORDER BY & LIMIT

Lesson 7 of 31 16 min read

Comparison and logical operators, IN, BETWEEN, LIKE, NULL checks, sorting and pagination.

SELECT on its own returns every row. WHERE keeps only the rows that match a condition, ORDER BY puts them in order, and LIMIT takes a slice. Together they answer most everyday questions: "the 10 most recent orders over ₹5,000", "customers in Pune who haven't verified their email", and so on.

Sample data#

SQL
CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    dept       VARCHAR(20) NOT NULL,
    city       VARCHAR(30),
    salary     INT NOT NULL,
    hired      DATE NOT NULL,
    manager_id INT
);

INSERT INTO employees VALUES
    (1, 'Asha',   'Engineering', 'Bengaluru', 95000, '2019-03-01', NULL),
    (2, 'Rohan',  'Engineering', 'Pune',      72000, '2021-07-15', 1),
    (3, 'Meera',  'Sales',       'Mumbai',    58000, '2020-01-10', 6),
    (4, 'Karan',  'Sales',       NULL,        61000, '2022-11-01', 6),
    (5, 'Zoya',   'IT',          'Pune',      66000, '2023-02-20', 1),
    (6, 'Vikram', 'Sales',       'Mumbai',    88000, '2018-05-05', NULL),
    (7, 'Neha',   'Engineering', 'Bengaluru', 72000, '2024-06-03', 1);

Comparison operators#

OperatorMeaning
=equal
<> or !=not equal
<, <=, >, >=less / greater than
<=>null-safe equal (NULL <=> NULL is true)
SQL
SELECT name, salary FROM employees WHERE salary >= 72000;
Output
+--------+--------+
| name   | salary |
+--------+--------+
| Asha   |  95000 |
| Rohan  |  72000 |
| Vikram |  88000 |
| Neha   |  72000 |
+--------+--------+

String comparisons follow the column's collation. With MySQL 8's default utf8mb4_0900_ai_ci, they're case-insensitive: WHERE dept = 'sales' matches 'Sales'.

AND, OR, NOT and parentheses#

SQL
SELECT name, dept, salary
FROM employees
WHERE (dept = 'Sales' OR dept = 'IT')
  AND salary > 60000;
Output
+--------+-------+--------+
| name   | dept  | salary |
+--------+-------+--------+
| Karan  | Sales |  61000 |
| Zoya   | IT    |  66000 |
| Vikram | Sales |  88000 |
+--------+-------+--------+

AND is evaluated before OR, just as multiplication happens before addition. Without the parentheses, this query would mean "all of Sales, plus IT people earning over 60,000". Always add parentheses when you mix AND and OR.

IN and BETWEEN#

IN is shorthand for several ORs on the same column:

SQL
SELECT name, city FROM employees WHERE city IN ('Pune', 'Mumbai');
Output
+--------+--------+
| name   | city   |
+--------+--------+
| Rohan  | Pune   |
| Meera  | Mumbai |
| Zoya   | Pune   |
| Vikram | Mumbai |
+--------+--------+

BETWEEN a AND b is inclusive at both ends:

SQL
SELECT name, hired FROM employees
WHERE hired BETWEEN '2021-01-01' AND '2022-12-31';
Output
+-------+------------+
| name  | hired      |
+-------+------------+
| Rohan | 2021-07-15 |
| Karan | 2022-11-01 |
+-------+------------+

Datetime trap: on a DATETIME column, BETWEEN '2022-01-01' AND '2022-12-31' stops at midnight on 31 December, missing the rest of that day. Use a half-open range instead: created_at >= '2022-01-01' AND created_at < '2023-01-01'.

Pattern matching with LIKE#

PatternMatches
'A%'starts with A
'%a'ends with a
'%ee%'contains ee
'_o%'second letter is o

% matches any number of characters (including none) and _ matches exactly one:

SQL
SELECT name FROM employees WHERE name LIKE '_o%';
Output
+-------+
| name  |
+-------+
| Rohan |
| Zoya  |
+-------+

To match a literal % or _, escape it: LIKE '100\%'. For richer patterns, MySQL 8 supports regular expressions with REGEXP (or REGEXP_LIKE()):

SQL
SELECT name FROM employees WHERE name REGEXP '^[AEIOU]';   -- starts with a vowel
Output
+------+
| name |
+------+
| Asha |
+------+

A pattern with a leading wildcard (LIKE '%ee%') can't use an ordinary index, so it scans the whole table. For searching text at scale, see Full-text search.

Working with NULL#

NULL means unknown. Any comparison with NULL gives NULL (not true), so rows are silently dropped:

SQL
SELECT COUNT(*) FROM employees WHERE city = NULL;     -- always 0
SELECT name FROM employees WHERE city IS NULL;
Output
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
+-------+
| name  |
+-------+
| Karan |
+-------+

Two more NULL surprises:

  • WHERE city <> 'Pune' does not return Karan, because his city is unknown. Write WHERE city <> 'Pune' OR city IS NULL if you want him.
  • WHERE id NOT IN (1, 2, NULL) returns no rows at all, since every comparison with the NULL is unknown. Watch for this with subqueries that can return NULL (see Subqueries).

Sorting with ORDER BY#

SQL
SELECT name, dept, salary
FROM employees
ORDER BY salary DESC, name ASC;
Output
+--------+-------------+--------+
| name   | dept        | salary |
+--------+-------------+--------+
| Asha   | Engineering |  95000 |
| Vikram | Sales       |  88000 |
| Neha   | Engineering |  72000 |
| Rohan  | Engineering |  72000 |
| Zoya   | IT          |  66000 |
| Karan  | Sales       |  61000 |
| Meera  | Sales       |  58000 |
+--------+-------------+--------+
  • ASC (ascending) is the default. DESC reverses the order.
  • Each column has its own direction. Here, ties on salary (Neha and Rohan) are broken alphabetically.
  • You can sort by an alias or an expression, e.g. ORDER BY YEAR(hired).

NULLs sort first in ascending order and last in descending order. To force them last in an ascending sort, sort on IS NULL first (false = 0 comes before true = 1):

SQL
SELECT name, city FROM employees ORDER BY city IS NULL, city, name;
Output
+--------+-----------+
| name   | city      |
+--------+-----------+
| Asha   | Bengaluru |
| Neha   | Bengaluru |
| Meera  | Mumbai    |
| Vikram | Mumbai    |
| Rohan  | Pune      |
| Zoya   | Pune      |
| Karan  | NULL      |
+--------+-----------+

Custom order with FIELD(), which returns the position of a value in a list:

SQL
SELECT name, dept FROM employees
ORDER BY FIELD(dept, 'Sales', 'IT', 'Engineering'), name;
Output
+--------+-------------+
| name   | dept        |
+--------+-------------+
| Karan  | Sales       |
| Meera  | Sales       |
| Vikram | Sales       |
| Zoya   | IT          |
| Asha   | Engineering |
| Neha   | Engineering |
| Rohan  | Engineering |
+--------+-------------+

LIMIT, OFFSET and pagination#

"Top N" queries combine ORDER BY and LIMIT. The three most recent hires:

SQL
SELECT name, hired FROM employees ORDER BY hired DESC LIMIT 3;
Output
+-------+------------+
| name  | hired      |
+-------+------------+
| Neha  | 2024-06-03 |
| Zoya  | 2023-02-20 |
| Karan | 2022-11-01 |
+-------+------------+

For pages of results, skip rows with OFFSET. Page p (starting at 1) with n rows per page is LIMIT n OFFSET (p - 1) * n:

SQL
-- page 2, 3 rows per page
SELECT id, name FROM employees ORDER BY id LIMIT 3 OFFSET 3;
Output
+----+--------+
| id | name   |
+----+--------+
|  4 | Karan  |
|  5 | Zoya   |
|  6 | Vikram |
+----+--------+

Two important rules:

  1. Sort by something unique. If several rows tie on the sort key, rows can jump between pages. Add the primary key as a tie-breaker: ORDER BY salary DESC, id.
  2. Large offsets are slow. OFFSET 100000 still reads and throws away 100,000 rows. For deep pagination use keyset pagination ("seek method"): remember the last row you showed and continue after it:
SQL
-- the previous page ended at id = 3
SELECT id, name FROM employees WHERE id > 3 ORDER BY id LIMIT 3;

With an index on the sort column, this is fast no matter how deep you go.

Putting it together#

Engineers or IT staff in Pune or Bengaluru hired since 2020, best paid first:

SQL
SELECT name, dept, city, salary
FROM employees
WHERE dept IN ('Engineering', 'IT')
  AND city IN ('Pune', 'Bengaluru')
  AND hired >= '2020-01-01'
ORDER BY salary DESC, name
LIMIT 10;
Output
+-------+-------------+-----------+--------+
| name  | dept        | city      | salary |
+-------+-------------+-----------+--------+
| Neha  | Engineering | Bengaluru |  72000 |
| Rohan | Engineering | Pune      |  72000 |
| Zoya  | IT          | Pune      |  66000 |
+-------+-------------+-----------+--------+

Common mistakes#

  • = NULL instead of IS NULL.
  • Forgetting parentheses around OR conditions.
  • Using BETWEEN with datetimes and missing the last day.
  • LIMIT without ORDER BY, which gives unpredictable "top" rows.
  • Wrapping an indexed column in a function, as in WHERE YEAR(hired) = 2021. It works, but it stops MySQL using an index on hired. Prefer hired >= '2021-01-01' AND hired < '2022-01-01' (more in Indexes).

What's next#

Next you'll transform values with MySQL's built-in string, numeric and control-flow functions.

Check your understanding

Quick quiz

0/3 answered

  1. 1.Which WHERE clause correctly finds employees whose manager is unknown?

  2. 2.What does ORDER BY salary DESC, name do?

  3. 3.WHERE dept = 'Sales' OR dept = 'IT' AND salary > 60000 is evaluated as…

Finished reading?

Mark this lesson complete to track your progress.