Skip to content

Joining Data with SQL

Here you can access every table used in the course. To access each table, you will need to specify the world schema in your queries (e.g., world.countries for the countries table, and world.languages for the languages table).

Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

The ins and outs of inner Joins

Spinner
DataFrameas
df
variable
-- Select name fields (with alias) and region 
SELECT cities.name AS city, countries.name AS country, region
FROM world.cities
INNER JOIN world.countries 
ON cities.country_code = countries.code;
Hidden output
Spinner
DataFrameas
countries
variable
-- Select fields with aliases
SELECT c.code As country_code, name, year , inflation_rate
FROM world.countries As c
-- Join to economies (alias e)
INNER JOIN world.economies As e
-- Match on code field using table aliases
ON c.code = e.code;
Hidden output
Spinner
DataFrameas
df1
variable
SELECT c.name AS country, l.name AS language, official
FROM world.countries AS c
INNER JOIN world.languages AS l
-- Match using the code column
Using(code);
Hidden output

Inspecting Relationship

Spinner
DataFrameas
df2
variable
-- Select country and language names, aliased
Select c.name As country, l.name As language
-- From countries (aliased)
FROM world.countries As c
-- Join to languages (aliased)
INNER JOIN languages As l
-- Use code as the joining field with the USING keyword
USING(code);
Spinner
DataFrameas
df3
variable
-- Rearrange SELECT statement, keeping aliases
SELECT l.name AS language, c.name AS country
FROM world.countries AS c
INNER JOIN languages AS l
USING(code)
-- Order the results by language
ORDER BY language;

Multiple Joins

Spinner
DataFrameas
df4
variable
-- Select relevant fields
SELECT name, year, fertility_rate
FROM world.populations As p
-- Inner join countries and populations, aliased, on code
INNER JOIN countries As c
ON p.country_code = c.code;
Spinner
DataFrameas
df5
variable
-- Select fields
SELECT name , e.year, fertility_rate, unemployment_rate
FROM world.countries AS c
INNER JOIN populations AS p
ON c.code = p.country_code
-- Join to economies (as e)
INNER JOIN economies As e
-- Match on country code
ON c.code = e.code
Spinner
DataFrameas
df6
variable
SELECT name, e.year, fertility_rate, unemployment_rate
FROM world.countries AS c
INNER JOIN populations AS p
ON c.code = p.country_code
INNER JOIN economies AS e
ON c.code = e.code
-- Add an additional joining condition such that you are also joining on year
AND p.year = e.year;

Left and Right Joins

Spinner
DataFrameas
df7
variable
SELECT 
    c1.name AS city,
    code,
    c2.name AS country,
    region,
    city_proper_pop
FROM world.cities AS c1
-- Perform an inner join with cities as c1 and countries as c2 on country code
INNER JOIN countries As c2
ON c1.country_code = c2.code
ORDER BY code DESC;world.cities
‌
‌
‌