Python MySQL Tutorial – How to Use SQL JOINs in Python

Last updated 5 months, 1 week ago | 459 views 75     5

Tags:- MySQL Python

When working with relational databases, it's common to split data across multiple tables. To retrieve related data stored in different tables, we use JOINs. In this tutorial, you’ll learn how to use SQL JOINs with Python and MySQL using the mysql-connector-python library.


Table of Contents

  1. What is a SQL JOIN?

  2. Types of JOINs in MySQL

  3. Prerequisites

  4. Install MySQL Connector

  5. Connect Python to MySQL

  6. Example Tables for JOIN

  7. Using INNER JOIN in Python

  8. LEFT JOIN, RIGHT JOIN, and FULL JOIN

  9. Full Working Python Example

  10. Tips and Common Pitfalls


1. What is a SQL JOIN?

A JOIN in SQL is used to combine rows from two or more tables based on a related column between them.

Example:

SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id;

This retrieves users and their related orders.


2. Types of JOINs in MySQL

JOIN Type Description
INNER JOIN Returns records with matching values in both tables
LEFT JOIN Returns all records from the left table, and matched records from the right
RIGHT JOIN Returns all records from the right table, and matched from the left
FULL JOIN Not directly supported by MySQL; can be simulated using UNION

⚙️ 3. Prerequisites

  • Python installed

  • MySQL Server running

  • Tables with related data (e.g., users and orders)

  • mysql-connector-python installed


4. Install MySQL Connector

Install with pip:

pip install mysql-connector-python

5. Connect Python to MySQL

import mysql.connector

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

cursor = db.cursor()

6. Example Tables for JOIN

Let's say we have two tables:

users Table:

id name
1 Alice
2 Bob

orders Table:

id user_id product
1 1 Laptop
2 2 Keyboard

7. Using INNER JOIN in Python

sql = """
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id
"""

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

for row in results:
    print(row)

Output:

('Alice', 'Laptop')
('Bob', 'Keyboard')

8. LEFT JOIN, RIGHT JOIN, and FULL JOIN

LEFT JOIN

sql = """
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id
"""
cursor.execute(sql)
for row in cursor.fetchall():
    print(row)

Returns all users, including those without orders.

RIGHT JOIN

sql = """
SELECT users.name, orders.product
FROM users
RIGHT JOIN orders ON users.id = orders.user_id
"""
cursor.execute(sql)
for row in cursor.fetchall():
    print(row)

Returns all orders, even if the user is missing (less common).

FULL JOIN (simulated using UNION)

sql = """
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT users.name, orders.product
FROM users
RIGHT JOIN orders ON users.id = orders.user_id
"""
cursor.execute(sql)
for row in cursor.fetchall():
    print(row)

9. Full Working Example

import mysql.connector

def fetch_user_orders():
    db = mysql.connector.connect(
        host="localhost",
        user="root",
        password="your_password",
        database="mydatabase"
    )

    cursor = db.cursor()

    sql = """
    SELECT users.name, orders.product
    FROM users
    INNER JOIN orders ON users.id = orders.user_id
    """

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

    for name, product in results:
        print(f"{name} ordered {product}")

    db.close()

# Run function
fetch_user_orders()

⚠️ 10. Tips and Common Pitfalls

Pitfall Solution
Using SELECT * in JOIN Always specify column names to avoid ambiguity
Missing ON clause Always use ON table1.col = table2.col to define the relationship
Duplicate column names Use aliases (e.g., users.name AS user_name)
Full JOIN in MySQL Simulate with LEFT JOIN UNION RIGHT JOIN
Performance issues Use indexes on foreign keys for faster joins

✅ Best Practices

  • Use INNER JOIN when you only need matching records

  • Use LEFT JOIN for optional data

  • Always test JOIN queries in SQL first before using in Python

  • Handle NULL values in your application logic


Summary Table

JOIN Type Use Case
INNER JOIN Only matching rows in both tables
LEFT JOIN All rows from left table, with matched data from right
RIGHT JOIN All rows from right table, with matched data from left
FULL JOIN Combine both using UNION in MySQL

Final Thoughts

JOINs are powerful tools in SQL that allow you to write cleaner, more efficient queries when dealing with related tables. By combining Python and MySQL JOINs, you can build robust database-driven applications that are both efficient and readable.