MySQL Join in Python

You can perform SQL JOIN operations (e.g., INNER JOIN, LEFT JOIN) in Python using the mysql-connector-python library.

1. Install MySQL Connector

Make sure you have the MySQL connector installed:

pip install mysql-connector-python

2. Connect to MySQL Database

First, establish a connection to your MySQL database:

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase"  # Change to your database name)# Create a cursor objectcursor = conn.cursor()print("Connected to MySQL database successfully!")

3. Create Sample Tables

Let's assume you have two tables: employees and departments.

# Create employees tablecursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    department_id INT)""")# Create departments tablecursor.execute("""CREATE TABLE IF NOT EXISTS departments (    id INT AUTO_INCREMENT PRIMARY KEY,    department_name VARCHAR(100))""")# Insert data into employees tablecursor.execute("""INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2), ('Charlie', 1)""")# Insert data into departments tablecursor.execute("""INSERT INTO departments (department_name) VALUES ('HR'), ('IT')""")conn.commit()

4. INNER JOIN Example

An INNER JOIN will return only the rows that have matching values in both tables.

# SQL query to perform INNER JOINinner_join_query = """SELECT employees.name, departments.department_nameFROM employeesINNER JOIN departments ON employees.department_id = departments.id"""cursor.execute(inner_join_query)# Fetch and print the resultsresults = cursor.fetchall()for row in results:    print(row)

This query retrieves the names of employees and their respective department names, where the department_id from the employees table matches the id in the departments table.

5. LEFT JOIN Example

A LEFT JOIN will return all records from the left table (employees), and the matched records from the right table (departments). If there's no match, the result will contain NULL for columns from the right table.

# SQL query to perform LEFT JOINleft_join_query = """SELECT employees.name, departments.department_nameFROM employeesLEFT JOIN departments ON employees.department_id = departments.id"""cursor.execute(left_join_query)# Fetch and print the resultsresults = cursor.fetchall()for row in results:    print(row)

This will return all employees along with their department names, even if they don't belong to a department (i.e., NULL if no department is found).

6. RIGHT JOIN Example

A RIGHT JOIN is similar to a LEFT JOIN, but it returns all records from the right table (departments), and the matched records from the left table (employees).

# SQL query to perform RIGHT JOINright_join_query = """SELECT employees.name, departments.department_nameFROM employeesRIGHT JOIN departments ON employees.department_id = departments.id"""cursor.execute(right_join_query)# Fetch and print the resultsresults = cursor.fetchall()for row in results:    print(row)

This will return all departments along with their employee names, showing NULL for departments with no employees.

7. Closing the Connection

Always close the connection when done.

cursor.close()conn.close()print("MySQL connection closed.")

Final Complete Code

import mysql.connector# Connect to MySQL Databaseconn = mysql.connector.connect(    host="localhost",    user="root",    password="password",    database="mydatabase")cursor = conn.cursor()# Create employees and departments tablescursor.execute("""CREATE TABLE IF NOT EXISTS employees (    id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    department_id INT)""")cursor.execute("""CREATE TABLE IF NOT EXISTS departments (    id INT AUTO_INCREMENT PRIMARY KEY,    department_name VARCHAR(100))""")# Insert data into employees tablecursor.execute("""INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2), ('Charlie', 1)""")# Insert data into departments tablecursor.execute("""INSERT INTO departments (department_name) VALUES ('HR'), ('IT')""")conn.commit()# Perform INNER JOINinner_join_query = """SELECT employees.name, departments.department_nameFROM employeesINNER JOIN departments ON employees.department_id = departments.id"""cursor.execute(inner_join_query)results = cursor.fetchall()print("INNER JOIN Results:")for row in results:    print(row)# Perform LEFT JOINleft_join_query = """SELECT employees.name, departments.department_nameFROM employeesLEFT JOIN departments ON employees.department_id = departments.id"""cursor.execute(left_join_query)results = cursor.fetchall()print("\nLEFT JOIN Results:")for row in results:    print(row)# Perform RIGHT JOINright_join_query = """SELECT employees.name, departments.department_nameFROM employeesRIGHT JOIN departments ON employees.department_id = departments.id"""cursor.execute(right_join_query)results = cursor.fetchall()print("\nRIGHT JOIN Results:")for row in results:    print(row)# Close the connectioncursor.close()conn.close()

Summary of Joins

This is how you can perform SQL JOIN operations using Python! ?