VMware ESXi and vSphere Cluster Management

Advanced SELECT Statements in MySQL

Learn MySQL SELECT queries with COUNT(), DISTINCT, WHERE conditions, comparison operators, and practical examples for counting and filtering table data.

A basic SELECT statement retrieves data from a table. You can extend it with clauses and functions to control which rows and columns are returned, remove repeated output values, or summarize many rows into one result.

This lesson uses a sample table named testtb. It represents people and contains a first name, surname, and birth year.

Sample table and query goals

Before using advanced features, inspect the complete table:

SELECT * FROM testtb;

Different SELECT queries answer different questions:

• Full rows: SELECT * FROM testtb; returns every selected column for each matching record.
• Individual columns: SELECT name FROM testtb; returns only the name column, including repeated values.
• Unique values: SELECT DISTINCT name FROM testtb; returns each selected name once.
• Summary values: SELECT COUNT(*) FROM testtb; returns a number summarizing the rows.

Counting rows with COUNT()

COUNT() is an aggregate function. An aggregate function summarizes multiple rows into a value instead of returning ordinary table rows. COUNT(*) counts every row included by the query.

SELECT COUNT(*) FROM testtb;

This query returns one summary row containing the total number of records. With the sample data, the value is 4. A query without grouping produces one count result for the whole filtered set.

You can count only rows that satisfy a WHERE condition:

SELECT COUNT(*)
FROM testtb
WHERE year > 1980;

The result is the number of people whose year is greater than 1980. The WHERE clause is evaluated for each row before that row is included in the count.

COUNT(*) and COUNT(column_name)

NULL means a missing or unknown value. If the goal is to count every matching row, use COUNT(*):

SELECT COUNT(*) FROM testtb WHERE year > 1980;

Removing duplicate result values with DISTINCT

A table can contain repeated values because different records can share the same name. A normal query shows every selected value:

SELECT name FROM testtb;

With the sample data, John appears twice. Use DISTINCT to return each different value once:

SELECT DISTINCT name FROM testtb;

DISTINCT changes only the query result. It does not delete, merge, or modify duplicate records in testtb.

DISTINCT with more than one column

DISTINCT applies to the complete combination of selected columns. It does not make each column independently unique.

SELECT DISTINCT name, surname
FROM testtb;

This returns unique name-and-surname pairs. Two rows with the same name but different surnames are different combinations, so both can appear. To deduplicate names specifically, select only name.

Filtering rows with WHERE

WHERE restricts a query to rows for which a condition evaluates as true. A condition is an expression that tests a row against a value or another expression.

SELECT column_name
FROM table_name
WHERE column_name operator value;

For example, find records whose name is John:

SELECT *
FROM testtb
WHERE name = 'John';

Text values are string literals and should be enclosed in single quotes. The query returns the rows whose name value equals John.

Numeric values normally do not need quotes. To find years after 1980:

SELECT *
FROM testtb
WHERE year > 1980;

Conceptually, MySQL examines each row, tests the WHERE condition, and returns only rows that pass. A false or unknown condition excludes the row.

Comparison operators and values

A comparison operator compares two values. These are the common operators used in WHERE conditions:

• Use single quotes around text, such as 'John' or 'Smith'.
• Numeric values normally appear without quotes, such as 1980.
• Dates are commonly supplied as quoted values in MySQL, such as '2026-08-18'.

Use IS NULL rather than = NULL when testing for missing values. NULL has special comparison behavior and is not equal to an ordinary value.

Combining COUNT(), DISTINCT, and WHERE

These features can be combined to answer practical questions. For example, count only people born after 1980:

SELECT COUNT(*)
FROM testtb
WHERE year > 1980;

Or list unique names among those matching records:

SELECT DISTINCT name
FROM testtb
WHERE year > 1980;

A useful conceptual reading order is:

1. Choose the columns or expression to return.
2. Choose the source table with FROM.
3. Filter individual rows with WHERE.
4. If DISTINCT is present, remove repeated selected values or repeated selected-column combinations from the resulting output.

SQL is written with SELECT first, but thinking about the row filter before examining the final output helps explain why both COUNT(*) and DISTINCT operate on the matching set.

Troubleshooting common mistakes

Text does not match or produces an error

Check that the text literal is enclosed in single quotes:

SELECT * FROM testtb WHERE name = 'John';

Also verify the exact stored value, including spelling, whitespace, and the behavior of the database collation.

DISTINCT does not remove an expected duplicate

When multiple columns are selected, MySQL compares the complete selected combination. If the names are equal but the surnames differ, the two result rows are not duplicates for that query. Select only the column that should be deduplicated when appropriate.

COUNT(column_name) is lower than the table row count

The specified column may contain NULL values. Use COUNT(*) to count every matching row, or use COUNT(column_name) intentionally when you want only non-NULL values.

WHERE returns no rows

Inspect the source data with a basic SELECT and verify the column name, exact value, capitalization or collation behavior, whitespace, data type, and comparison operator. For example, year > 1980 excludes 1980 itself; use year >= 1980 to include it.

DISTINCT does not delete records

DISTINCT affects only the returned result set. It is not a data-modification operation. Removing rows would require a carefully controlled DELETE statement, which is a separate topic.

Exam-relevant summary

• SELECT retrieves data from a table.
• WHERE restricts output to rows that satisfy a condition.
• COUNT(*) returns one numeric total for all included rows.
• COUNT(column_name) excludes NULL values in that column.
• DISTINCT removes repeated values or repeated combinations from query output, not from the table.
• Use quoted string literals such as 'John'; numeric values such as 1980 normally do not require quotes.
• Remember the difference between > and >=, and between < and <=.

For the next steps, related subjects include sorting with ORDER BY, limiting results with LIMIT, combining conditions with AND and OR, and grouping rows with GROUP BY.