Alias

Provides a shorcut for a table or field.

 

For tables:

SELECT column_name(s) FROM table_name AS alias_name ;

 

mysql> select Age from Students As text;
+-----+
| Age |
+-----+
|  21 |
|  20 |
|  34 |
|  42 |
|  17 |
|  36 |
|  58 |
|  49 |
+-----+
8 rows in set (0.00 sec)

 

 

For fields:

SELECT column_name AS alias_name FROM table_name ;

 

mysql> select Age as No from Students;
+----+
| No |
+----+
| 21 |
| 20 |
| 34 |
| 42 |
| 17 |
| 36 |
| 58 |
| 49 |
+----+
8 rows in set (0.00 sec)

 

 

Or assigning the CONCAT() function (to concatenate two fields) AS an alias:

mysql> SELECT CONCAT(FirstName, ‘ ‘, Surname) AS FullName FROM directory;
+--------------+
| FullName     |
+--------------+
| Jo Jones     |
| Paul Smith   |
| Terry Brains |
| Tony Bond    |
+--------------+
4 rows in set (0.00 sec)

 

 

 

Two tables example

customers table:

mysql> select * from customers;
+--------+-------+---------+------+
| CustID | First | Surname | Age  |
+--------+-------+---------+------+
|      1 | Pam   | Frost   |   21 |
|      2 | James | Cross   |   20 |
|      3 | Andy  | Jones   |   34 |
+--------+-------+---------+------+
3 rows in set (0.00 sec)

 orders table

mysql> select * from orders;
+---------+--------+---------+------+
| OrderID | CustID | Part    | Cost |
+---------+--------+---------+------+
|       1 |      3 | CPU     |  975 |
|       2 |      1 | RAM     |  433 |
|       3 |      2 | SSD     |  768 |
|       4 |      3 | Monitor |  250 |
+---------+--------+---------+------+
4 rows in set (0.00 sec)

 

It can be seen that the orders table has a CustID field, that relates to the customers table.

 

Assign two aliases:

  • c as the customers tables alias
  • o as the orders tables alias

SELECT o.OrderID, c.First, c.Surname FROM customers AS c, orders AS o WHERE c.Surname=’Cross’  AND o.CustID=c.CustID;

 

mysql> SELECT o.OrderID, c.First, c.Surname FROM customers AS c, orders AS o WHERE c.Surname=’Cross’ AND o.CustID=c.CustID;
+---------+-------+---------+
| OrderID | First | Surname |
+---------+-------+---------+
|       3 | James | Cross   |
+---------+-------+---------+
1 row in set (0.00 sec)

 

 

*Note: the keyword AS can be completely removed/replaced with a space:

mysql> SELECT o.OrderID, c.First, c.Surname FROM customers c, orders o WHERE c.Surname=’Cross’ AND o.CustID=c.CustID;
+---------+-------+---------+
| OrderID | First | Surname |
+---------+-------+---------+
|       3 | James | Cross   |
+---------+-------+---------+
1 row in set (0.00 sec)

Leave a Reply