Understanding LIMIT and Aliasing in MySQL

Introduction

In this lesson we explore two fundamental MySQL features: the LIMIT clause, which controls how many rows are returned, and aliasing, which lets you rename columns for readability and reuse.

Using LIMIT

• Basic syntax: SELECT ... FROM table LIMIT n;
• Example: LIMIT 3 returns the first three rows of the result set.
• Demonstration shows the output containing employees Leslie, Tom, and April when limiting to three rows.

Combining LIMIT with ORDER BY

• By ordering the data first, you can pick the top or bottom records based on a specific column.
• Example: ORDER BY age DESC LIMIT 3 returns the three oldest employees.
• The query first sorts by age in descending order, then LIMIT extracts the first three rows of that sorted list.

OFFSET with LIMIT (Using a Comma)

• Syntax: LIMIT offset, row_count;
• The first number is the starting position (zero‑based), the second is how many rows to return.
• Example: LIMIT 2, 1 starts at the third row (position 2) and returns one row, yielding Leslie Knope.
• This technique is useful for pagination or retrieving a specific row.

Aliasing Columns

• The AS keyword renames a column in the result set.
• Example without alias: sql SELECT gender, AVG(age) FROM employee_demographics GROUP BY gender; Output column appears as AVG(age).
• With alias: sql SELECT gender, AVG(age) AS average_age FROM employee_demographics GROUP BY gender; The column now appears as average_age.
• The AS keyword is optional; AVG(age) average_age works the same because MySQL implicitly treats the space as an alias indicator.

Using Aliases in HAVING Clauses

• After aliasing, you can reference the new column name in other parts of the query, such as HAVING.
• Example: sql SELECT gender, AVG(age) AS average_age FROM employee_demographics GROUP BY gender HAVING average_age > 40; This filters groups where the average age exceeds 40, using the alias instead of repeating the aggregate function.

Summary of Key Points

• LIMIT n restricts output to n rows.
• Combine ORDER BY with LIMIT to fetch top/bottom records based on a criterion.
• Use LIMIT offset, count for pagination or to start at a specific row.
• AS creates readable column names; it is optional but improves clarity.
• Aliased columns can be referenced in HAVING, WHERE, or other clauses, simplifying complex queries.

Next Steps

The lesson concludes the beginner MySQL series. The upcoming intermediate series will cover joins, unions, CASE statements, subqueries, and window functions.

LIMIT lets you control result size and position, while aliasing makes column names clearer and reusable throughout a query—together they enable concise, readable, and powerful SQL statements.