Python MySQL Join Tables
Connect tables together - combine data from multiple tables!
🔗 Connect Your Tables!
JOIN connects tables together - like matching students with their grades!
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="school"
)
cursor = mydb.cursor()
sql = """SELECT students.name, grades.subject, grades.score
FROM students
JOIN grades ON students.id = grades.student_id"""
cursor.execute(sql)
for result in cursor.fetchall():
print(f"{result[0]} got {result[2]} in {result[1]}")
JOIN
Connect
Multiple
Tables
Related
Data
What is JOIN?
JOIN connects related tables:
- 🔗 Connect Tables - Link related information together
- 📊 Complete Picture - Get all related data in one query
- 🎯 Avoid Repetition - Don't store the same data twice
- ⚡ Efficient - Get everything you need in one go
Example: Students and Grades
📚 Our Example Tables
students table: id, name, age
grades table: id, student_id, subject, score
We connect them using student_id!
Basic JOIN (INNER JOIN)
Connect Students with Their Grades
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="school"
)
cursor = mydb.cursor()
# Join students with their grades
sql = """SELECT students.name, grades.subject, grades.score
FROM students
INNER JOIN grades ON students.id = grades.student_id"""
cursor.execute(sql)
results = cursor.fetchall()
print("📊 Student Grades:")
for result in results:
print(f" {result[0]}: {result[2]} in {result[1]}")
mydb.close()
LEFT JOIN - Show All Students
Show All Students (Even Without Grades)
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="school"
)
cursor = mydb.cursor()
# Show all students, even if they don't have grades
sql = """SELECT students.name, grades.subject, grades.score
FROM students
LEFT JOIN grades ON students.id = grades.student_id"""
cursor.execute(sql)
results = cursor.fetchall()
print("👥 All Students (with or without grades):")
for result in results:
if result[1]: # If they have a grade
print(f" {result[0]}: {result[2]} in {result[1]}")
else: # If no grade found
print(f" {result[0]}: No grades yet")
mydb.close()
JOIN with Filtering
Find High Scores Only
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="school"
)
cursor = mydb.cursor()
# Find students with scores above 85
sql = """SELECT students.name, grades.subject, grades.score
FROM students
JOIN grades ON students.id = grades.student_id
WHERE grades.score > %s"""
cursor.execute(sql, (85,))
results = cursor.fetchall()
print("🏆 High Achievers (Score > 85):")
for result in results:
print(f" {result[0]}: {result[2]} in {result[1]}")
mydb.close()
Join Multiple Tables
Students + Grades + Teachers
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="school"
)
cursor = mydb.cursor()
# Join three tables: students, grades, and teachers
sql = """SELECT students.name, grades.subject, grades.score, teachers.name as teacher
FROM students
JOIN grades ON students.id = grades.student_id
JOIN teachers ON grades.teacher_id = teachers.id"""
cursor.execute(sql)
results = cursor.fetchall()
print("📚 Complete Grade Report:")
for result in results:
print(f" {result[0]}: {result[2]} in {result[1]} (Teacher: {result[3]})")
mydb.close()
JOIN with Calculations
Average Grade per Student
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="school"
)
cursor = mydb.cursor()
# Calculate average grade for each student
sql = """SELECT students.name, AVG(grades.score) as average_score
FROM students
JOIN grades ON students.id = grades.student_id
GROUP BY students.id, students.name"""
cursor.execute(sql)
results = cursor.fetchall()
print("📊 Student Averages:")
for result in results:
print(f" {result[0]}: {result[1]:.1f} average")
mydb.close()
Easy JOIN Function
Get Student Report
import mysql.connector
def get_student_report(student_name):
"""Get all grades for a specific student"""
try:
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="yourpassword",
database="school"
)
cursor = mydb.cursor()
sql = """SELECT students.name, grades.subject, grades.score
FROM students
JOIN grades ON students.id = grades.student_id
WHERE students.name = %s"""
cursor.execute(sql, (student_name,))
results = cursor.fetchall()
if results:
print(f"📋 Report for {student_name}:")
total_score = 0
for result in results:
print(f" {result[1]}: {result[2]}")
total_score += result[2]
average = total_score / len(results)
print(f" 📊 Average: {average:.1f}")
else:
print(f"❌ No grades found for {student_name}")
mydb.close()
except mysql.connector.Error as error:
print(f"❌ Error: {error}")
# Get report for a student
get_student_report("Alice Johnson")
JOIN Types Quick Reference
INNER JOIN
Only matching records
Students WITH grades
LEFT JOIN
All from left table
ALL students (with/without grades)
RIGHT JOIN
All from right table
ALL grades (with/without students)
Multiple JOINs
Connect 3+ tables
Students + Grades + Teachers