SQL Aliases

An SQL alias is a temporary name given to a column or table using the AS keyword.

SELECT student_name AS name
FROM students;

Here, name is an alias for the student_name column.

Aliases are useful for:

• Making column names shorter.
• Making query results easier to understand.
• Giving names to calculated values.
• Making table references shorter.
• Making JOIN queries easier to read.

The AS keyword can be used to create a column alias.

SELECT
    student_name AS name
FROM students;

The result column will be displayed as name.

In MySQL, the AS keyword can usually be omitted for column aliases.

SELECT
    student_name name
FROM students;

This produces the same alias as:

SELECT
    student_name AS name
FROM students;

Aliases are especially useful when a query contains calculations.

SELECT
    price * quantity AS total_amount
FROM orders;

The calculated result will be displayed with the name total_amount.

An alias can make aggregate results easier to understand.

SELECT
    SUM(amount) AS total_payment
FROM payments;

The result column will be named total_payment.

We can give a meaningful name to a COUNT result.

SELECT
    COUNT(*) AS total_students
FROM students;

This makes the result easier to understand.

Aliases can also be used with AVG().

SELECT
    AVG(marks) AS average_marks
FROM students;

The calculated average is displayed as average_marks.

Use an alias to give a meaningful name to the minimum value.

SELECT
    MIN(marks) AS lowest_marks
FROM students;

The result column will be called lowest_marks.

Use an alias to name the maximum value.

SELECT
    MAX(marks) AS highest_marks
FROM students;

The result will be displayed as highest_marks.

Multiple columns can have different aliases in the same query.

SELECT
    student_name AS name,
    mobile AS phone,
    email AS email_address
FROM students;

Aliases are useful when using string functions.

SELECT
    UPPER(student_name) AS uppercase_name
FROM students;

The result column will be named uppercase_name.

Aliases can make date calculations easier to understand.

SELECT
    YEAR(admission_date) AS admission_year
FROM students;

The extracted year is displayed as admission_year.

A table can also have a temporary alias.

SELECT
    s.student_name
FROM students AS s;

Here, s is an alias for the students table.

The AS keyword can also be omitted when creating a table alias in MySQL.

SELECT
    s.student_name
FROM students s;

Here, s is the temporary table alias.

A table alias can be used when referencing columns in a WHERE condition.

SELECT
    s.student_name,
    s.marks
FROM students AS s
WHERE s.marks >= 60;

This is especially useful when working with multiple tables.

A column alias can be used in ORDER BY.

SELECT
    student_name AS name,
    marks AS score
FROM students
ORDER BY score DESC;

The students are sorted using the alias score.

An alias can be used to make grouped results easier to understand.

SELECT
    course AS course_name,
    COUNT(*) AS total_students
FROM students
GROUP BY course;

In MySQL, a SELECT alias can be referenced in a HAVING clause.

SELECT
    course,
    COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING total_students > 5;

This returns courses having more than five students.

Table aliases make JOIN queries shorter and easier to read.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id;

Here, s represents students and c represents courses.

Each table can have its own alias.

SELECT
    s.student_name,
    c.course_name,
    p.amount
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
INNER JOIN payments AS p
ON s.id = p.student_id;

Aliases are useful when combining multiple columns with CONCAT().

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

The combined name will be displayed as full_name.

A CASE expression can be given a meaningful alias.

SELECT
    student_name,
    CASE
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS result
FROM students;

Here, result is the alias of the CASE expression.

Aliases are very useful for mathematical calculations.

SELECT
    total_fee - paid_fee AS pending_fee
FROM students;

The calculated value is displayed as pending_fee.

We can give a name to a calculated percentage.

SELECT
    student_name,
    (paid_fee / total_fee) * 100 AS paid_percentage
FROM students;

The calculated percentage is displayed as paid_percentage.

A subquery result can also be given an alias.

SELECT
    student_name,
    (SELECT AVG(marks) FROM students) AS average_marks
FROM students;

The subquery result is displayed with the alias average_marks.

Meaningful aliases make reports easier to understand.

SELECT
    COUNT(*) AS total_students,
    AVG(marks) AS average_marks,
    MAX(marks) AS highest_marks,
    MIN(marks) AS lowest_marks
FROM students;

Each calculated value has a clear name.

An alias is temporary. It does not change the actual column name in the database.

SELECT
    student_name AS name
FROM students;

The original column is still called student_name. Only the query result uses name.

Aliases can make a student report much easier to understand.

SELECT
    s.student_name AS student,
    s.mobile AS phone,
    s.total_fee AS total_fee,
    s.paid_fee AS paid_fee,
    s.total_fee - s.paid_fee AS pending_fee
FROM students AS s;

This query creates readable names for the student information and calculated pending fee.

The following example combines table aliases, column aliases, calculations, and functions.

SELECT
    s.student_name AS student,
    c.course_name AS course,
    YEAR(s.admission_date) AS admission_year,
    s.total_fee AS total_fee,
    s.paid_fee AS paid_fee,
    s.total_fee - s.paid_fee AS pending_fee,
    CASE
        WHEN s.paid_fee >= s.total_fee THEN 'Paid'
        WHEN s.paid_fee > 0 THEN 'Partially Paid'
        ELSE 'Pending'
    END AS fee_status
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id;

This is a practical example of using aliases to create a readable student fee report.