SQLite Joins and UNION in Python

A join combines rows according to a relationship condition. The fixture has sites Meadow, Ridge and Fen, and samples 10 and 11 belonging to Meadow plus sample 12 belonging to Ridge. INNER JOIN produces three matches. Meadow appears twice because two sample rows match it; this is the intended one-to-many relationship rather than an accidental duplicate.

Qualify shared column names with table aliases such as s.SiteID and m.SiteID. Omitting or miswriting the relationship can multiply unrelated rows. Do not use DISTINCT merely to hide a faulty join: first check whether repeated rows reflect legitimate matches or an incorrect condition. Select the identifying columns needed to explain each result.

A LEFT JOIN retains every left-hand site. If Fen has no sample, its sample columns are NULL. A RIGHT JOIN retains every right-hand row, while FULL OUTER JOIN retains unmatched rows from both sides. Modern SQLite supports RIGHT and FULL joins from version 3.39.0; an older installed engine needs an upgrade or a logically equivalent supported query, not a claim that the syntax works everywhere.

A filter on a right-hand field in WHERE can remove the NULL-extended rows of a LEFT JOIN. For example, WHERE m.Reading>=20 excludes Fen because its comparison is unknown. If the task wants every site plus matching samples of at least 20, put that sample condition in the ON clause. Explain whether the filter restricts matches or removes final result rows.

UNION combines compatible SELECT results vertically: they must have the same number of result columns. UNION removes duplicate result rows; UNION ALL retains them. It does not match columns through a foreign key and does not horizontally attach unrelated attributes. Use an outer ORDER BY when the combined result needs a defined order.

The in-memory example includes a separate request table with one matching site and one unknown site to demonstrate unmatched rows in a full join. It intentionally does not declare those requests as valid Sample foreign keys: an orphan record should not be presented as evidence that enforced referential integrity permits nonexistent parents. The version check makes the FULL/RIGHT prerequisite explicit.

import sqlite3

connection = sqlite3.connect(':memory:')
try:
    connection.execute('CREATE TABLE Site (SiteID INTEGER PRIMARY KEY, Name TEXT)')
    connection.execute('CREATE TABLE Sample (SampleID INTEGER PRIMARY KEY, SiteID INTEGER, Reading INTEGER)')
    connection.executemany('INSERT INTO Site VALUES (?, ?)', [(1, 'Meadow'), (2, 'Ridge'), (3, 'Fen')])
    connection.executemany('INSERT INTO Sample VALUES (?, ?, ?)', [(10, 1, 10), (11, 1, 20), (12, 2, 30)])
    inner = connection.execute('SELECT s.Name, m.SampleID FROM Site s INNER JOIN Sample m ON s.SiteID=m.SiteID ORDER BY s.SiteID, m.SampleID').fetchall()
    left = connection.execute('SELECT s.Name, m.SampleID FROM Site s LEFT JOIN Sample m ON s.SiteID=m.SiteID ORDER BY s.SiteID, m.SampleID').fetchall()
    kept = connection.execute('SELECT s.Name, m.SampleID FROM Site s LEFT JOIN Sample m ON s.SiteID=m.SiteID AND m.Reading>=20 ORDER BY s.SiteID, m.SampleID').fetchall()
    print(inner)
    print(left)
    print(kept)
    print(connection.execute('SELECT Name FROM Site WHERE SiteID<=2 UNION SELECT Name FROM Site WHERE SiteID>=2 ORDER BY Name').fetchall())
    print(connection.execute('SELECT Name FROM Site WHERE SiteID<=2 UNION ALL SELECT Name FROM Site WHERE SiteID>=2 ORDER BY Name').fetchall())
finally:
    connection.close()

import sqlite3

if sqlite3.sqlite_version_info < (3, 39, 0):
    raise RuntimeError('This outer-join exercise requires SQLite 3.39.0 or later')
connection = sqlite3.connect(':memory:')
try:
    connection.execute('CREATE TABLE Site (SiteID INTEGER PRIMARY KEY)')
    connection.execute('CREATE TABLE Request (SiteID INTEGER)')
    connection.executemany('INSERT INTO Site VALUES (?)', [(1,), (2,)])
    connection.executemany('INSERT INTO Request VALUES (?)', [(2,), (99,)])
    right = connection.execute('SELECT s.SiteID, r.SiteID FROM Site s RIGHT JOIN Request r ON s.SiteID=r.SiteID ORDER BY r.SiteID').fetchall()
    full = connection.execute('SELECT s.SiteID, r.SiteID FROM Site s FULL OUTER JOIN Request r ON s.SiteID=r.SiteID ORDER BY COALESCE(s.SiteID, r.SiteID)').fetchall()
    print(right)
    print(full)
finally:
    connection.close()

Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.