SQL inner join example

sql_inner_join.py

import sqlite3

PLAYERS = [
    (1, 'Player1'),
    (2, 'Player2'),
    (3, 'Player3'),
]
COUNTRIES = [
    (1, 'Country1'),
    (2, 'Country2'),
    (3, 'Country3'),
]
GOALS = [
    (2, 2, 7),
    (3, 2, 9),
    (1, 3, 4),
]

# create in-memory db
conn = sqlite3.connect(':memory:')
cur = conn.cursor()

# create example tables
cur.execute('CREATE TABLE players (id INTEGER, name TEXT)')
cur.execute('CREATE TABLE countries (id INTEGER, name TEXT)')
cur.execute('CREATE TABLE goals (player_id INTEGER, country_id INTEGER, goals INTEGER)')

# insert example data
cur.executemany('INSERT INTO players VALUES (?, ?)', PLAYERS)
cur.executemany('INSERT INTO countries VALUES (?, ?)', COUNTRIES)
cur.executemany('INSERT INTO goals VALUES (?, ?, ?)', GOALS)

conn.commit()

# output country names and goals ordered by goals
results = cur.execute("""
    SELECT countries.name, goals.goals
    FROM goals
    INNER JOIN countries
    ON goals.country_id = countries.id
    ORDER BY goals DESC
""").fetchall()

cur.close()
conn.close()

for result in results:
    print(result[0], result[1])