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).

Database: The leadership database

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.

Chapter 1: Inner Joins

The Ins and Outs of INNER JOIN

  • Key: Field that uniquely identifies each record
  • INNER JOIN looks for records in both tables which match on given field
  • Schema:
Spinner
DataFrameas
df
variable
--Inner join of presidents and prime_ministers, joining on country
SELECT prime_ministers.country, prime_ministers.continent, prime_ministers.prime_minister, presidents.president
FROM world.prime_ministers
INNER JOIN world.presidents
ON prime_ministers.country = presidents.country;

Aliasing

Spinner
DataFrameas
df1
variable
--Alternative: use aliasing
SELECT p1.country, p1.continent, prime_minister, president
FROM world.prime_ministers as p1
INNER JOIN world.presidents as p2
on p1.country = p2.country;

USING

Spinner
DataFrameas
df2
variable
--Alternative: using USING
SELECT p1.country, p1.continent, prime_minister, president
FROM world.prime_ministers as p1
INNER JOIN world.presidents as p2
USING(country)

Types of Relationships

  • One-to-many: One entity can be associated with multiple entities of another type. Example: One artist can produce many songs.
  • One-to-one: Each entity of one type is associated with only one entity of another type. Example: Unique fingerprint.
  • Many-to-many: Multiple entities of one type can be associated with multiple entities of another type. Example: Many students can enroll in many courses.

Multiple Joins

Spinner
DataFrameas
df3
variable
-- Select fields
SELECT c.name, e.year, fertility_rate, unemployment_rate
FROM world.countries AS c
INNER JOIN world.populations AS p
ON c.code = p.country_code
-- Join to economies (as e)
INNER JOIN world.economies as e
-- Match on country code
ON c.code = e.code;
Spinner
DataFrameas
df4
variable
SELECT name, e.year, fertility_rate, unemployment_rate
FROM world.countries AS c
INNER JOIN world.populations AS p
ON c.code = p.country_code
INNER JOIN world.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;

Chapter 2: Outer Joins, Cross Joins, and Inner Joins

LEFT JOIN and RIGHT JOIN

LEFT JOIN

  • LEFT JOIN will return all records in the left table
  • LEFT JOIN = LEFT OUTER JOIN
‌
‌
‌