Python MySQL: Using JOIN to Combine Tables

In relational databases, data is often stored across multiple tables. To retrieve meaningful information, you need to combine these tables. MySQL’s JOIN clause allows you to fetch related data from multiple tables in a single query. In this tutorial, we’ll show you how to use JOIN operations with Python to manage and query your MySQL database efficiently.

At The Coding College, we simplify complex programming concepts to help you grow as a developer.

Prerequisites

Ensure the following are in place before diving into JOIN operations:

  • Python Installed: Download the latest version from python.org.
  • MySQL Server Installed: Install via MySQL official site.
  • MySQL Connector for Python: Install it with: 
pip install mysql-connector-python

What is a JOIN in MySQL?

The JOIN clause is used to retrieve data from two or more tables based on a related column. There are several types of JOINs in MySQL:

  1. INNER JOIN: Fetches records with matching values in both tables.
  2. LEFT JOIN: Returns all records from the left table and the matched records from the right table.
  3. RIGHT JOIN: Returns all records from the right table and the matched records from the left table.
  4. FULL JOIN: Combines LEFT and RIGHT JOIN (not directly supported in MySQL, but achievable using UNION).

Python MySQL JOIN Examples

Step 1: Connect to the Database

Establish a connection to your database:

import mysql.connector

# Connect to MySQL
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

print("Connected to the database!")

Replace your_username, your_password, and your_database with your MySQL credentials.

Step 2: Perform an INNER JOIN

To fetch data that exists in both tables, use an INNER JOIN:

cursor = db.cursor()

sql = """
SELECT orders.order_id, customers.name, orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
"""

cursor.execute(sql)
result = cursor.fetchall()

for row in result:
    print(row)

Here, we are retrieving order IDs, customer names, and order amounts from two related tables: orders and customers.

Step 3: Perform a LEFT JOIN

To include all records from the left table, regardless of whether there is a match in the right table, use a LEFT JOIN:

sql = """
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
"""

cursor.execute(sql)
result = cursor.fetchall()

for row in result:
    print(row)

Step 4: Perform a RIGHT JOIN

To include all records from the right table, use a RIGHT JOIN:

sql = """
SELECT orders.order_id, customers.name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id
"""

cursor.execute(sql)
result = cursor.fetchall()

for row in result:
    print(row)

Step 5: Simulate a FULL JOIN

Since MySQL doesn’t natively support FULL JOIN, you can achieve it by combining LEFT and RIGHT JOIN using UNION:

sql = """
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id
"""

cursor.execute(sql)
result = cursor.fetchall()

for row in result:
    print(row)

Full Python Code Example

Here’s a complete Python script demonstrating an INNER JOIN:

import mysql.connector

# Connect to MySQL
db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Perform INNER JOIN
sql = """
SELECT orders.order_id, customers.name, orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
"""

cursor.execute(sql)
result = cursor.fetchall()

for row in result:
    print(row)

# Close the connection
db.close()

Exercises

Exercise 1: Join Products and Categories

Write a query to fetch product names and their corresponding category names from products and categories tables using INNER JOIN.

Exercise 2: Find Unmatched Records

Use a LEFT JOIN to list all customers who have not placed an order.

Exercise 3: Combine Data

Simulate a FULL JOIN between employees and departments to retrieve all records.

Why Use JOINs?

  • Efficient Data Retrieval: Combines related data from multiple tables in a single query.
  • Simplifies Queries: Reduces the need for multiple queries to gather related data.
  • Enables Complex Reports: Supports generating comprehensive reports from relational data.

Conclusion

The JOIN clause is a powerful feature of MySQL that enables you to work with relational data effectively. With Python, you can leverage JOINs to build robust, data-driven applications.

Leave a Comment