SQL SELECT Explained
The One Statement Behind Every Query You’ll Ever Write
Before joins, before subqueries, before window functions — there is SELECT. Master it completely, and everything else builds on top.
— -
What Is a SELECT Statement?
A SELECT statement is how you ask a database a question. You tell it which table to look in, which columns you want, and optionally which rows to include. The database returns a result set — a table of matching rows.
The most basic form looks like this:
SELECT column1, column2 FROM table_name;
SELECT * FROM users; // * means every column
Simple enough. But the power comes from everything you can layer on top.
— -
Filtering Rows with WHERE
Most of the time you don’t want every row — you want specific ones. That’s what WHERE is for.
SELECT name, email, country
FROM users
WHERE country = ‘Egypt’;
You can combine conditions using AND and OR:
SELECT name, salary
FROM employees
WHERE department_id = 1 AND salary > 6000;
And negate any condition with NOT:
SELECT name, stock
FROM products
WHERE NOT stock = 0;
One thing that trips up almost every beginner: NULL values. In SQL, NULL means “unknown” — and you cannot compare it with = or !=. You have to use IS NULL or IS NOT NULL:
SELECT name, referrer_id
FROM users
WHERE referrer_id IS NULL; // users who were not referred by anyone
Using WHERE referrer_id = NULL will always return zero rows. The SQL standard is explicit about this: nothing equals NULL, not even NULL itself.
— -
Pattern Matching with LIKE
When you need to search by a partial value, LIKE comes in handy. The % symbol is a wildcard that matches any number of characters.
SELECT name FROM users WHERE name LIKE ‘Mo%’; // starts with Mo
SELECT email FROM users WHERE email LIKE ‘%@gmail.com’; // any Gmail address
SELECT name FROM products WHERE name LIKE ‘%Pro%’; // Pro anywhere in name
When you need to match exactly one character, use _ (underscore) instead:
SELECT name FROM users WHERE name LIKE ‘A___’; // 4-letter names starting with A
This distinction matters more than it looks. A common mistake is writing LIKE ‘%term%’ everywhere and wondering why queries are slow — the leading % prevents any index from being used, forcing a full table scan.
— -
Filtering Ranges with IN and BETWEEN
Instead of chaining multiple OR conditions, IN lets you check against a list of values in one clean line:
SELECT name, country
FROM users
WHERE country IN (‘Egypt’, ‘Saudi Arabia’, ‘UAE’);
This is much easier to read than three separate OR conditions, and most query optimizers handle it more efficiently too.
BETWEEN is useful for ranges and is inclusive on both ends:
SELECT name, price FROM products WHERE price BETWEEN 20 AND 100;
SELECT id, total, created_at
FROM orders
WHERE created_at BETWEEN ‘2023–01–01’ AND ‘2023–12–31’;
The inclusivity of BETWEEN is worth remembering. BETWEEN 20 AND 100 includes both 20 and 100. On dates, BETWEEN ‘2023–01–01’ AND ‘2023–12–31’ includes the last day but only at midnight — if you have datetime values you usually want to write < ‘2024–01–01’ instead.
— -
Sorting with ORDER BY
By default, rows come back in no guaranteed order. The database is free to return them in whatever order is cheapest to produce. To control that, use ORDER BY:
SELECT name, price FROM products ORDER BY price ASC; // cheapest first
SELECT name, created_at FROM users ORDER BY created_at DESC; // newest first
You can sort by multiple columns. The second column is used as a tiebreaker when the first has equal values:
SELECT name, country, created_at
FROM users
ORDER BY country ASC, created_at DESC;
Get Hossam Soliuman’s stories in your inbox
Join Medium for free to get updates from this writer.
This returns users grouped by country alphabetically, and within each country, the newest users appear first.
— -
Pagination with LIMIT and OFFSET
LIMIT restricts how many rows come back. It is essential for top-N queries and pagination.
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 5; // top 5 most expensive
OFFSET skips rows before returning results. Together they power pagination:
SELECT name, price FROM products ORDER BY price DESC LIMIT 10 OFFSET 0; // page 1
SELECT name, price FROM products ORDER BY price DESC LIMIT 10 OFFSET 10; // page 2
SELECT name, price FROM products ORDER BY price DESC LIMIT 10 OFFSET 20; // page 3
One thing to keep in mind: OFFSET-based pagination becomes expensive on large datasets because the database still has to scan and discard the skipped rows. For high-performance pagination on large tables, keyset pagination (WHERE id > last_seen_id) is far more efficient.
— -
Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
Aggregate functions collapse multiple rows into a single computed value. You use them when you want totals, averages, or counts — not individual rows.
SELECT COUNT(*) AS total_users FROM users;
SELECT SUM(total) AS total_revenue
FROM orders
WHERE status = ‘delivered’;
SELECT AVG(price) AS avg_price FROM products;
SELECT MAX(price) AS most_expensive, MIN(price) AS cheapest
FROM products;
The * in COUNT(*) counts every row regardless of NULL values. COUNT(column_name) counts only non-NULL values in that column. This is a subtle but important difference — COUNT(*) and COUNT(some_nullable_column) will give you different numbers on the same table.
— -
Grouping with GROUP BY
GROUP BY is used with aggregate functions when you want a result per group, not a single total across everything.
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;
SELECT ship_country, SUM(total) AS revenue
FROM orders
WHERE status = ‘delivered’
GROUP BY ship_country
ORDER BY revenue DESC;
The rule that makes GROUP BY click: any column in your SELECT that is not wrapped in an aggregate function must appear in your GROUP BY clause. If it doesn’t, the database doesn’t know which value to show for that column — since the group may contain many different values.
— -
Filtering Groups with HAVING
WHERE filters rows before grouping. HAVING filters groups after the aggregation is done. This distinction trips up a lot of developers.
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2; // only users with more than 2 orders
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 3;
You cannot use WHERE to filter on COUNT(*) or any other aggregate. WHERE runs before the aggregation even happens — there is nothing to compare yet. That is exactly what HAVING is for.
— -
The Full Execution Order
This is the one thing most SQL tutorials skip, and it causes real confusion. SQL does not execute in the order you write it. The actual internal order is:
1. FROM — which table?
2. WHERE — filter individual rows
3. GROUP BY — group the remaining rows
4. HAVING — filter the groups
5. SELECT — pick the columns
6. ORDER BY — sort the result
7. LIMIT/OFFSET — slice the result
This is why you cannot reference a column alias defined in SELECT inside a WHERE clause. WHERE runs before SELECT has even evaluated the alias. And it is why you can reference an alias in ORDER BY — ORDER BY runs after SELECT.
— -
Putting It All Together
Let’s write a query that uses everything we’ve covered. Top 3 countries by number of active users, only including countries that have at least 2:
SELECT country, COUNT(*) AS user_count
FROM users
WHERE is_active = 1
GROUP BY country
HAVING COUNT(*) >= 2
ORDER BY user_count DESC
LIMIT 3;
Read it in execution order — not in the order it’s written. Take the users table, keep only active rows, group by country, discard groups under 2 users, sort by user count descending, return the top 3. Writing it and reading it are two different exercises, and both are worth practicing.
— -
Summary
SELECT is the foundation every other SQL concept is built on. WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT are not separate topics — they are the clauses that extend a single statement into a precise question. Understanding the execution order is the key that makes all the edge cases suddenly make sense. Once that mental model is in place, aggregations stop being confusing and pagination stops producing unexpected results.
Everything from joins to window functions is just SELECT with more layers. The foundation is here.
— -
Written by Hossam Soliuman — Backend Developer specializing in Laravel. Follow for more PHP and Laravel deep-dives.
