SQL Aliases alias

May 16, 2021 SQL

1. SQL Alias (Aliases)

2. Demonstrate the database

3. The Alias instance of the column

4. The Alias instance of the table

5. Example

SQL Alias (Aliases)

By using SQL, you can assign an alias (Alias) to a table name or column name.

SQL aliases are used to provide temporary names for tables or columns in tables, and the actual table names in the database do not change.
SQL aliases are typically used to make column names more readable.
SQL an alias exists only during queries.
Table aliasses are used to rename tables in specific SQL statements.
A column alias is used to rename a table's columns for a specific SQL query.

THE SQL Alias syntax of the column

SELECT column_name AS alias_name




FROM table_name;
WHERE [condition];

The SQL Alias syntax of the table

SELECT column_name(s)
FROM table_name AS alias_name;
WHERE [condition];

Demonstrate the database

In this tutorial, we'll use the famous Northwind sample database.

Here's the data from the Customers table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK

Here's the data from the Orders table:

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10354	58	8	1996-11-14	3
10355	4	6	1996-11-15	1
10356	86	6	1996-11-18	2

The Alias instance of the column

The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:

SELECT CustomerID as ID, CustomerName AS Customer
FROM Customers;

The following SQL statement creates two aliases, one for the CustomerName column and one for the ContactName column. : If the alias contains spaces, double quotes or square brackets are required:

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

The following SQL statement creates an alias called Address, which contains four columns (Address, PostalCode, City, and Country):

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

Note: For the SQL statement above to work in MySQL, use the following command:

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

The Alias instance of the table

The following SQL statement selects all orders for CustomerID s 4 ("around the corner"). W e use the "Customers" and "Orders" tables and give them table aliases of "c" and "o" respectively (here we use aliases to make SQL shorter):

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

The following SQL statement is the same as above, but without an alias:

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;

It is useful to use an alias in the following situations:

The query involves multiple tables
Used to query functions
Two or more columns need to be put together
The column name is long or poorly readable

Example

Consider the following two data sheets:

(a) THE CUSTOMERS table, as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

(b) The other is the ORDERS table, as follows:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Here's how the table alias is used:

SQL> SELECT C.ID, C.NAME, C.AGE, O.AMOUNT 
        FROM CUSTOMERS AS C, ORDERS AS O
        WHERE  C.ID = O.CUSTOMER_ID;

The results of the above statement are as follows:

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |   3000 |
|  3 | kaushik  |  23 |   1500 |
|  2 | Khilan   |  25 |   1560 |
|  4 | Chaitali |  25 |   2060 |
+----+----------+-----+--------+

Here's how the column alias is used:

SQL> SELECT  ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
     FROM CUSTOMERS
     WHERE SALARY IS NOT NULL;

The results are as follows:

+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
|           1 | Ramesh        |
|           2 | Khilan        |
|           3 | kaushik       |
|           4 | Chaitali      |
|           5 | Hardik        |
|           6 | Komal         |
|           7 | Muffy         |
+-------------+---------------+