5

What is the difference in accessing a table with alias "AS" and without using "AS". Is there any performance enhancements?

For Example :

SELECT S.ID,S2.Value FROM dbo.Sample AS S
INNER JOIN dbo.Sample2 AS S2 ON 
S.ID = S2.ID;

SELECT S.ID,S2.Value FROM dbo.Sample S
INNER JOIN dbo.Sample2 S2 ON 
S.ID = S2.ID;

What will be difference between 1 and 2 ?

  • 3
    There is no difference. – a_horse_with_no_name Mar 20 '15 at 10:08
  • 2
    The only difference is that the second query works everywhere. The first query will raise error in Oracle. – ypercubeᵀᴹ Mar 20 '15 at 10:41
  • 1
    The dbo. suggests you are using SQL-Server anyway. – ypercubeᵀᴹ Mar 20 '15 at 10:45
  • yes i am using sql server – Sriram M Mar 20 '15 at 10:46
  • It seems if you work in a VS database project that you have to use the 'as' keyword, or it won't build – Zach Smith Aug 18 '17 at 13:34
8

Assuming MySQL, if you execute EXPLAIN EXTENDED on both queries, you get:

mysql> EXPLAIN EXTENDED SELECT S.ID,S2.Value FROM dbo.Sample AS S
    -> INNER JOIN dbo.Sample2 AS S2 ON 
    -> S.ID = S2.ID;
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref      | rows | filtered | Extra       |
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
|  1 | SIMPLE      | S     | index  | ID            | ID   | 8       | NULL     |    1 |   100.00 | Using index |
|  1 | SIMPLE      | S2    | eq_ref | ID            | ID   | 8       | dbo.S.ID |    1 |   100.00 | NULL        |
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `dbo`.`S`.`ID` AS `ID`,`dbo`.`S2`.`Value` AS `Value` from `dbo`.`Sample` `S` join `dbo`.`Sample2` `S2` where (`dbo`.`S2`.`ID` = `dbo`.`S`.`ID`)
1 row in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT S.ID,S2.Value FROM dbo.Sample S
    -> INNER JOIN dbo.Sample2 S2 ON 
    -> S.ID = S2.ID;
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref      | rows | filtered | Extra       |
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
|  1 | SIMPLE      | S     | index  | ID            | ID   | 8       | NULL     |    1 |   100.00 | Using index |
|  1 | SIMPLE      | S2    | eq_ref | ID            | ID   | 8       | dbo.S.ID |    1 |   100.00 | NULL        |
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `dbo`.`S`.`ID` AS `ID`,`dbo`.`S2`.`Value` AS `Value` from `dbo`.`Sample` `S` join `dbo`.`Sample2` `S2` where (`dbo`.`S2`.`ID` = `dbo`.`S`.`ID`)
1 row in set (0.00 sec)

EXPLAIN EXTENDED shows the actual query executed as a WARNING, once the aliases, column names, etc have been resolved. As you can see, the query sent to the query planner, once parsed, is exactly the same for both cases, so it confirms that it would not have any performance difference.

| improve this answer | |

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