A join in MySQL is used to combine rows from two or more tables based on a related column between them. In Python, we can perform the join operation between MySQL tables using the JOIN clause in the SQL query.
In this tutorial, we will learn how to perform various types of joins using the JOIN clause in MySQL with Python.
Setup
Before we proceed, let’s setup the MySQL database and create some sample tables that we will use to perform the join operation.
We will install the PyMySQL module, which is a pure-python MySQL client library, to interact with MySQL from Python. You can install it using the pip command as follows:
pip install PyMySQL
Create a database and two tables users and user_details with sample data as below.
CREATE DATABASE sample_db;
USE sample_db;
CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL
);
CREATE TABLE user_details (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT(6) UNSIGNED NOT NULL,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50),
FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO users (username, password)
VALUES ('admin', 'admin'),
('johndoe', 'johndoe123'),
('janedoe', 'janedoe123');
INSERT INTO user_details (user_id, first_name, last_name, email)
VALUES (1, 'Admin', 'User', '[email protected]'),
(2, 'John', 'Doe', '[email protected]'),
(3, 'Jane', 'Doe', '[email protected]');
Now that we have created the required tables and added some data to them, let’s move on to the next section to perform some join operations on these tables.
The JOIN Clause
The JOIN clause is used to combine rows from two or more tables based on a related column between them. It can be used in conjunction with SELECT, UPDATE, and DELETE statements. There are various types of JOIN clauses available in MySQL, such as:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN – Not supported in MySQL. We can use UNION to implement Full Outer Join in MySQL.
In the following sections, we will learn how to use each type of JOIN in Python with MySQL.
INNER JOIN
INNER JOIN returns all rows from both tables where the join condition is satisfied. It only returns the rows where there is a match between the columns in both tables.
Let’s see an example to understand the INNER JOIN:
import pymysql # Establishing a connection to the database conn = pymysql.connect(host='localhost', user='root', password='', db='sample_db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # SQL query to perform INNER JOIN sql = "SELECT * FROM users INNER JOIN user_details ON users.id = user_details.user_id" # Executing the SQL query using the execute() method cursor.execute(sql) # Fetching all rows from the resultset results = cursor.fetchall() # Printing the rows for row in results: print(row[1], row[2], row[5]) # Closing the cursor and database connection cursor.close() conn.close()
Output:
Admin User [email protected] John Doe [email protected] Jane Doe [email protected]
In the above example, we have performed an INNER JOIN between the users and user_details table using the id column from the users table and the user_id column from the user_details table as the join condition. We have selected all columns from both tables using the * operator.
The result of the INNER JOIN is a new table that contains all the rows from both tables where the id column in the users table and the user_id column in the user_details table are equal. Only the rows where there is a match between the columns in both tables are returned.
LEFT JOIN
LEFT JOIN returns all rows from the left table and matching rows from the right table. If there are no matching rows in the right table, NULL values are returned for the right table’s columns.
Let’s see an example to understand the LEFT JOIN:
import pymysql # Establishing a connection to the database conn = pymysql.connect(host='localhost', user='root', password='', db='sample_db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # SQL query to perform LEFT JOIN sql = "SELECT * FROM users LEFT JOIN user_details ON users.id = user_details.user_id" # Executing the SQL query using the execute() method cursor.execute(sql) # Fetching all rows from the resultset results = cursor.fetchall() # Printing the rows for row in results: print(row[1], row[2], row[5]) # Closing the cursor and database connection cursor.close() conn.close()
Output:
Admin User [email protected] John Doe [email protected] Jane Doe [email protected]
In the above example, we have performed a LEFT JOIN between the users and user_details table using the id column from the users table and the user_id column from the user_details table as the join condition. We have selected all columns from both tables using the * operator.
The result of the LEFT JOIN is a new table that contains all the rows from the users table and the matching rows from the user_details table. If there are no matching rows in the user_details table, NULL values are returned for the user_details table’s columns.
RIGHT JOIN
RIGHT JOIN is similar to LEFT JOIN, except it returns all rows from the right table and matching rows from the left table. If there are no matching rows in the left table, NULL values are returned for the left table’s columns.
Let’s see an example to understand the RIGHT JOIN:
import pymysql # Establishing a connection to the database conn = pymysql.connect(host='localhost', user='root', password='', db='sample_db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # SQL query to perform RIGHT JOIN sql = "SELECT * FROM users RIGHT JOIN user_details ON users.id = user_details.user_id" # Executing the SQL query using the execute() method cursor.execute(sql) # Fetching all rows from the resultset results = cursor.fetchall() # Printing the rows for row in results: print(row[1], row[2], row[5]) # Closing the cursor and database connection cursor.close() conn.close()
Output:
Admin User [email protected] John Doe [email protected] Jane Doe [email protected]
In the above example, we have performed a RIGHT JOIN between the users and user_details table using the id column from the users table and the user_id column from the user_details table as the join condition. We have selected all columns from both tables using the * operator.
The result of the RIGHT JOIN is a new table that contains all the rows from the user_details table and the matching rows from the users table. If there are no matching rows in the users table, NULL values are returned for the users table’s columns.
Cross Join
Cross Join is a type of join in which every row from the first table is combined with every row from the second table.
Let’s see an example to understand the Cross Join:
import pymysql # Establishing a connection to the database conn = pymysql.connect(host='localhost', user='root', password='', db='sample_db') # Creating a cursor object using the cursor() method cursor = conn.cursor() # SQL query to perform Cross Join sql = "SELECT * FROM users CROSS JOIN user_details" # Executing the SQL query using the execute() method cursor.execute(sql) # Fetching all rows from the resultset results = cursor.fetchall() # Printing the rows for row in results: print(row[1], row[2], row[5]) # Closing the cursor and database connection cursor.close() conn.close()
Output:
Admin User [email protected] John Doe [email protected] Jane Doe [email protected] Admin User [email protected] John Doe John [email protected] Jane Doe John [email protected] Admin User [email protected] John Doe [email protected] Jane Doe Jane [email protected]
In the above example, we have performed a Cross Join between the users and user_details table using the * operator to select all columns from both tables.
The result of the Cross Join is a new table that contains all possible combinations of rows from both tables.
Conclusion
In this tutorial, we have learned how to perform various types of joins in MySQL using Python. Joins are a very useful feature of SQL that allow us to combine data from two or more tables based on a related column between them.
It is important to understand the different types of join clauses and how they work so that we can use them efficiently in our database queries. The examples in this tutorial should help you get started with performing joins in MySQL with Python.
