Alias using "AS" and without using "AS" [closed]

Question

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 ?

Answer 1

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.