Python MySQL JOIN

🐍 Python MySQL JOIN — Complete Guide

In MySQL, JOINs are used to combine rows from two or more tables based on a related column.
Python can execute JOIN queries using the mysql-connector-python module.

1️⃣ Types of JOINs in MySQL

JOIN Type	Description
INNER JOIN	Returns rows with matching values in both tables
LEFT JOIN / LEFT OUTER JOIN	Returns all rows from left table, matching from right table or NULL
RIGHT JOIN / RIGHT OUTER JOIN	Returns all rows from right table, matching from left table or NULL
FULL OUTER JOIN	Returns all rows when there is a match in one of the tables (MySQL requires workaround)

2️⃣ Example: Setup Two Tables

-- Students Table
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);-- Courses Table
CREATE TABLE courses (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_name VARCHAR(50),
FOREIGN KEY (student_id) REFERENCES students(id)
);

-- Students Table

CREATE TABLE students (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50),

age INT

);-- Courses Table

CREATE TABLE courses (

id INT AUTO_INCREMENT PRIMARY KEY,

student_id INT,

course_name VARCHAR(50),

FOREIGN KEY (student_id) REFERENCES students(id)

);

Insert sample data:

3️⃣ INNER JOIN in Python

import mysql.connector

conn = mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="testdb"
)
cursor = conn.cursor()

query = """
SELECT students.name, students.age, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id
"""
cursor.execute(query)
results = cursor.fetchall()

for row in results:
print(row)

cursor.close()
conn.close()

import mysql.connector

conn = mysql.connector.connect(

host="localhost",

user="root",

password="your_password",

database="testdb"

)

cursor = conn.cursor()

query = """

SELECT students.name, students.age, courses.course_name

FROM students

INNER JOIN courses ON students.id = courses.student_id

"""

cursor.execute(query)

results = cursor.fetchall()

for row in results:

print(row)

cursor.close()

conn.close()

Output:

Only students who have courses appear (INNER JOIN).

4️⃣ LEFT JOIN in Python

Output:

Shows all students; if no course, None is returned.

5️⃣ RIGHT JOIN in Python

Output: Only rows where courses exist, with corresponding students.

6️⃣ JOIN Multiple Tables

You can JOIN more than two tables:

Python query:

7️⃣ Summary Table of JOINs

JOIN Type	Purpose	Python Usage
INNER JOIN	Only matching rows	`INNER JOIN table2 ON table1.id = table2.fk`
LEFT JOIN	All rows from left table	`LEFT JOIN table2 ON ...`
RIGHT JOIN	All rows from right table	`RIGHT JOIN table2 ON ...`
FULL OUTER JOIN	All rows from both tables	Use `UNION` in MySQL
Multiple JOINs	Join 3+ tables	Chain `JOIN` statements

💡 Tips:

Always use aliases (s, c, etc.) for readability.
fetchall() returns all results; use fetchone() for one row.
JOIN queries in Python are executed same as in MySQL, just pass them as a string to cursor.execute().

Python MySQL JOIN