Using Aliases for Table and Column Names
In SQL database management systems, the alias feature is used to provide a temporary table name to a user-defined database table or column during database table queries. Table alias features make SQL database table queries more readable, detailed, and easier to understand. Alias features help database users deal with complex table queries by shortening the names of long database tables.
There are two types of aliases in SQL database management systems.
- Database table alias – This provides a temporary table name for the database table using the alias feature.
- Table column alias – This provides a temporary name for the table column in the result output of the database table.
Database Table Alias Features.
A database table alias is a simple method to represent the original long name of a table in a table query with a small single character. Table aliases are used when joining multiple database tables with long names, making the database table query more readable and simplifying and making the table query more descriptive by shortening the long table names.
Syntax of Database Table Alias.
SELECT column_name(s)
FROM table_name AS alias_name;
Elements of Database Table Alias.
- table_name – This is the original table name of the alias in the database table.
- alias_name – This is a temporary table alias name in the existing table, which the database user assigns to the table as an alias. This is a small user-defined table alias name.
Example of Table Alias in SQL.
- Let’s analyze the Employee and Department tables to understand the alias feature in database tables.
Detailed information about the employee table.
employee_id emp_name department_id
101 Siddhi deora 322
201 Harry deora 111
301 Bhavishi deora 798
Detailed information about the department table.
department_id department_name
290 Marketing
703 Development
408 Design
Without alias features in these tables, a database user could create a query to display employee names and their department names like this:
SELECT employee.emp_name, department.department_name
FROM employee, department
WHERE employee.department_id = department.department_id;
This is a proper method, but since the employee and department tables have long and detailed names, the database user can improve and shorten it using alias features.
Here’s how the user can create the table query with alias features.
SELECT e.emp_name, d.department_name
FROM employee AS e
JOIN department AS d ON e.department_id = d.department_id;
Alias features in database tables.
- Here, ‘e’ is created as an alias name for the employee table.
- Here, ‘d’ is created as an alias name for the department table.
Advantages of aliases in database tables.
- Alias features represent database table query names in a shorter form, especially when the database user is using multiple join queries simultaneously.
- It makes the database table query more readable.
- It avoids repeating long names in the database table query.
Table Column Alias Features.
In SQL database tables, a column alias is a temporary user-defined table column name provided to a column in the result output of a table query. This is helpful when the database user wants an easier or more readable alternative for the column name in the table output, especially when the database user is applying table functions or when a table column name is long or complex.
Syntax for table column aliases.
SELECT column_name AS alias_name
FROM table_name;
Elements of Table Column Alias.
- column_name – This is the original name of the column in the database table.
- alias_name – This is a temporary table column alias name provided to the original table column in the result output.
Note that the AS keyword is optional for the database user; therefore, the database user can create it without it as well.
SELECT column_name alias_name
FROM table_name;
Example of column aliases in an SQL database table.
Let’s modify the previous database table query and provide an alias name to the table column name.
SELECT e.employee_name AS emp_name, d.department_name AS dept_name
FROM employee AS e
JOIN department AS d ON e.department_id = d.department_id;
Here, in the Table Column Alias.
- emp_name is the alias name for the employee_name column from the employee table.
- Similarly, dept_name is an alias name for the department_name column from the department table.
- In the result output, instead of the original table column names, the column names emp_name and dept_name aliases will be displayed.
Result of Employee and Department table column aliases.
emp_name dept_name
Siddhi deora Marketing
Harry deora Development
Bhavishi deora Design
Advantages of Column Aliases in SQL Databases.
- This makes the result output of database tables more readable and easier to understand, especially when database users are applying aggregate functions to the table.
- It helps in formatting the table output by providing more meaningful names to the database table columns, especially when using table expressions, functions, or computed values.
Using Aliases in Complex Database Table Queries.
Alias features in database tables are especially useful when dealing with more complex database table queries that include multiple table joins, table subqueries, or aggregated data.
Example of a complex query using aliases.
Here, the database user is analyzing three tables simultaneously: Orders, Customers, and Products.
Information of the Orders database table.
order_id customer_id product_id quantity
111 89787 90 3
444 42342 10 2
Information of the Customers database table.
customer_id customer_name
101 Siddhi Deora
201 Harry Deora
Information of the Products database table.
product_id product_name price
01 HP Laptop 83000
02 MacBook Pro 120000
Here, the database user wants to generate a report based on the existing database tables with the customer’s name, product name, quantity ordered, and the total price for each order.
Orders, Customers, and Products table query with aliases.
SELECT c.customer_name AS customer, p.product_name AS product,
o.quantity, (o.quantity * p.price) AS total_price
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
JOIN products AS p ON o.product_id = p.product_id;
Here, for the Orders, Customers, and Products tables.
- Here, o is a user-defined table alias name for the Orders table.
- Here, c is a user-defined table alias name for the Customers table.
- Here, p is a user-defined table alias name for the Products table.
Here, customer_name and product_name are defined as aliases as customer and product.
Where quantity * price is defined as an alias as total_price.
Result of the Orders, Customers, and Products tables.
customer product quantity total_price
Siddhi deora Hp Laptop 3 249000
Harry deora Mackbook Pro 2 240000
Explanation of the Orders, Customers, and Products table query.
- Here, the column names customer_name and product_name have been modified to customer and product to make them more readable.
- Similarly, the expression quantity * price is defined as an alias as total_price to display the calculated field in a clear order.
Conclusion of Using Aliases for Table and Column Names.
- Using alias names for tables and columns in SQL databases improves table queries, making them more readable, descriptive, and easier to maintain. This is especially useful when database users are using long table and column names.
- For example, when performing join operations on multiple tables in a database.
- When applying numeric table functions to calculated or aggregated columns in a database table.
- It is also helpful when creating subqueries in a database table.
- Table and column aliases make complex table queries more detailed and descriptive, and ensure that the table output is easy to understand, allowing for the creation of clear, human-readable names for tables and columns.
