JOIN clause in SQLite combines rows from two or more tables using a related column or a specified condition. Python's built-in sqlite3 module allows us to execute these queries and retrieve the combined results.

Types of JOIN

• INNER JOIN (OR JOIN): Gives the records that have common attributes in both tables.
• LEFT JOIN: Gives all records from the left table and only the common records from the right table.
• RIGHT JOIN: Gives all records from the right table and only the common records from the left table.
• FULL OUTER JOIN: Gives all records when there is a common attribute in either the left or the right table.
• CROSS JOIN: Gives records of one table with all other records of another table.

We will create two tables: Advisor and Student. The Advisor table stores advisor details, while Student contains student records and an AdvisorID column that identifies each student's advisor.

1. Connect to SQLite and Create Tables

import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.executescript("""
CREATE TABLE Advisor (
    AdvisorID INTEGER PRIMARY KEY,
    AdvisorName TEXT NOT NULL
);

CREATE TABLE Student (
    StudentID INTEGER PRIMARY KEY,
    StudentName TEXT NOT NULL,
    AdvisorID INTEGER,
    FOREIGN KEY (AdvisorID)
        REFERENCES Advisor(AdvisorID)
);
""")
conn.commit()

conn.close()

Explanation:

• sqlite3 provides the interface for working with SQLite databases.
• connect(":memory:") creates a temporary database stored in memory.
• cursor() creates an object for executing SQL statements.
• executescript() creates both tables in a single operation.
• Student table references Advisor through AdvisorID.

2. Inserting Sample Records

advisors = [
    (1, "John Paul"),
    (2, "Anthony Roy"),
    (3, "Raj Shetty"),
    (4, "Sam Reeds"),
    (5, "Arthur Clintwood")
]

students = [
    (501, "Geek1", 1),
    (502, "Geek2", 1),
    (503, "Geek3", 3),
    (504, "Geek4", 2),
    (505, "Geek5", 4),
    (506, "Geek6", 2),
    (507, "Geek7", 2),
    (508, "Geek8", 3),
    (509, "Geek9", None),
    (510, "Geek10", 1)
]

cursor.executemany(
    "INSERT INTO Advisor VALUES (?, ?)",
    advisors
)

cursor.executemany(
    "INSERT INTO Student VALUES (?, ?, ?)",
    students
)

Explanation:

• advisors and students contain the sample records for both tables.
• executemany() inserts multiple records using the supplied values.
• Question-mark placeholders represent values passed separately to the SQL statement.
• None represents SQL NULL, indicating that Geek9 has no assigned advisor.

3. Creating Function to Display Results

We will use the following function to execute each JOIN query and print its results.

def run_query(query):
    cursor.execute(query)

    for row in cursor.fetchall():
        print(row)

Explanation:

• execute() runs the supplied SQL query.
• fetchall() retrieves all remaining rows returned by the query.
• Each row is displayed as a Python tuple.

INNER JOIN

It returns only rows that satisfy the matching condition in both tables. Records without a corresponding match are excluded.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example:

run_query("""
SELECT s.StudentID, s.StudentName, a.AdvisorName
FROM Student AS s
INNER JOIN Advisor AS a
ON s.AdvisorID = a.AdvisorID
ORDER BY s.StudentID;
""")

Output

Explanation:

• Student and Advisor are matched using AdvisorID.
• Only students with a matching advisor appear.
• Geek9 is excluded because its AdvisorID is NULL.
• ORDER BY sorts the returned records by StudentID.

LEFT JOIN

It returns every row from the left table and matching rows from the right table. When a match does not exist, the right-side columns contain NULL values.

Example:

run_query("""
SELECT s.StudentID, s.StudentName, a.AdvisorName
FROM Student AS s
LEFT JOIN Advisor AS a
ON s.AdvisorID = a.AdvisorID
ORDER BY s.StudentID;
""")

Output

Explanation:

• Student is the left table, so every student is included.
• Matching advisor details are returned when available.
• Geek9 remains in the result even though no advisor matches.
• Displays SQL NULL values as None.

RIGHT JOIN

It returns every row from the right table and matching rows from the left table. If no corresponding left-side record exists, its columns contain NULL values.

Example:

run_query("""
SELECT s.StudentID, s.StudentName, a.AdvisorName
FROM Student AS s
RIGHT JOIN Advisor AS a
ON s.AdvisorID = a.AdvisorID
ORDER BY a.AdvisorID, s.StudentID;
""")

Output

Explanation:

• Advisor is the right table, so all advisors appear.
• Student information is included wherever an AdvisorID matches.
• Arthur Clintwood appears without a student because no student references AdvisorID 5.
• Students without matching advisors are not preserved by this RIGHT JOIN.

FULL OUTER JOIN

Returns all matching rows along with unmatched rows from both tables. Missing values on either side are represented by NULL.

Example:

run_query("""
SELECT s.StudentID, s.StudentName, a.AdvisorName
FROM Student AS s
FULL OUTER JOIN Advisor AS a
ON s.AdvisorID = a.AdvisorID
ORDER BY COALESCE(s.AdvisorID, a.AdvisorID, 999),
         s.StudentID;
""")

Output

Explanation:

• All matching student-advisor pairs are included.
• Geek9 appears even though no advisor is assigned.
• Arthur Clintwood appears even though no student is assigned to that advisor.
• COALESCE selects the first non-NULL value for sorting the results.

CROSS JOIN

It returns the Cartesian product of two tables. Every row from the first table is combined with every row from the second table, regardless of whether their values match.

Example:

run_query("""
SELECT s.StudentName, a.AdvisorName
FROM Student AS s
CROSS JOIN Advisor AS a
ORDER BY s.StudentID, a.AdvisorID
LIMIT 5;
""")

Output

Explanation:

• Each student is paired with every advisor.
• Ten students and five advisors produce 50 combinations.
• LIMIT 5 displays only the first five rows, not the entire result.
• CROSS JOIN should be used carefully when tables contain many records.

Using ON and USING in JOIN Queries

ON clause specifies the matching condition explicitly. The USING clause offers a shorter alternative when both tables have a common column name.

Example:

run_query("""
SELECT StudentName, AdvisorName
FROM Student
INNER JOIN Advisor
USING (AdvisorID)
ORDER BY StudentID;
""")

Explanation:

• USING (AdvisorID) matches rows by the column shared by both tables.
• It can replace ON Student.AdvisorID = Advisor.AdvisorID in this example.
• USING is appropriate when the common column name represents the intended relationship.