SQL Aliases
We use SQL Aliases to give a temporary name to a table or a column. Sometimes the column names are too lengthy or just complicated so that we can’t show it in the results to the user. In those cases, Aliases are really handy.
NOTE: We can only use Aliases in the single query results. We have to make Aliases again with new query even if we want to use same Aliases.
Syntax
SELECT column AS alias
FROM table;
SELECT column/columns
FROM table AS alias;The first syntax is for column Aliases and the second one is for table Aliases.
Example
SELECT Name AS Name, Population AS Population
FROM country
LIMIT 10;
In the query above, I am creating two Aliases. One is Name and second one is Population. I haven’t changed any names since both the names are simplified already.
You can see the results in DEMO link provided at the end of this article.
Let’s see how to combine columns.
SELECT Name, CONCAT(Region,', ',Continent,', ',LocalName) AS RCL
FROM country
LIMIT 10;
We use CONCAT keyword to combine two or more columns and create Aliases.
NOTE: The above syntax is exclusively used for MySQL only.
The SQL query above will combine the data from Region, Continent and LocalName columns to make one Aliases RCL. You can see the results in DEMO link provided at the end of this article.
Alias for Tables
SELECT c.Language, b.Name, b.Region
FROM countrylanguage AS c, country AS b
WHERE c.Language='French'
AND c.CountryCode=b.Code;
In the query above I am creating Aliases for 2 tables. One is c for countrylanguage tables and other one is b for country table. I have used those Aliases names to pull up data from two tables.
For e.g. c.Language pulls up data from the table countrylanguage and column Language, b.Name pulls up data from country table and Name column and b.Region pulls up data from the same country table but Region column.
After pulling up the data, I’ve applied a WHERE clause in which I am applying two conditions.
One is, I want the data for the Language French so c.Language=’French’ (do not forget to add quotes). The other condition is I want the data in both tables and one way to do that is comparing the country code from both of the tables.
Now in the countrylanguage the name of the country code column is CountryCode but in the country table it’s just Code.
So we have to be careful while comparing those two columns or the query won’t work. So at the end the second condition is c.CountryCode=b.Code and I am using AND operator to make sure both of the conditions are right. That’s it. See the results by yourself in the DEMO.
database sql alias
