Joining with aliased tables
Table aliases are helpful in allowing you to reference them in other parts of your query, like the SELECT
statement.
When you SELECT
fields, a field can be ambiguous. For example, imagine two tables, apples
and oranges
, both containing a column called color
. You need to use the syntax apples.color
or oranges.color
in your SELECT
statement to point SQL to the correct table. Without this, you would get the following error:
column reference "color" is ambiguous
You'll practice joining with aliased tables using data from both the countries
and economies
tables to examine the inflation rate in 2010 and 2015.
When writing joins, many SQL users prefer to write the SELECT
statement after writing the join code, in case the SELECT
statement requires using table aliases.
This exercise is part of the course
Joining Data in SQL
Exercise instructions
- Start with your inner join in line 5; join the tables
countries AS c
(left) witheconomies
(right), aliasingeconomies AS e
. - Next, use
code
as your joining field in line 7; do not use theUSING
command here. - Lastly, select the following columns in order in line 2:
code
from thecountries
table (aliased ascountry_code
),name
,year
, andinflation_rate
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Select fields with aliases
___
FROM countries AS c
-- Join to economies (alias e)
___
-- Match on code field using table aliases
___