SQL Joins Example in Python with SQLite

3.2.5.5. SQL Joins Example in Python with SQLite#

Below, we’ll create sample tables and demonstrate different join types using sqlite3 in Python.

Step 1 – Setup and Create Tables#

Hide code cell source

 
import sqlite3

# Create a fresh database
conn = sqlite3.connect("joins_demo.db")
cursor = conn.cursor()

# Drop tables if they exist
cursor.execute("DROP TABLE IF EXISTS Customers;")
cursor.execute("DROP TABLE IF EXISTS Orders;")

# Create Customers table
cursor.execute("""
CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    Name TEXT,
    City TEXT
);
""")

# Create Orders table
cursor.execute("""
CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    Product TEXT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
""")

# Insert sample data into Customers
cursor.executemany(
    "INSERT INTO Customers (CustomerID, Name, City) VALUES (?, ?, ?);",
    [
        (1, "Alice", "New York"),
        (2, "Bob", "Los Angeles"),
        (3, "Charlie", "Chicago"),
        (4, "David", "Houston"),
    ]
)

# Insert sample data into Orders (note: CustomerID 5 does not exist)
cursor.executemany(
    "INSERT INTO Orders (OrderID, CustomerID, Product) VALUES (?, ?, ?);",
    [
        (1, 1, "Laptop"),
        (2, 2, "Phone"),
        (3, 2, "Tablet"),
        (4, 5, "Monitor")
    ]
)

conn.commit()

Step 2 – View Initial Data#

# Customers table
print("Customers Table:")
for row in cursor.execute("SELECT * FROM Customers;"):
    print(row)

# Orders table
print("\nOrders Table:")
for row in cursor.execute("SELECT * FROM Orders;"):
    print(row)

Customers Table:
(1, 'Alice', 'New York')
(2, 'Bob', 'Los Angeles')
(3, 'Charlie', 'Chicago')
(4, 'David', 'Houston')

Orders Table:
(1, 1, 'Laptop')
(2, 2, 'Phone')
(3, 2, 'Tablet')
(4, 5, 'Monitor')

Step 3 – INNER JOIN (Only matching records)#

print("INNER JOIN (Customers with Orders):")
query = """
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
"""
for row in cursor.execute(query):
    print(row)

INNER JOIN (Customers with Orders):
(1, 'Alice', 1, 'Laptop')
(2, 'Bob', 2, 'Phone')
(2, 'Bob', 3, 'Tablet')

Step 4 – LEFT JOIN (All customers, even if they have no orders)#

print("LEFT JOIN (All Customers, with Orders if any):")
query = """
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
"""
for row in cursor.execute(query):
    print(row)

LEFT JOIN (All Customers, with Orders if any):
(1, 'Alice', 1, 'Laptop')
(2, 'Bob', 2, 'Phone')
(2, 'Bob', 3, 'Tablet')
(3, 'Charlie', None, None)
(4, 'David', None, None)

Step 5 – RIGHT JOIN Workaround (All Orders, even if no matching customer)#

SQLite doesn’t support RIGHT JOIN natively, so we swap table order and use LEFT JOIN:

print("RIGHT JOIN workaround (All Orders, with Customers if any):")
query = """
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Orders
LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID;
"""
for row in cursor.execute(query):
    print(row)

RIGHT JOIN workaround (All Orders, with Customers if any):
(1, 'Alice', 1, 'Laptop')
(2, 'Bob', 2, 'Phone')
(2, 'Bob', 3, 'Tablet')
(None, None, 4, 'Monitor')

Step 6 – FULL OUTER JOIN Workaround (Combine LEFT and RIGHT JOIN results)#

print("FULL OUTER JOIN (Combine LEFT and RIGHT JOIN):")
query = """
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
UNION
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Orders
LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID;
"""
for row in cursor.execute(query):
    print(row)

FULL OUTER JOIN (Combine LEFT and RIGHT JOIN):
(None, None, 4, 'Monitor')
(1, 'Alice', 1, 'Laptop')
(2, 'Bob', 2, 'Phone')
(2, 'Bob', 3, 'Tablet')
(3, 'Charlie', None, None)
(4, 'David', None, None)

Step 7 – NATURAL JOIN (Joins on columns with the same name)#

print("NATURAL JOIN (Join on CustomerID automatically):")
query = """
SELECT *
FROM Customers
NATURAL JOIN Orders;
"""
for row in cursor.execute(query):
    print(row)

NATURAL JOIN (Join on CustomerID automatically):
(1, 'Alice', 'New York', 1, 'Laptop')
(2, 'Bob', 'Los Angeles', 2, 'Phone')
(2, 'Bob', 'Los Angeles', 3, 'Tablet')

Step 8 – CROSS JOIN (Cartesian product filtered by WHERE)#

print("CROSS JOIN (Explicit join with WHERE filter):")
query = """
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID;
"""
for row in cursor.execute(query):
    print(row)

CROSS JOIN (Explicit join with WHERE filter):
(1, 'Alice', 1, 'Laptop')
(2, 'Bob', 2, 'Phone')
(2, 'Bob', 3, 'Tablet')
Contents