Join Operation

The JOIN clause is used to combine rows from two or more tables based on a related column between them. In this example, we'll demonstrate how to use a LEFT JOIN in Python to merge data from the students and courses tables.

Example: Joining 'students' and 'courses' Tables


import mysql.connector

# Establishing a connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpassword',
    database='student_db'
)
cursor = connection.cursor()

# Creating the 'students' table
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(2)
)
""")
# Inserting data into 'students' table
cursor.execute("INSERT INTO students (name, age, grade) VALUES ('Alice', 20, 'A')")
cursor.execute("INSERT INTO students (name, age, grade) VALUES ('Bob', 22, 'B')")
cursor.execute("INSERT INTO students (name, age, grade) VALUES ('Charlie', 21, 'A')")
connection.commit()

# Creating another table 'courses' for join operation
cursor.execute("""
CREATE TABLE IF NOT EXISTS courses (
    student_id INT,
    course_name VARCHAR(100)
)
""")
# Inserting data into 'courses' table
cursor.execute("INSERT INTO courses (student_id, course_name) VALUES (1, 'Math')")
cursor.execute("INSERT INTO courses (student_id, course_name) VALUES (2, 'Science')")
connection.commit()

# Performing a LEFT JOIN operation
cursor.execute("""
SELECT students.id, students.name, students.grade, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id
""")
results = cursor.fetchall()

print("Joined Data:")
for row in results:
    print(row)

cursor.close()
connection.close()

Output

Joined Data:
(1, 'Alice', 'A', 'Math')
(2, 'Bob', 'B', 'Science')
(3, 'Charlie', 'A', None)

Data in the 'students' Table

id name age grade
1 Alice 20 A
2 Bob 22 B
3 Charlie 21 A

Data in the 'courses' Table

student_id course_name
1 Math
2 Science

Result of the LEFT JOIN Operation

id name grade course_name
1 Alice A Math
2 Bob B Science
3 Charlie A None

As shown above:
- The LEFT JOIN operation returns all rows from the 'students' table, even if there are no matches in the 'courses' table.
- For 'Charlie', since there was no matching course entry, the result shows None in the course_name column.