Sorting and filtering data in MySQL. Using WHERE to narrow rows, ORDER BY to sort results, and a few extra operators for ranges and NULLs.
Filtering with WHERE
Filtering means keeping only the rows that match your criteria. In MySQL you do that with the WHERE clause.
Basic filtering
Suppose you have a products table and you want products priced over £100:
SELECT * FROM products WHERE price > 100;That returns every column from products where the price is greater than £100.
Combining conditions
You can combine conditions with AND and OR. Products over £100 and in stock:
SELECT * FROM products WHERE price > 100 AND in_stock = TRUE;Products either over £100 or in stock:
SELECT * FROM products WHERE price > 100 OR in_stock = TRUE;Partial matches with LIKE
LIKE does pattern matching. To find products whose names start with ‘Coffee’:
SELECT * FROM products WHERE name LIKE 'Coffee%';The % symbol is a wildcard – it matches any sequence of characters.
Sorting with ORDER BY
ORDER BY puts your results in a specific order – ascending (ASC) or descending (DESC).
Basic sorting
Sort products by price, lowest first:
SELECT * FROM products ORDER BY price ASC;Highest first:
SELECT * FROM products ORDER BY price DESC;Sorting by multiple columns
You can sort by more than one column. Price first, then name:
SELECT * FROM products ORDER BY price, name;Products with the same price are then sorted by name.
Combining WHERE and ORDER BY
Often you want to filter and sort in one query. In-stock products, most expensive first:
SELECT * FROM products WHERE in_stock = TRUE ORDER BY price DESC;That gives you all in-stock products, sorted from most to least expensive.
BETWEEN, IN, and NULL
A few more operators for slightly trickier filters:
- BETWEEN: filter within a range. Products priced between £50 and £150:
SELECT * FROM products WHERE price BETWEEN 50 AND 150;- IN: match any value in a list. Products in specific categories:
SELECT * FROM products WHERE category_id IN (2, 5, 7);- NULL: find rows where a value is not set:
SELECT * FROM products WHERE category_id IS NULL;A few practical tips
- Watch performance on big tables: filtering and sorting large datasets can get slow if you are not careful.
- Index the right columns: indexing columns used in WHERE and ORDER BY can help a lot.
- Test your queries: especially with complex filters, run them and check the results match what you expect.
Wrapping up
Sorting and filtering are basic skills you will use constantly – reports, dashboards, or just making sense of a table. Try different combinations on your own data and they will soon feel routine.