3

Would it be possible to give a table in MySQL query multiple aliases?

If so how would I do this?

  • 4
    Hi, can you help me understand why you would want to do this? – Derek Downey Apr 5 '13 at 13:32
3

You can if you are using the table multiple times in different clauses and/or sub-queries like

SELECT SeniorManager = sm.displayName, Manager = m.displayName, Worker = w.displayName
FROM   people AS w
LEFT OUTER JOIN people AS  m ON  m.id = w.bossID
LEFT OUTER JOIN people AS sm ON sm.id = m.bossID

But you can not give the same use of a table more than one alias - the following for instance is invalid syntax:

SELECT a1.name, a2.name FROM people AS a1 AND a2

Also, you can not (in most DBMSs) use a table by both its real name and alias, so the following is invalid too:

SELECT people.name, a1.name FROM people AS a1 

If ause of an object is given an alias you must refer to that instance by that alias even if the real name is unambiguous within the query.

1

Yes you can.

For example: for a table such as this:

create table staff
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(100),
    supervisorId INT,
    FOREIGN KEY (SupervisorId) REFERENCES staff(id)
)

You can query it like so :

select a.name as EmployeeName, b.name as SupervisorName
from staff a,
 staff b
where 
 a.supervisorId = b.id

or

select a.name as EmployeeName, b.name as SupervisorName
from 
 staff a
 inner join staff b on a.supervisorId = b.id
0

You can use INNER JOIN To do this, example

SELECT count(*)
FROM user AS u1
INNER JOIN user u2 ON u1.user_id = u2.user_id

But note that will be worse performing than only "one alias"

mysql   > DESC select count(*)
          FROM user AS u1
+------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------+--------+------------+-------------+
|   id | select_type   | table   |   partitions | type   |   possible_keys | key           |   key_len |    ref |   rows |   filtered | Extra       |
|------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------+--------+------------+-------------|
|    1 | SIMPLE        | u1      |       <null> | index  |          <null> | client_id_idx |         4 | <null> |   5625 |        100 | Using index |
+------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------+--------+------------+-------------+

mysql   > DESC select count(*)
          FROM user AS u1
          INNER JOIN user u2 ON u1.user_id = u2.user_id
+------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------------------+--------+------------+-------------+
|   id | select_type   | table   |   partitions | type   | possible_keys   | key           |   key_len | ref                |   rows |   filtered | Extra       |
|------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------------------+--------+------------+-------------|
|    1 | SIMPLE        | u1      |       <null> | index  | PRIMARY         | client_id_idx |         4 | <null>             |   5625 |        100 | Using index |
|    1 | SIMPLE        | u2      |       <null> | eq_ref | PRIMARY         | PRIMARY       |         4 | gr_cg35.u1.user_id |      1 |        100 | Using index |
+------+---------------+---------+--------------+--------+-----------------+---------------+-----------+--------------------+--------+------------+-------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.