CodeCapsule

Python MySQL JOIN

by

🐍 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)
);

Insert sample data:

INSERT INTO students (name, age) VALUES ('Vipul', 25), ('Riya', 22), ('Amit', 24);

INSERT INTO courses (student_id, course_name) VALUES (1, 'Python'), (1, 'Java'), (2, 'C++');

🔹 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()

Output:

('Vipul', 25, 'Python')
('Vipul', 25, 'Java')
('Riya', 22, 'C++')

Only students who have courses appear (INNER JOIN).

🔹 4️⃣ LEFT JOIN in Python

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

for row in results:
print(row)

Output:

('Vipul', 25, 'Python')
('Vipul', 25, 'Java')
('Riya', 22, 'C++')
('Amit', 24, None)

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

🔹 5️⃣ RIGHT JOIN in Python

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

for row in results:
print(row)

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

🔹 6️⃣ JOIN Multiple Tables

You can JOIN more than two tables:

-- Add table: grades
CREATE TABLE grades (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
grade VARCHAR(2),
FOREIGN KEY (student_id) REFERENCES students(id)
);

Python query:

query = """
SELECT s.name, c.course_name, g.grade
FROM students s
INNER JOIN courses c ON s.id = c.student_id
INNER JOIN grades g ON s.id = g.student_id
"""
cursor.execute(query)
results = cursor.fetchall()

for row in results:
print(row)

🔹 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().

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *