Want to learn more? Take the full course at [ Ссылка ] at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.
---
In this lesson, we'll discuss how to use aliasing to avoid some kinds of errors and how it can make code easier to read and understand.
Aliasing refers to providing a short name, code, or simple identity to tables, columns, and sub-queries in a query. Aliases are temporary and only applied during the running of the query; they do not alter the table or column names in the database. They can be handy for making code in a query easier to read. And, in many cases, are required, for example, a new column or sub-query, that will be re-used in another part of the query.
Let's look at some reasons why we need to use aliasing.
We use aliasing to:
Avoid repetitive use of long table or column names.
Easily identify joined tables and associated columns.
Identify new columns.
Identify sub-queries.
Avoid ambiguity when columns from joined tables share the same name.
And rename columns.
In this example, we want to know the 2017 population of each Nation capital. We can get this by joining the Nations and Cities tables from the Earthquakes database.
When run, it returns an error because both tables have a column named Pop2017. These refer to 2017 census populations. In the Nations table, it is the population of each Nation, and in the Cities table, it is the population of each City. The query needs to know which is which. In this case, we want the City population.
Aliasing tables can correct this; Nations as n and Cities as c. Now if we look at the SELECT statement, we see aliases used to indicate the source table of each selected column with the column name prefixed with the alias of the table name. The Cities table has the c alias, and therefore the 2017 population column is identified as c.Pop2017. Note, there are no rules to what aliases should be. Just make sure they're intuitive and consistent.
To make the output a little easier to read we can choose to rename columns by aliasing the source columns in the SELECT statement. In this example, we renamed CountryName to Country, Code2 to CountryCode and Pop2017 to Population.
Anytime a new column is created it should be given an alias. By doing this indicates what information is in the column and, it is also required if the column is referenced or used elsewhere in the query. In this example, we're creating a new column which is the maximum magnitude earthquake by Country and nearest population. We alias the new column as MaxMagnitude.
When using sub-queries, we need to use an alias to refer to it in other parts of the query. In this example, we want a query that returns the biggest earthquake magnitude where a country capital is the nearest population to the epicenter. We join the Nations table, aliased as n, to a sub-query, aliased as e.
Let's now practice aliasing.
#SQL #DataCamp #SQLTutorial #Query #Performance #SQLServer #Aliasing
