SQL Alias (AS) |
SQL Sample Code - SQL Alias (AS). |
Tables
| SELECT column_name(s)FROM table_name AS alias_name; |
Columns
| SELECT column_name AS alias_name FROM table_name; |
What does Alias do? |
Alias is used to temporarily give a database table or column adifferent name. It is used mainly to either shorten or simplify long, complex column and table names or to change meaningless names into something more meaningful to the intended user.
SQL Sample 1 – SQL Alias (AS) (Columns) |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
If we want to shorten the ‘CompanyName’ column name to say ‘Company’ and Company_ID to ID.
Use the SELECT statement below:
| SELECT Company_ID AS ‘ID’, CompanyName AS ‘Company’, Address, Town FROM tblCompany; |
The table of results will look like this:
| ID | Company | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 4 | SQL Code Land | 2 Code Rd | Pisa |
| 3 | Sample Code World | 66 SQL St | Curry |
| 2 | SQL Reference Ltd | 34 Reference St | Hamburg |
SQL Sample 2 – SQL Alias (AS) (Tables) |
In this SQL sample we will use the database table ‘tblCompany’ and 'tblStaff' .
| Company_ID | CompanyName | Address | Town |
| 1 | SQL Sample | 1 Sample St | Hamburg |
| 2 | SQL Code Land | 2 Code Rd | Hamburg |
| 3 | Sample Code World | 66 SQL St | Curry |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa |
| Staff_ID | Staff_Forename | Staff_Surname | Staff_Age | StaffGender |
| 1 | Joe | Sample | 21 | M |
| 2 | Fred | Code | 32 | M |
| 3 | Ben | SQL | 35 | F |
| 4 | Frederica | Reference | 21 | F |
We want to know who the staff members are for the Burger Town company who are aged 21 and Female.
Use the SELECT statement below using a WHERE clause:
| SELECT Staff_Forename, Staff_Surname, Company_Name FROM tblStaff, tblCompany WHERE tblCompany.CompanyName=’Burger Town’ AND tblStaff.Staff_Age=21 AND tblStaff.StaffGender=’F’; |
The table of results will look like this:
| Staff_Forename | Staff_Surname | StaffGender | Staff_Age | CompanyName |
| Frederica | Reference | F | 21 | Burger Town |
Using aliases for tables we could have written the SQL SELECT statement so it was easier to read as follows. Be aware that this example only uses 2 tables and only a few columns, very often you will use several tables and columns at a time.
| SELECT st.Staff_Forename, st.Staff_Surname, c.Company_Name FROM tblStaff AS st, tblCompany AS c WHERE c.CompanyName=’Burger Town’ AND st.Staff_Age=21 AND st.StaffGender=’F’; |
Related SQL Sample Code:
|
|