SELECT Command

What SELECT Does

SELECT asks MySQL to produce a result set. It can read stored columns, calculate expressions, combine tables, filter rows, form groups and sort the final output. A basic SELECT is read-only: it does not modify the rows it reads.

The smallest valid SELECT does not even need a table:

SELECT 2 + 3 AS answer,
       CURRENT_DATE AS today;

For table data, the common shape is SELECT columns FROM table.

SELECT Syntax and Choosing Columns

SELECT student_id, full_name, marks
FROM students
ORDER BY student_id;

Name only the columns the user or application needs. Column order in the result follows the select list, not necessarily the table definition.

SELECT *
FROM students;

* requests every column. It is useful while exploring a small table, but explicit columns are safer for production APIs and reports because they limit transfer, protect stable output contracts and reveal intent during code review.

Aliases and Calculated Expressions

A SELECT item can be a column, constant, function or expression. AS gives the output a readable label; it does not rename the stored column.

SELECT
  full_name AS student_name,
  marks,
  ROUND(marks / 100 * 5, 2) AS score_out_of_5
FROM students
ORDER BY student_id;

When an expression uses NULL, its result is often NULL. Decide whether missing marks should remain unknown or be replaced for a particular report using a function such as COALESCE; never silently convert missing results into zero without a business rule.

Return Unique Values with DISTINCT

SELECT DISTINCT class_name
FROM students
ORDER BY class_name;

DISTINCT applies to the complete combination of selected expressions. In SELECT DISTINCT class_name, status, rows are duplicates only when both values match. It is not a substitute for fixing unintended duplicate data in a table.

Filter, Sort and Limit the Result

SELECT student_id, full_name, marks
FROM students
WHERE status = 'Active' AND marks >= 80
ORDER BY marks DESC, student_id ASC
LIMIT 2;

• WHERE keeps only matching rows.
• ORDER BY marks DESC places higher marks first.
• student_id ASC breaks ties deterministically.
• LIMIT 2 returns at most two rows after filtering and ordering.

Without ORDER BY, row order is not guaranteed. An index, new data, execution plan or server version can change the observed order. Read the dedicated WHERE and operators lesson for ranges, lists, patterns and NULL tests.

Written Clause Order and Conceptual Processing

Write the major clauses in this order:

SELECT [DISTINCT] select_list
FROM table_source
WHERE row_condition
GROUP BY grouping_columns
HAVING group_condition
ORDER BY sort_expressions
LIMIT row_count;

A useful conceptual model is: FROM identifies rows, WHERE filters them, GROUP BY forms groups, HAVING filters groups, SELECT produces expressions, DISTINCT removes duplicate result rows, ORDER BY sorts and LIMIT trims the output. The optimizer may physically execute work differently while preserving SQL semantics.

Best Practices and Practice

• Return only needed columns and rows.
• Qualify columns with table aliases when a join could make names ambiguous.
• Use meaningful aliases for calculated fields.
• Add deterministic ORDER BY before LIMIT or pagination.
• Never insert untrusted text directly into SQL; bind it with prepared statements.
• Use EXPLAIN and indexes after correctness is established and a real performance problem is measured.

1. List active students alphabetically by full name.
2. Show the three highest marks, breaking ties by student_id.
3. Return each class once using DISTINCT.
4. Create an alias named percentage for marks and explain whether it changes the table.
5. Predict the result before running every query.

Quick Summary

• SELECT creates a result set without changing stored rows.
• Explicit column lists are more stable than SELECT *.
• Aliases label output; expressions calculate output values.
• DISTINCT removes duplicate selected combinations.
• WHERE filters, ORDER BY sorts and LIMIT reduces count; deterministic limits need ordering.

Official References

• SELECT Statement — MySQL 8.4
• SELECT ... INTO Statement
• Problems with Column Aliases
• LIMIT Query Optimization

References reviewed on 14 August 2026. Results assume the exact four-row learning dataset shown above.