SQL ·
‹ Previous Next ›
⏱ 5 min read Modified: 2026-11-20

SQL SELECT Statement: Querying Data from a Table

You need the stores that sold more than 60 pounds of fruit. The query looks reasonable: SELECT shop, SUM(lbs) AS total_lbs FROM sales WHERE total_lbs > 60 GROUP BY shop; — yet MySQL replies ERROR 1054 (42S22): Unknown column 'total_lbs' in 'where clause'. The clauses of a query do not run in the order they are written, and at the moment WHERE is checked, the alias total_lbs does not exist yet.

SELECT is the SQL statement that reads data from one or more tables and returns the result as a new table (a result set of rows); the stored data does not change. In the query you specify which columns to return, which table to read (FROM), which rows to keep (WHERE), how to group them (GROUP BY, HAVING), how to sort them (ORDER BY) and how many rows to return (LIMIT). By the SQL standard SELECT belongs to DML, but it is often put in a separate group, DQL — the data query language.

What SELECT Does and Its Syntax

Data is retrieved from a database with the SELECT statement.

The general form:

SELECT [DISTINCT] columns_or_expressions
FROM table
[WHERE row_condition]
[GROUP BY columns]
[HAVING group_condition]
[ORDER BY columns [ASC | DESC]]
[LIMIT row_count [OFFSET offset]];

The parts in square brackets are optional, but their order is strict: WHERE after GROUP BY is a syntax error.

Clause What it does Example
SELECT Lists the columns and expressions of the result SELECT shop, lbs
FROM Names the table (or several tables combined with JOIN) FROM sales
WHERE Keeps only the rows for which the condition is true WHERE lbs > 20
GROUP BY Collects rows with equal values into groups GROUP BY shop
HAVING Keeps only the groups for which the condition is true HAVING SUM(lbs) > 60
ORDER BY Sorts the result ORDER BY lbs DESC
LIMIT Caps the number of result rows (MySQL, PostgreSQL) LIMIT 3

Selecting from a table:

SELECT * FROM users; -- select all fields
SELECT username FROM users; -- select the username field
SELECT * FROM users WHERE phone = '0959872323'; -- select all fields of the rows with this phone number

Here and below, users is the user table of the forum practice database, with the columns id, username, email and phone; the script that creates it is in The Forum Practice Database.

The sales Practice Table: SELECT *, Columns and Expressions

SELECT * returns all columns of a table in the order they were declared, while SELECT column1, column2 returns only the listed columns, in the listed order. Instead of a column you can write an expression — arithmetic, a function call or a constant — and MySQL evaluates it for every row.

To give the examples an output you can check, most queries in this lesson run on one table, sales: how many pounds of fruit each grocery store sold and the price per pound in US dollars. Two items have no price yet — their price is NULL. Create the table in any practice database, for example in forum. If you have never run a query in the mysql client, start with Your First SQL Query in MySQL. The syntax is MySQL 8; almost every query also works unchanged in MySQL 5.7, and the differences are noted in the text.

CREATE TABLE sales (
    id      INT          NOT NULL AUTO_INCREMENT,
    shop    VARCHAR(50)  NOT NULL,
    product VARCHAR(50)  NOT NULL,
    lbs     INT          NOT NULL,
    price   DECIMAL(6,2) NULL,
    PRIMARY KEY (id)
);

INSERT INTO sales (shop, product, lbs, price) VALUES
    ('Acme',       'apples', 10, 1.50),
    ('Acme',       'pears',  14, 2.50),
    ('Acme',       'grapes', 20, NULL),
    ('Delta',      'apples', 20, 1.75),
    ('Delta',      'grapes', 34, 4.50),
    ('Delta',      'lemons', 34, NULL),
    ('Berry Hill', 'lemons', 14, 3.50),
    ('Berry Hill', 'apples', 54, 1.25);

How CREATE TABLE and a multi-row insert work is explained in CREATE TABLE: Creating a Table and Inserting Multiple Rows with One INSERT; why money goes into DECIMAL is covered in Fractional Numbers: DECIMAL, FLOAT, DOUBLE.

SELECT * FROM sales;
+----+------------+---------+-----+-------+
| id | shop       | product | lbs | price |
+----+------------+---------+-----+-------+
|  1 | Acme       | apples  |  10 |  1.50 |
|  2 | Acme       | pears   |  14 |  2.50 |
|  3 | Acme       | grapes  |  20 |  NULL |
|  4 | Delta      | apples  |  20 |  1.75 |
|  5 | Delta      | grapes  |  34 |  4.50 |
|  6 | Delta      | lemons  |  34 |  NULL |
|  7 | Berry Hill | lemons  |  14 |  3.50 |
|  8 | Berry Hill | apples  |  54 |  1.25 |
+----+------------+---------+-----+-------+
8 rows in set

Revenue for each item sold by Delta is the expression lbs * price right in the column list:

SELECT product, lbs, price, lbs * price
FROM sales
WHERE shop = 'Delta';
+---------+-----+-------+-------------+
| product | lbs | price | lbs * price |
+---------+-----+-------+-------------+
| apples  |  20 |  1.75 |       35.00 |
| grapes  |  34 |  4.50 |      153.00 |
| lemons  |  34 |  NULL |        NULL |
+---------+-----+-------+-------------+

Any arithmetic with NULL gives NULL: the lemons have no price, so they have no revenue either. When no table is needed at all, you can drop FROM: SELECT 2 + 3; returns 5. Built-in functions for the column list and conditions are covered in the module lesson on SQL functions.

SELECT * is for the console, not for Java code

In the mysql client, SELECT * is a quick way to look at a table. In application queries, list the columns explicitly: the server does not send data you do not need (such as a large TEXT column), and code that reads a ResultSet by column index (getString(2)) does not break when someone adds a new column to the table.

The LIKE Operator: Pattern Matching

The LIKE operator checks whether a string matches a pattern. A pattern has two wildcards: % matches any number of any characters, including none, and _ matches exactly one character.

Selecting from a table with LIKE:

SELECT id FROM users WHERE phone LIKE '%3';

This selects from users all values of the id field for which phone ends with the digit 3 (% stands for any number of any characters; LIKE checks whether a character string matches the given pattern).

LIKE is normally applied to string fields — CHAR, VARCHAR, TEXT. MySQL also accepts it for numbers: the number is implicitly converted to a string, and WHERE lbs LIKE '3%' on the sales table returns rows 5 and 6 (34 pounds).

The underscore (_) stands for any single character. For example, 'b_t' matches 'bat' or 'bit', but not 'brat'.

The percent sign (%) stands for a sequence of any number of characters. For example, '%p%t' matches 'put', 'posit' or 'opt', but not 'spite'.

Letter case does not matter in MySQL by default: LIKE compares strings by the same rules as =. A pattern with a lowercase “b” finds “Berry Hill”:

SELECT id, shop, product
FROM sales
WHERE shop LIKE 'b%';
+----+------------+---------+
| id | shop       | product |
+----+------------+---------+
|  7 | Berry Hill | lemons  |
|  8 | Berry Hill | apples  |
+----+------------+---------+

NOT LIKE selects the rows that do not match the pattern. To find a literal % or _, escape it with a backslash: LIKE '%50\%%' finds strings that contain “50%”. A pattern that starts with % cannot use an index, so on a large table such a query scans every row.

LIKE with a parameter in Java

With a PreparedStatement, the % sign is added to the parameter value, not to the query text: "SELECT id, shop FROM sales WHERE shop LIKE ?" and ps.setString(1, prefix + "%"). Writing LIKE '?%' does not work — the driver treats a question mark inside quotes as an ordinary character, not a parameter. If the search string comes from a user, escape % and _ in it; otherwise typing “%” finds everything.

SQL Aliases: Renaming Columns and Tables

SQL aliases are temporary names that can be used to rename tables and columns within a single query.

Selecting from a table with an alias:

SELECT persons.username FROM users AS persons
WHERE persons.username LIKE 'P%';

The keyword AS is optional:

The same query without AS:

SELECT persons.username FROM users persons
WHERE persons.username LIKE 'P%';

A column alias becomes the header in the result, which is handy for computed expressions:

SELECT s.product, s.lbs * s.price AS revenue
FROM sales AS s
WHERE s.shop = 'Delta';
+---------+---------+
| product | revenue |
+---------+---------+
| apples  |   35.00 |
| grapes  |  153.00 |
| lemons  |    NULL |
+---------+---------+

Once a table has an alias, its original name no longer works in that query:

SELECT sales.shop FROM sales AS s;
ERROR 1054 (42S22): Unknown column 'sales.shop' in 'field list'

Table aliases are required when the same table appears in a query twice, and they show up in almost every query with JOIN — joins have their own lesson in this module. If an alias contains a space, put it in backticks: AS `revenue, USD`. A column alias, however, is not visible in the WHERE clause of the same query — that is the error from the top of this lesson, and the reason is explained in SQL Query Execution Order.

ORDER BY: Sorting with ASC and DESC

ORDER BY sorts the result rows. Without it the row order is undefined: a table is usually read in primary key order, but MySQL does not guarantee that, and the order can change after an index is added.

Selecting from a table with ORDER BY:

SELECT username FROM users ORDER BY username;

The query selects the username field and sorts the result by it. ORDER BY sorts by one or more fields, including fields that are not listed after SELECT. The default is ascending order (ASC); to sort in reverse order, add DESC after the field:

Selecting from a table with ORDER BY and DESC:

SELECT username FROM users ORDER BY username DESC;

The direction is set separately for each column, and the next sort column only orders rows that have the same value in the previous one:

SELECT shop, product, lbs
FROM sales
ORDER BY lbs DESC, product;
+------------+---------+-----+
| shop       | product | lbs |
+------------+---------+-----+
| Berry Hill | apples  |  54 |
| Delta      | grapes  |  34 |
| Delta      | lemons  |  34 |
| Delta      | apples  |  20 |
| Acme       | grapes  |  20 |
| Berry Hill | lemons  |  14 |
| Acme       | pears   |  14 |
| Acme       | apples  |  10 |
+------------+---------+-----+

You can also sort by an alias from SELECT (ORDER BY revenue). MySQL accepts a column number too (ORDER BY 3), but when the column list changes, such a query silently starts sorting by a different column.

In ascending order MySQL puts NULLs first: ORDER BY price starts with rows 3 and 6. PostgreSQL and Oracle do the opposite. To push NULLs to the end in MySQL, write ORDER BY price IS NULL, price: the expression price IS NULL is 0 for rows with a price and 1 for rows without one.

LIMIT and OFFSET: First N Rows and Pagination

LIMIT n returns at most n rows of the result, and LIMIT n OFFSET m skips the first m rows before that. This is how you get the “top N” rows and split data into pages. Without ORDER BY there is no telling which rows come first, so LIMIT goes together with a sort.

-- the three largest sales
SELECT shop, product, lbs
FROM sales
ORDER BY lbs DESC, id
LIMIT 3;
+------------+---------+-----+
| shop       | product | lbs |
+------------+---------+-----+
| Berry Hill | apples  |  54 |
| Delta      | grapes  |  34 |
| Delta      | lemons  |  34 |
+------------+---------+-----+

The second page of three rows is LIMIT 3 OFFSET 3: rows 3, 4 and 2 (20, 20 and 14 pounds). MySQL also has the short form LIMIT 3, 3, where the first number is the offset and the second is the row count: LIMIT 10, 20 means 20 rows starting from the eleventh, not rows 10 through 20.

The id column in the sort is not redundant. Two rows have 34 pounds, and without a unique column in ORDER BY MySQL may return them in any order — with pagination, one row can then land on two pages while another lands on none. A large OFFSET is also slow: to skip 100,000 rows, the server still reads them. For long lists, use keyset pagination by the last key shown: WHERE id > 1200 ORDER BY id LIMIT 20.

Comparison Operators in WHERE

Comparison (relational) operators build the condition in the WHERE clause: only the rows for which the condition is true get into the result. Rows where the condition is false or NULL are discarded.

Operator Meaning Example Rows in sales
= Equal to lbs = 20 2
> Greater than lbs > 30 3
< Less than lbs < 14 1
>= Greater than or equal to lbs >= 34 3
<= Less than or equal to lbs <= 14 3
<> (in MySQL also !=) Not equal to shop <> 'Acme' 5
<=> NULL-safe equal (MySQL only): NULL <=> NULL is true price <=> NULL 2

Selecting from a table with a comparison operator:

SELECT username FROM users WHERE id>2;

The same technique on the sales table — all sales of 20 pounds or more:

SELECT id, shop, product, lbs
FROM sales
WHERE lbs >= 20;
+----+------------+---------+-----+
| id | shop       | product | lbs |
+----+------------+---------+-----+
|  3 | Acme       | grapes  |  20 |
|  4 | Delta      | apples  |  20 |
|  5 | Delta      | grapes  |  34 |
|  6 | Delta      | lemons  |  34 |
|  8 | Berry Hill | apples  |  54 |
+----+------------+---------+-----+

Strings go in single quotes, numbers without quotes. In MySQL 8 string comparison is case-insensitive by default (the utf8mb4_0900_ai_ci collation, where ci stands for case insensitive), so WHERE shop = 'acme' also finds the store “Acme”. In PostgreSQL the same condition finds nothing.

NULL Values: IS NULL and IS NOT NULL

NULL stands for a missing or unknown value and is handled differently from all other values.

You cannot test a value for NULL with the ordinary comparison operators (=, < or <>). Use the IS NULL and IS NOT NULL operators instead:

Selecting the rows with a NULL value:

SELECT * FROM users WHERE username IS NULL;

Here is what happens if you compare with = anyway:

SELECT id, product FROM sales WHERE price = NULL;
SELECT id, product FROM sales WHERE price IS NULL;
Empty set

+----+---------+
| id | product |
+----+---------+
|  3 | grapes  |
|  6 | lemons  |
+----+---------+

The first query ran without an error or a warning and found nothing: a comparison with NULL gives neither true nor false but NULL. For the same reason WHERE price < 3 returns rows 1, 2, 4 and 8, WHERE price >= 3 returns rows 5 and 7, and rows 3 and 6 are in neither result. If you also need the rows without a price, say so explicitly: WHERE price < 3 OR price IS NULL. To substitute a value for NULL, use the COALESCE(price, 0) function.

Boolean Operators AND, OR, NOT

The Boolean operators AND, OR and NOT combine several conditions into one. AND requires both conditions to hold, OR requires at least one, and NOT negates a condition.

Selecting from a table with Boolean operators:

SELECT username FROM users WHERE id>1 AND id<3;
SELECT username FROM users WHERE NOT username='Sidorov';

WHERE NOT username='Sidorov' is the same as WHERE username <> 'Sidorov'.

AND vs OR Precedence: What Will This Query Return?

You want the items from Acme and Delta that sold more than 25 pounds:

SELECT id, shop, product, lbs
FROM sales
WHERE shop = 'Acme' OR shop = 'Delta' AND lbs > 25;
+----+-------+---------+-----+
| id | shop  | product | lbs |
+----+-------+---------+-----+
|  1 | Acme  | apples  |  10 |
|  2 | Acme  | pears   |  14 |
|  3 | Acme  | grapes  |  20 |
|  5 | Delta | grapes  |  34 |
|  6 | Delta | lemons  |  34 |
+----+-------+---------+-----+

Every Acme row made it into the result, even the one with 10 pounds. AND is evaluated before OR (the precedence is NOT, then AND, then OR), so MySQL read the condition as shop = 'Acme' OR (shop = 'Delta' AND lbs > 25). Parentheses set the order you meant:

SELECT id, shop, product, lbs
FROM sales
WHERE (shop = 'Acme' OR shop = 'Delta') AND lbs > 25;
+----+-------+---------+-----+
| id | shop  | product | lbs |
+----+-------+---------+-----+
|  5 | Delta | grapes  |  34 |
|  6 | Delta | lemons  |  34 |
+----+-------+---------+-----+

When a condition mixes AND and OR, add parentheses every time, even where precedence would give the right result: whoever edits the query next should not have to remember the precedence table.

The IN Operator

The IN operator defines a set of values that a given value may or may not belong to.

For example, instead of

SELECT username FROM users WHERE username='Sidorov' OR username='Petrov';

you can write

SELECT username FROM users WHERE username IN ('Sidorov', 'Petrov');

On the sales table:

SELECT id, shop, product
FROM sales
WHERE product IN ('pears', 'lemons');
+----+------------+---------+
| id | shop       | product |
+----+------------+---------+
|  2 | Acme       | pears   |
|  6 | Delta      | lemons  |
|  7 | Berry Hill | lemons  |
+----+------------+---------+

NOT IN and NULL: Why the Result Is Empty

SELECT id FROM sales WHERE lbs NOT IN (10, 14);
SELECT id FROM sales WHERE lbs NOT IN (10, 14, NULL);
5 rows in set
Empty set

The first query returned rows 3, 4, 5, 6 and 8, the second one returned nothing. lbs NOT IN (10, 14, NULL) expands to lbs <> 10 AND lbs <> 14 AND lbs <> NULL, and the last comparison is never true. Nobody types NULL into the list by hand, but it arrives from a subquery (NOT IN (SELECT ...)), and the query suddenly stops returning anything. Exclude NULL in the subquery with IS NOT NULL or use NOT EXISTS.

The BETWEEN Operator

BETWEEN is used to compare against a range.

Selecting from a table with the BETWEEN operator:

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 4000;

which can be rewritten as:

SELECT * FROM employees WHERE salary >= 3000 AND salary <= 4000;

With BETWEEN the boundary values are included in the range, so the query also returns employees with a salary of exactly 3000 and 4000.

The same on sales — the bounds 14 and 20 pounds are in the result:

SELECT id, product, lbs
FROM sales
WHERE lbs BETWEEN 14 AND 20;
+----+---------+-----+
| id | product | lbs |
+----+---------+-----+
|  2 | pears   |  14 |
|  3 | grapes  |  20 |
|  4 | apples  |  20 |
|  7 | lemons  |  14 |
+----+---------+-----+

The lower bound goes first: lbs BETWEEN 20 AND 14 returns Empty set without any error. The negation is NOT BETWEEN ... AND ....

With dates, BETWEEN is easy to get wrong: for a DATETIME column, BETWEEN '2026-11-01' AND '2026-11-30' loses almost all of November 30, because '2026-11-30' means midnight. A monthly range is safer as a half-open interval: created_at >= '2026-11-01' AND created_at < '2026-12-01'.

SELECT DISTINCT: Unique Values

The DISTINCT operator tells the query to work only with the unique values of a column.

Selecting non-repeating values from the username column:

SELECT DISTINCT username FROM users;

On the sales table, eight rows shrink to four product names:

SELECT DISTINCT product FROM sales;
+---------+
| product |
+---------+
| apples  |
| pears   |
| grapes  |
| lemons  |
+---------+

DISTINCT does not guarantee the row order — add ORDER BY product for a predictable one. DISTINCT goes right after SELECT and applies to the whole result row, not to the first column: SELECT DISTINCT shop, product FROM sales; returns all 8 rows, because no store–product pair repeats. All NULLs count as one value for DISTINCT, so SELECT DISTINCT price FROM sales; returns 7 rows: six prices and one NULL.

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Aggregate functions perform a calculation on a set of values and return a single value. Without GROUP BY, all rows that pass WHERE form one set, and the query returns exactly one row.

Function What it computes NULL in the column On an empty set
COUNT(*) Counts the rows that satisfy the query condition Rows with NULL are counted 0
COUNT(column) Counts the column values that are not NULL Skipped 0
SUM Computes the arithmetic sum of all values in the column Skipped NULL
AVG Computes the arithmetic mean of all values Skipped and not counted in the divisor NULL
MAX Finds the largest of all selected values Skipped NULL
MIN Finds the smallest of all selected values Skipped NULL

Aggregate functions, except COUNT(*), ignore NULL values. COUNT(column) skips NULL too.

Using aggregate functions:

SELECT count(DISTINCT username) FROM users; -- number of distinct values in the username column
SELECT MAX(id) FROM users;
SELECT MIN(id) FROM users;

All the functions at once on the sales table:

SELECT COUNT(*)                AS all_rows,
       COUNT(price)            AS priced,
       COUNT(DISTINCT product) AS products,
       SUM(lbs)                AS total_lbs,
       AVG(price)              AS avg_price,
       MIN(price)              AS min_price,
       MAX(price)              AS max_price
FROM sales;
+----------+--------+----------+-----------+-----------+-----------+-----------+
| all_rows | priced | products | total_lbs | avg_price | min_price | max_price |
+----------+--------+----------+-----------+-----------+-----------+-----------+
|        8 |      6 |        4 |       200 |  2.500000 |      1.25 |      4.50 |
+----------+--------+----------+-----------+-----------+-----------+-----------+

avg_price is the price total of 15.00 divided by the 6 rows that have a price, not by all 8 (that would be 1.875). If a missing price should count as zero, write AVG(COALESCE(price, 0)).

And what do the functions return when no row matches the condition?

SELECT COUNT(*), SUM(lbs) FROM sales WHERE shop = 'Northgate';
+----------+----------+
| COUNT(*) | SUM(lbs) |
+----------+----------+
|        0 |     NULL |
+----------+----------+

SUM returned NULL, not 0. In Java, rs.getInt(2) returns 0 for such a value, and only rs.wasNull() or COALESCE(SUM(lbs), 0) in the query itself lets you tell “no sales” from “sold 0 pounds”.

GROUP BY: Grouping Rows

The GROUP BY clause combines the rows of a result by one or more columns. Its syntax is GROUP BY column_name. Aggregate functions in such a query are computed separately for each group, and the result has one row per group.

Suppose there is a table with the number of pounds of each kind of fruit a store sold (it is the sales table without the price column):

id shop product lbs
1 Acme apples 10
2 Acme pears 14
3 Acme grapes 20
4 Delta apples 20
5 Delta grapes 34
6 Delta lemons 34
7 Berry Hill lemons 14
8 Berry Hill apples 54

Using GROUP BY, find the number of pounds sold by each store:

SELECT shop, SUM(lbs) AS total_lbs
FROM sales GROUP BY shop;
shop total_lbs
Acme 44
Delta 88
Berry Hill 68

Check against the table: Acme 10 + 14 + 20 = 44, Delta 20 + 34 + 34 = 88, Berry Hill 14 + 54 = 68, 200 in total — the same as SUM(lbs) over the whole table. You can group by several columns (GROUP BY shop, product), and rows with NULL in a grouping column form one shared group.

The order of groups in this result is not guaranteed. MySQL 5.7 sorted the result by the GROUP BY columns on its own; since MySQL 8.0 there is no implicit sorting. Groups usually come in order of appearance, as above, but code that relied on the old behavior may list them differently after an upgrade. If you need an order, add ORDER BY shop.

ONLY_FULL_GROUP_BY and Error 1055

In a query with GROUP BY, the SELECT list may contain only the GROUP BY columns and aggregate functions. Here is what happens if you add product to it:

SELECT shop, product, SUM(lbs) FROM sales GROUP BY shop;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'forum.sales.product' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The Acme group has three different products, and it is unclear which one to show. The ONLY_FULL_GROUP_BY mode has been on by default since MySQL 5.7.5; older versions ran such a query and returned the product from an arbitrary row of the group. There are three fixes: add the column to GROUP BY, wrap it in an aggregate function (MAX(product), GROUP_CONCAT(product)), or explicitly accept any value with ANY_VALUE(product).

HAVING: Filtering Groups

The SQL HAVING clause sets a condition on the groups produced by GROUP BY: it checks the results of aggregate functions. WHERE selects rows before grouping and HAVING selects groups after it, so a condition like “the sum is greater than 60” can only go into HAVING.

Using SQL HAVING, list the stores that sold more than 60 pounds:

SELECT shop, SUM(lbs) AS total_lbs
FROM sales GROUP BY shop
HAVING SUM(lbs) > 60;
shop total_lbs
Delta 88
Berry Hill 68

MySQL also lets you use the alias in HAVING: HAVING total_lbs > 60. This is a MySQL extension; standard SQL and PostgreSQL require repeating the function, as in the example above.

WHERE and HAVING are often needed in the same query. How many pounds each store sold, not counting apples — and only the stores where those sales exceed 30 pounds:

SELECT shop, SUM(lbs) AS total_lbs
FROM sales
WHERE product <> 'apples'
GROUP BY shop
HAVING SUM(lbs) > 30
ORDER BY shop;
+-------+-----------+
| shop  | total_lbs |
+-------+-----------+
| Acme  |        34 |
| Delta |        68 |
+-------+-----------+

WHERE removed the three apple rows, GROUP BY computed the sums (Acme 34, Delta 68, Berry Hill 14), and HAVING dropped Berry Hill.

Aspect WHERE HAVING
What it filters Individual table rows Groups produced by GROUP BY
When it runs Before grouping After grouping
Aggregate functions in the condition Not allowed: ERROR 1111 (HY000): Invalid use of group function Allowed
Aliases from SELECT Not allowed: ERROR 1054 Allowed in MySQL, not in standard SQL
Performance Conditions on plain columns belong here: fewer rows reach grouping, and an index can be used Only conditions on aggregates

SQL Query Execution Order

The clauses of a SELECT query are logically processed not in the order they are written but in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. That is why an alias from SELECT is not visible in WHERE but works in ORDER BY, and why aggregate functions are allowed in HAVING but not in WHERE.

Step Clause What happens What is already available
1 FROM (and JOIN) Table rows are read; with a join, combinations of rows from several tables Table columns
2 WHERE Rows for which the condition is not true are discarded Columns; no aliases or aggregates yet
3 GROUP BY Rows are collected into groups Columns
4 HAVING Groups are discarded Aggregate functions
5 SELECT Expressions are evaluated, aliases are assigned Everything above
6 DISTINCT Duplicate result rows are removed Result columns
7 ORDER BY Rows are sorted Aliases from SELECT
8 LIMIT / OFFSET The requested slice of the result is cut out —

This is exactly where the query from the top of the lesson breaks. WHERE total_lbs > 60 runs at step 2, while the alias total_lbs appears only at step 5 — hence ERROR 1054. Replacing it with WHERE SUM(lbs) > 60 does not help: there are no groups yet at step 2, and MySQL replies ERROR 1111 (HY000): Invalid use of group function. A condition on a sum belongs in HAVING.

This is the logical order, which decides which names are visible in each clause. The optimizer may execute a query physically in a different way — for example, read rows already sorted through an index — but the result always matches the logical order. MySQL relaxes the standard in one direction: it lets you refer to SELECT aliases in GROUP BY and HAVING. In WHERE, aliases do not work in any database.

The result of a SELECT is a table too, so you can write it straight into another table with INSERT ... SELECT (see INSERT ... SELECT: Inserting Query Results) or combine data from several tables at the FROM step — that is the topic of the module lesson on SQL JOINs.

SELECT Gotchas and How to Fix Them

Some SELECT mistakes produce no error message at all: the query runs and returns a plausible but wrong result. The table lists the cases people get wrong most often, what MySQL 8 does in each one and the fix.

Situation What happens How to do it right
WHERE price = NULL Empty set with no error and no warning WHERE price IS NULL
a OR b AND c without parentheses Extra rows: AND is evaluated before OR (a OR b) AND c
NOT IN with a list or subquery that contains NULL Empty result Filter out NULL or use NOT EXISTS
A column alias in WHERE ERROR 1054: Unknown column ... in 'where clause' Repeat the expression in WHERE, or move a condition on an aggregate to HAVING
An aggregate function in WHERE ERROR 1111: Invalid use of group function Put the condition in HAVING
A column outside GROUP BY in the SELECT list ERROR 1055 (only_full_group_by); before MySQL 5.7.5 — a value from a random row Add it to GROUP BY or wrap it in an aggregate / ANY_VALUE()
Relying on the row order after GROUP BY Since MySQL 8.0 groups are not sorted An explicit ORDER BY
LIMIT without ORDER BY or with a non-unique sort Arbitrary rows; with pagination, rows repeat and go missing ORDER BY ending with a unique column such as id
LIMIT 10, 20 read as “rows 10 to 20” 20 rows are returned, starting from the 11th LIMIT 10 OFFSET 10 is easier to read
AVG(price) with missing prices Rows with NULL are in neither the sum nor the divisor AVG(COALESCE(price, 0)) if a missing value means zero
BETWEEN '2026-11-01' AND '2026-11-30' on a DATETIME Almost the whole last day is lost >= '2026-11-01' AND < '2026-12-01'

Frequently Asked Questions

Can you use HAVING without GROUP BY?

Yes. Without GROUP BY the whole result is one group, and HAVING decides whether to return it: SELECT SUM(lbs) FROM sales HAVING SUM(lbs) > 100; returns one row with 200, and with > 500 the result is empty. MySQL also accepts conditions on plain columns in HAVING, but they belong in WHERE. The difference is covered in the section HAVING.

How do I run a SELECT from Java and read the result?

Through JDBC: get a Connection, prepare the query with conn.prepareStatement("SELECT shop, lbs FROM sales WHERE lbs > ?"), set the parameter with ps.setInt(1, 20) and call executeQuery(). It returns a ResultSet: in a while (rs.next()) loop you read values by column name — rs.getString("shop"), rs.getInt("lbs"). Close all three objects with try-with-resources. Pass user input as parameters rather than concatenating strings — that is your protection against SQL injection.

Is there SELECT TOP in MySQL?

No. SELECT TOP 3 ... is Microsoft SQL Server syntax; in MySQL the equivalent is ... ORDER BY ... LIMIT 3. The standard form FETCH FIRST 3 ROWS ONLY works in PostgreSQL, Oracle 12c+ and SQL Server (together with OFFSET), but MySQL does not support it. Examples are in the section LIMIT and OFFSET.

How do I select a random row from a table?

The simplest way is SELECT * FROM sales ORDER BY RAND() LIMIT 1;. It works, but on every call MySQL generates a random number for every row and sorts them all, so on a table with hundreds of thousands of rows the query gets noticeably slow. On large tables, pick a random id between MIN(id) and MAX(id) and take the first row with id >= that value, or choose a random row in Java code from a preloaded list of keys.

What is the difference between COUNT(*), COUNT(1) and COUNT(id)?

COUNT(*) and COUNT(1) are equivalent in MySQL: both count rows, and the optimizer executes them the same way, so COUNT(1) is not faster. COUNT(column) counts only non-NULL values; for a NOT NULL column such as the primary key id the result equals COUNT(*), while for price in the practice table it is 6 instead of 8. The example is in the section Aggregate Functions.

Comments

Please log in or register to have a possibility to add comment.