Diffrence between these select queries

Question

SELECT cust_email  FROM  customer AS c WHERE c.cust_email="pankaj@yahoo.com"
SELECT cust_email  FROM  customer  WHERE cust_email="pankaj@yahoo.com"

I have these 2 sql queries which perform same operation ,my question is what is the use of giving customer a another name for selecting data ?

an alias name is useful when there is a query involving more than one table. — Manashvi Birla, Jun 09 '15 at 08:59
When we have two or more tables which contains some same columns in that case to identify that which column belongs to which table. — Pankaj K, Jun 09 '15 at 09:00

score 3 · Accepted Answer · edited May 23 '17 at 10:26

It is so called alias. In your case, it is not required.
It is used to make querying easier when current names are inconvenient, ambigious or unknown (for group functions).

Example with ambigiousness in JOIN query. Let we have two tables:

Users

Id         int (PK)
Name       nvarchar(50)

Books

Id         int (PK)
UserId     int (FK)
Name       nvarchar(30)

Now, if you query both tables it will be ambigious because names are similar. Here you can use aliases:

SELECT u.Id as UserId, u.Name as UserName, b.Name AS BookName
FROM Users AS u
INNER JOIN Books AS b
ON u.Id = b.UserId

You will get results:

| UserId | UserName | BookName |
--------------------------------
|   1    |   John   | Book123  |
|   2    |   Mark   | BookXYZ  |
etc...

In case you make a join on columns with the same name you will have to use aliases. Otherwise, it will be a wrong syntax.

INNER JOIN Books ON Id = Id ???

Group functions naming.
For example, in queries with functions like.

SELECT COUNT(*) as RowsCount FROM Users
SELECT CONCAT(FirstName, " ", LastName, " ", MiddleName) as FullName FROM Users

you can use column alias to set a name and access it in the SQL reader or somewhere else.

It can be used for aliasing columns for convenience in some situations like this one:
```
SELECT Id, HasUserEverBeenOnAHelpPage as Value FROM SomeTable
```

and get

| UserId | Value |
------------------
|   1    | true  |
|   2    | false |

instead of

| UserId | HasUserEverBeenOnAHelpPage |
---------------------------------------
|   1    | true                       |
|   2    | false                      |

It doesn't mean that it cannot be used in other situations. Sometimes, it is even used for code readabiliy and programmer's convenience:

SELECT u.Id,
       u.FirstName,
       u.LastName,
       ua.City,
       ua.AddressLine,
       ua.PostalCode,
       us.Language,
       us.IsCookieEnabled,
       lh.LastEnterDate
FROM Users as u
INNER JOIN UserAddresses as ua
ON ua.UserId = u.Id
INNER JOIN UserSettings as us,
ON us.UserId = u.Id
INNER JOIN LoginHistory as lh
ON lh.UserId = u.Id

In this case, the names are not ambigious and this query can be easily done without aliases. However, it is more convenient to work with them:

SELECT Users.Id,
       Users.FirstName,
       Users.LastName,
       UserAddresses.City,
       UserAddresses.AddressLine,
       UserAddresses.PostalCode,
       UserSettings.Language,
       UserSettings.IsCookieEnabled,
       LoginHistory.LastEnterDate
FROM Users
INNER JOIN UserAddresses
ON UserAddresses.UserId = Users.Id
INNER JOIN UserSettings
ON UserSettings .UserId = Users.Id
INNER JOIN LoginHistory
ON LoginHistory .UserId = Users.Id

Read more here:
http://www.w3schools.com/sql/sql_alias.asp
When to use SQL Table Alias
http://www.techonthenet.com/sql/alias.php

Good answer. Strictly speak you can do a join without an alias if the column names match with the USING syntax. Can't say I am keen on it though. — Kickstart, Jun 09 '15 at 16:12

score 0 · Answer 2 · edited May 23 '17 at 12:05

In this case, it doesn't make any difference what so ever.
But when you have a query that uses multiple tables with joins, using an alias to the table name can make the query a lot more readable. However, There is a naming convention that I use that will make aliasing table names completely useless (except when joining the same table more then once). You can read about it here.

from the performance point of view it doesn't change anything.

score 0 · Answer 3 · answered Jun 09 '15 at 09:00

When querying from a single table, using an alias is really not necessary (but it's good practice to get used to using it.)

When querying multiple tables, joining etc (specially when the same table is referenced multiple times), it is needed.

select * from Customer c join Email e on e.cust_id = c.id

Also, it is smart to use in nested selects, specially when multiple table share the same column name. This in order to be sure that you are referencing the column in the table you want to reference, and not another one.

score 0 · Answer 4 · answered Jun 09 '15 at 09:00

0

There can be more reasons to do so.

it can make a query more readable.
When you make a self join you have to give every table a unique name.

answered Jun 09 '15 at 09:00

Jens

67,715
15
98
113

score 0 · Answer 5 · answered Jun 09 '15 at 09:00

These custom names (aliases) are useful when you're selecting a function, like

SELECT RTRIM(XMLAGG(XMLELEMENT(E, text, '').EXTRACT('//text()') ORDER BY line)
             .GetClobVal(),
             ',') 
  FROM all_source t
 WHERE owner = 'QA_OWNER'
       AND NAME = 'PKG_PRJ_TEST_MANUAL';

this will return you a column with stupid and sometimes unpredictable name. You won't be able to use this as a subquery, for example.
It's also nice to give custom names to columns in views you grant to another project users, for example. Your names may be meaningless to them.

In general - aliases improve readability.

Diffrence between these select queries

5 Answers5