06 May 2014
MySQL aliases are simply nicknames used to express tables, columns and functions’ names. These nicknames can be used in your queries to make them short and neat. Let’s explore this feature.
Before digging into MySQL aliases, we need to create test database, two tables and insert some dummy data.
CREATE TABLE `posts` (
`post_id` int(11) not null auto_increment,
`post_title` varchar(150) not null,
`user_id` int(11) not null,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE `users` (
`user_id` int(11) not null auto_increment,
`first_name` varchar(50) not null,
`last_name` varchar(50) not null,
`username` varchar(60) not null,
PRIMARY KEY (`user_id`),
UNIQUE KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
INSERT INTO `users` (`user_id`, `first_name`, `last_name`, `username`) VALUES
('1', 'mark', 'josh', 'marjo'),
('2', 'selen', 'john', 'selejo'),
('3', 'helen', 'john', 'helejo'),
('4', 'sandra', 'delen', 'sandele'),
('5', 'taylor', 'semoz', 'taysem');
INSERT INTO `posts` (`post_id`, `post_title`, `user_id`) VALUES
('1', 'PHP cURL', '1'),
('2', 'PHP cURL', '2'),
('3', 'PHP stdClass', '3'),
('4', 'PHP strings', '4'),
('5', 'PHP arrays', '5'),
('6', 'PHP & MySQL', '3'),
('7', 'PHP mysqli', '4');
Table aliases are used to express tables’ names in shorter form. You can set table alias using AS keyword or without it. This means that
mysql> SELECT user_id, username FROM users AS usr;
is similar to
mysql> SELECT user_id, username FROM users AS usr;
Explore the following example to understand table aliases.
mysql> SELECT usr.username, pos.post_title FROM
-> users AS usr INNER JOIN posts AS pos
-> USING (user_id) WHERE usr.user_id > 3;
+----------+-------------+
| username | post_title |
+----------+-------------+
| sandele | PHP strings |
| taysem | PHP arrays |
| sandele | PHP mysqli |
+----------+-------------+
3 rows in set (0.00 sec)
As you can see, the users table is aliased as usr and posts table is aliased as pos. This allows you to express columns in more compact form (usr.column_name).
Actually the previous example is simple and doesn’t show the power of this feature so let’s make things a bit difficult. Suppose you want to select posts which have the same title and the corresponding user id. To do this you might try a query like the following.
mysql> SELECT pos1.user_id, pos2.post_title
-> FROM posts AS pos1, posts AS pos2
-> WHERE pos1.post_title = pos2.post_title
-> AND pos1.user_id != pos2.user_id;
+---------+------------+
| user_id | post_title |
+---------+------------+
| 2 | PHP cURL |
| 1 | PHP cURL |
+---------+------------+
2 rows in set (0.00 sec)
As you can see, The previous query do the following:
SELECT pos1.user_id, pos2.post_title).FROM posts AS pos1, posts AS pos2).WHERE pos1.post_title = pos2.post_title AND pos1.user_id != pos2.user_id;)Column aliases are similar to table aliases but the AS keyword is required and mustn’t be omitted. Consider the following example.
mysql> SELECT username AS usnam FROM users;
+---------+
| usnam |
+---------+
| helejo |
| marjo |
| sandele |
| selejo |
| taysem |
+---------+
5 rows in set (0.00 sec)
As you can see, the column username is aliased as usnam. You can see that in the output. Let’s explore another advanced example.
mysql> SELECT CONCAT(first_name," ",last_name) AS full_name
-> FROM users ORDER BY full_name;
+--------------+
| full_name |
+--------------+
| helen john |
| mark josh |
| sandra delen |
| selen john |
| taylor semoz |
+--------------+
5 rows in set (0.00 sec)
Well, I used CONCAT() function to concate the first_name and last_name of users and aliased them as full_name. Let’s explore another example for further understanding.
mysql> SELECT CONCAT(first_name," ",last_name," posted ",post_title)
-> AS full_data FROM users INNER JOIN posts USING (user_id)
-> ORDER BY full_data;
+---------------------------------+
| full_data |
+---------------------------------+
| helen john posted PHP & MySQL |
| helen john posted PHP stdClass |
| mark josh posted PHP cURL |
| sandra delen posted PHP mysqli |
| sandra delen posted PHP strings |
| selen john posted PHP cURL |
| taylor semoz posted PHP arrays |
+---------------------------------+
7 rows in set (0.00 sec)