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 ?

closed as off-topic by Paul White, Michael Green, Max Vernon, Mark Sinkinson, Shanky Mar 20 '15 at 13:29

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Michael Green, Max Vernon, Mark Sinkinson, Shanky
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
  • 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
up vote 8 down vote accepted

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.

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