MySQL: A Detailed Explanation of the SELECT Syntax for…

Last updated: 2026-08-26

SELECT is the most frequently used statement in SQL—almost all data retrieval begins with it.

This lesson provides a systematic explanation of the core syntax and common operations of the SELECT statement.

graph TB
    A[SELECT Inquiry Process] --> B[FROM<br/>Specify a data source]
    B --> C[WHERE<br/>Conditional Filtering]
    C --> D[GROUP BY<br/>Grouping]
    D --> E[HAVING<br/>Filter Groups]
    E --> F[SELECT<br/>Select a field]
    F --> G[DISTINCT<br/>Remove duplicates]
    G --> H[ORDER BY<br/>Sort]
    H --> I[LIMIT<br/>Pagination]

1. What You'll Learn

SELECT: Field Selection (All/Specified/Alias)
WHERE clause filtering
DISTINCT Query to Remove Duplicates
ORDER BY sorting
LIMIT Pagination

2. A True Story Behind a Report

(1) Pain Point: There’s too much data to go through

A data analyst needs to identify the "10 customers with the highest order amounts last month" from 100,000 orders.

Manually scrolling through pages? Filtering in Excel? Neither is very efficient.

(2) Solution for SELECT

SQL

SELECT 
    customer_name,
    SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01'
GROUP BY customer_name
ORDER BY total_spent DESC
LIMIT 10;

Benefits: A single SQL query returns results in seconds.

3. Basic SELECT Syntax

▶ Example: Query all fields

SQL

-- Query all fields（Use with caution in production environments）
SELECT * FROM employees;

-- Query a Specific Field（Recommendations）
SELECT first_name, last_name, salary FROM employees;

▶ Try it Yourself

TEXT 📖 Display only

Output displayed

▶ Example: Field Aliases

TEXT 📖 Display only

-----------+-----------+-------
first_name | last_name | salary
-----------+-----------+-------
Alice      | Alice     | 25.00 
Bob        | Bob       | 50.00 
Charlie    | Charlie   | 75.00 
-----------+-----------+-------
3 rows in set

----------------------+--------------------
first_name first_name | last_name last_name
----------------------+--------------------
Alice                 | Alice              
Bob                   | Bob                
Charlie               | Charlie            
----------------------+--------------------
3 rows in set

------------
'First Name'
------------
Alice       
Bob         
Charlie     
------------
3 rows in set

SQL

-- AS Set Aliases for Keywords
SELECT 
    first_name AS first_name,
    last_name AS last_name,
    salary AS salary
FROM employees;

-- (Omit) AS (same result)
SELECT first_name first_name, last_name last_name FROM employees;

-- If an alias contains spaces, it must be enclosed in quotation marks.
SELECT first_name AS 'First Name' FROM employees;

TEXT 📖 Display only

+--------+--------+--------+
| first_name | last_name | salary |
+--------+--------+--------+
| John   | Smith  | 5000   |
| Jane   | Doe    | 6000   |
+--------+--------+--------+

TEXT 📖 Display only

-----------+--------+--------------
first_name | salary | annual_salary
-----------+--------+--------------
Alice      | 25.00  | 25.00        
Bob        | 50.00  | 50.00        
Charlie    | 75.00  | 75.00        
-----------+--------+--------------
3 rows in set

------------------+---------+----------
CONCAT(first_name | ' '     | full_name
------------------+---------+----------
Alice             | value_1 | Alice    
Bob               | value_2 | Bob      
Charlie           | value_3 | Charlie  
------------------+---------+----------
3 rows in set

▶ Example: Expression Calculation

SQL

-- Calculate Annual Salary
SELECT 
    first_name,
    salary,
    salary * 12 AS annual_salary
FROM employees;

-- String Concatenation
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

▶ Try it Yourself

TEXT 📖 Display only

Output displayed

4. WHERE Condition Filtering

▶ Example: Basic Conditions

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

SQL

-- Equal Value Query
SELECT * FROM employees WHERE department = 'Engineering';

-- Non-equality query
SELECT * FROM employees WHERE salary != 5000;

-- Range Query
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 8000;

-- List Query
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing', 'HR');

-- Fuzzy Search
SELECT * FROM employees WHERE first_name LIKE 'J%';

-- NULL Search
SELECT * FROM employees WHERE manager_id IS NULL;

TEXT 📖 Display only

Output displayed

▶ Example: Combined Conditions

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

SQL

-- AND Combination
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 6000;

-- OR Combination
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing';

-- Mixed Combinations（Use parentheses to specify precedence）
SELECT * FROM employees 
WHERE (department = 'Sales' OR department = 'Marketing')
  AND salary > 5000;

-- NOT Negate
SELECT * FROM employees WHERE NOT (department = 'HR');

TEXT 📖 Display only

Output displayed

5. DISTINCT Dedup

▶ Example: Duplicate Removal Query

SQL

-- Single-Field Duplicate Removal
SELECT DISTINCT department FROM employees;

-- Multi-field combination dedup
SELECT DISTINCT department, job_title FROM employees;

-- Count the number of unique entries
SELECT COUNT(DISTINCT department) AS dept_count FROM employees;

▶ Try it Yourself

TEXT 📖 Display only

+-------------+
| department  |
+-------------+
| Engineering |
| Sales       |
| Marketing   |
| HR          |
+-------------+

6. ORDER BY Sorting

7. LIMIT Pagination

8. Complete Query Examples

❓ FAQ

Q What is the difference between WHERE and HAVING?

A WHERE filters rows before grouping, while HAVING filters groups after grouping. WHERE cannot be used with aggregate functions, but HAVING can.

Q What is the sorting priority when using ORDER BY with multiple fields?

A The results are sorted first by the first field; if the first field is tied, they are then sorted by the second field.

Q What should I do if performance is poor when the LIMIT offset is large?

A Large offsets (LIMIT 100000, 10) result in very poor performance. Switch to cursor pagination: WHERE id > last_id LIMIT 10.

Q Does DISTINCT affect performance?

A DISTINCT requires sorting to remove duplicates, so it can be slow when dealing with large datasets. If you don't need to remove duplicates, don't use DISTINCT.

📖 Summary

SELECT retrieves data; * retrieves all fields—it is recommended to specify field names
WHERE filter condition; supports = / != / < / > / BETWEEN / LIKE / IN / IS NULL
DISTINCT removes duplicates; it can be applied to a single field or a combination of fields
ORDER BY: Sorting; ASC = ascending (default), DESC = descending
LIMIT pagination, LIMIT offset, count or LIMIT count OFFSET offset
Optimize pagination with large offsets using cursor pagination

📝 Exercises

Basic Question (Difficulty ⭐): Query the employees table for employees whose salary is greater than 5,000, sort the results in descending order by salary, and display only the first 5 rows.
Advanced Problem (Difficulty ⭐⭐): Query the orders table for orders from 2026, calculate the number of orders and total amount by month, and sort the results by month.
Challenge (Difficulty: ⭐⭐⭐): Implement a paginated query that returns 20 records per page. Query the data on page 5, and compare the performance differences between using a large LIMIT offset and cursor-based pagination.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

MySQL: A Detailed Explanation of the SELECT Syntax for…

1. What You'll Learn

2. A True Story Behind a Report

(1) Pain Point: There’s too much data to go through

(2) Solution for SELECT

3. Basic SELECT Syntax

▶ Example: Query all fields

▶ Example: Field Aliases

▶ Example: Expression Calculation

4. WHERE Condition Filtering

▶ Example: Basic Conditions

▶ Example: Combined Conditions

5. DISTINCT Dedup

▶ Example: Duplicate Removal Query

6. ORDER BY Sorting

7. LIMIT Pagination

8. Complete Query Examples

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好