SQL Aliases

Definition: An alias is a temporary name given to a table or a column in a query. It only exists for the duration of that specific query and does not change the actual names stored in the database structure.

Why: Beginner SQL tutorials introduce aliases because they solve two major problems: making output headers more readable and keeping complex join queries concise. As your queries grow longer, aliases become a "shorthand" that saves time and reduces typing errors.

1. Column Aliases

Use the AS keyword to rename a column header in your final result set. This is especially helpful when using aggregate functions like COUNT or AVG.

SELECT name AS student_name
FROM students;

Instead of a header that simply says "name," your result table will now clearly display "student_name."

2. Table Aliases

Table aliases are used to give a table a nickname. This is standard practice in JOIN operations, allowing you to refer to tables using a single letter instead of their full name.

SELECT s.name, e.course_id
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;

Explanation

• Readability: In the table alias example, s stands for students and e stands for enrollments. This makes the ON clause much easier to read.
• The AS Keyword: While AS is required for column aliases in many databases for clarity, it is often optional for table aliases. You can write FROM students s or FROM students AS s.
• Scope: Once you define a table alias in the FROM or JOIN clause, you must use that alias throughout the rest of the query (including in the SELECT and WHERE clauses).

Key Notes

• Handling Spaces: If you want your alias to contain spaces (e.g., AS "Student Name"), you must enclose it in double quotes or square brackets, depending on your database type.
• Self-Joins: Aliases are mandatory if you ever need to join a table to itself (a "Self-Join"), as they allow the database to distinguish between the two different roles the table is playing in that query.
• Calculated Fields: Aliases are essential for naming columns created by math operations, such as SELECT price * tax AS total_price.