MySQL Aliases

MySQL server is an open-source relational database management system that is a major support for web-based applications. Databases and related tables are the main components of many websites and applications as the data is stored and exchanged over the web. Even all social networking websites mainly Facebook, Twitter, and Google depend on MySQL data which are designed and optimized for such purpose. For all these reasons, MySQL servers become the default choice for web applications.

In MySQL, aliases are used to make the content of your database more readable. Aliases are used to give columns or tables a temporary name.

• Aliases are often used to make column names more readable.
• This makes the result of the query more clear.
• There are two aliases in MySQL, which are column alias and table alias.
• In column alias, you give a name to a column using the ‘AS’ keyword.
• Table aliases are mostly used in performing complex joins where you need to write table names many times, by using aliases this can be minimized.
• The renaming is just a temporary change and the table name does not change in the original database.

Column Alias

In MySQL, a column alias is a temporary name assigned to a column in a query result, providing a more readable or meaningful identifier. The AS keyword is optional when assigning column aliases.

Syntax:

SELECT column_name AS alias_name FROM table_name;

Here, column_name is the column in a table, alias_name is a temporary alias name to be used in replacement of the original column name, and table_name is a table name.

1. Create Table

Create a table using CREATE TABLE command.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

2. Insert data into a table

Insert data in the table created in the previous step, using the following command:

 INSERT INTO employees VALUES (1,'A','J',10000000);

3. Query the table

Now query the table using the following command:

SELECT first_name AS 'First Name', last_name AS 'Last Name', salary AS 'Monthly Salary'
FROM employees;

This SQL statement creates three aliases, one for the first_name, another for the last_name, and the other one for salary. The resulting query will display this column with specified aliases which will make the output more readable.

Column Alias

Table Alias

Table aliases are used to fetch the data from more than just a single table and connect them through field relations. Table aliases are mostly used when you have to join more than one table.

1. Create tables

Create two tables ’employees’ and ‘departments’ using the following command:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

2. Insert data

Now, insert data in both the tables.

Use the following commands for inserting the data:

INSERT INTO employees VALUES
    (1, 'John', 'Doe', 1, 50000.00),
    (2, 'Alice', 'Smith', 2, 60000.00),
    (3, 'Bob', 'Johnson', 1, 55000.00);

INSERT INTO departments VALUES
    (1, 'Sales'),
    (2, 'Marketing');

3. Query tables

Now write a query to find the employees within their respective departments. Use the following query to find the answer and in this query, we will also see the use of table alias.

SELECT e.first_name, e.last_name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;

Output:

Table Alias

In this example, we abbreviated the employees table as e and the department table as d, which made our work easier while dealing with the columns of the tables.

Conclusion

Aliases are useful when the column names are big or not readable. Aliases allow us to combine two or more columns. Aliases are just a temporary change and the table name does not change in the database. Aliases in MySQL, both for columns and tables, enhance readability in queries. Column aliases, assigned with the ‘AS’ keyword, provide temporary, meaningful names, while table aliases simplify complex joins, especially when dealing with multiple tables.