An alias is a temporary name assigned to a table or a column in SQL. It is often created using the keyword AS. Aliases are not stored in the database and exist only during the execution of the query.

• Column Alias: Provides a more descriptive or user-friendly name for a column.
• Table Alias: Shortens the table name, making complex queries easier to read and write.

Why Use Aliases?

1. Improve Readability:

   • Queries become more understandable, especially for long column or table names.
   • Helpful in cases where column names are unclear or non-descriptive.

2. Simplify Complex Queries:

   • Shorten table names when joining multiple tables.
   • Avoid repetitive typing and reduce errors.

3. Clarify Calculated Columns:

   • Assign meaningful names to calculated or derived columns.

4. Handle Ambiguity:

   • Resolve naming conflicts when working with columns from multiple tables with similar names.

How to Use AS for Aliases

1. Column Aliases

You can use AS to rename columns in the result set for better readability.

Syntax:

sql code

SELECT column_name AS alias_name

FROM table_name;

Example: Suppose you have a Sales table with columns sales_amount and profit_margin. To rename them for clarity:

sql code

SELECT sales_amount AS "Total Sales", profit_margin AS "Profit Percentage"

FROM Sales;

Output:

2. Table Aliases

Table aliases are used to rename tables temporarily, making them easier to reference, especially in joins.

Syntax:

sql code

SELECT alias_name.column_name

FROM table_name AS alias_name;

Example: Suppose you are joining two tables, Orders and Customers, which both have a column named customer_id. You can use aliases to distinguish between them:

sql code

SELECT o.order_id, c.customer_name

FROM Orders AS o

JOIN Customers AS c ON o.customer_id = c.customer_id;

Output:

Best Practices for Using Aliases

1. Use Meaningful Names:

   • Choose aliases that represent the purpose of the column or table. For example, use c for Customers instead of random names like x.

2. Avoid Overusing:

   • Use aliases only when they enhance readability. Don’t alias every column unnecessarily.

3. Double-Quote or Bracket Aliases with Spaces:

   • If the alias includes spaces or special characters, enclose it in double quotes (") or square brackets ([]).

   Example:

   sql code

   SELECT sales_amount AS "Total Sales"

4. FROM Sales;

5. Be Consistent:

   • Use the same aliases throughout the query for consistency.

Common Scenarios for Using Aliases

1. Renaming Calculated Columns

When performing calculations or aggregations, aliases make results easier to interpret.

Example:

sql code

SELECT sales_amount * 0.1 AS "Tax Amount"

FROM Sales;

2. Shortening Table Names in Joins

Aliases reduce repetition when working with long table names.

Example:

sql code

SELECT p.product_name, o.order_date

FROM Products AS p

JOIN Orders AS o ON p.product_id = o.product_id;

3. Resolving Column Name Conflicts

In a self-join, where a table is joined to itself, aliases distinguish between the two instances.

Example:

sql code

SELECT e1.employee_name AS "Manager", e2.employee_name AS "Subordinate"

FROM Employees AS e1

JOIN Employees AS e2 ON e1.employee_id = e2.manager_id;

Advantages of Using Aliases

• Enhances query clarity and reduces ambiguity.
• Allows dynamic renaming of calculated fields and complex expressions.
• Simplifies query-writing in multi-table joins.
• Creates user-friendly outputs for reports and dashboards.

Hands-On Exercise

Dataset:

Task:

Write a query to:

1. Display the order_id as "Order ID" and customer_name as "Customer".
2. Use table aliases to join the tables.

Solution:

sql code

SELECT o.order_id AS "Order ID", c.customer_name AS "Customer"

FROM Orders AS o

JOIN Customers AS c ON o.customer_id = c.customer_id;

Output:

Using aliases effectively will make your queries more readable and easier to maintain, which is essential for real-world data analysis and collaboration.