Python MySQL - Table Joins

In this tutorial, we will learn how to join two or more MySQL tables in Python.

In order to combine two or more tables in MySQL, JOIN statement is used. One thing to note here is that there must be a common column in both tables based on which this operation will be performed.

We have already created the students table in the studytonight database(from the Python MySQL create table tutorial). Let us create another table named results after that we will join both the tables.

Below we have code to create a table named results. Both results and students table have one field common that is rollno.

Yuo can directly run the query to create the new table in MySQL command line.

Python MySQL - Joining two tables

Below we have a code snippet where we will join two tables named results and students based on the column rollno:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()

sql = "SELECT students.rollno, students.name,students.Branch,students.Address, results.status from students INNER JOIN results ON students.rollno=results.rollno;"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
    print(x)

The output of the above code will be:

(1, 'Ramesh', 'CSE', '149 Indirapuram', 'Pass') (2, 'Peter', 'ME', 'Noida', 'Fail') (3, 'Navin', 'CE', 'New Delhi', 'Pass')

The above output indicates that both tables are joined.

Here is the snapshot of the actual output:

Python MySQL - Left Join

It is important to note that the INNER JOIN(which we covered in the example above) only shows the rows in the resultset when there is a match.

If you want to fetch all the records fromthea left-hand side table even if there is no match then Left Join will be used.

Let us see an example given below for the Left Join:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()

sql = "SELECT students.rollno, students.name,students.Branch,students.Address, results.status from students LEFT JOIN results ON students.rollno=results.rollno;"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
    print(x)

The output of the above code is as follows. Let us see:

(1, 'Ramesh', 'CSE', '149 Indirapuram', 'Pass') (2, 'Peter', 'ME', 'Noida', 'Fail') (3, 'Navin', 'CE', 'New Delhi', 'Pass') (5, 'suraj', 'ECE', 'Panipat', None) (6, 'Mukesh', 'CSE', 'Samalkha', None)

In the above output, the status of rollno 5 and 6 is None because their result is not mentioned in the results table. But as we have applied LEFT JOIN so the query selects all the rows from the left table even if there is no match.

Here is the snapshot of the actual output:

Python MySQL - Right Join

If you want to fetch all the records from the right-hand side table even if there is no match then Right Join will be used.

Let us see an example given below for the Right Join. The code given below will fetch all the rows from the right-hand side table. It will not return those rows with rollno 5 and 6:

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "yourusername",
    passwd = "yourpassword",
    database = "studytonight"
)

cursor = db.cursor()

sql = "SELECT students.rollno, students.name,students.Branch,students.Address, results.status from students RIGHT JOIN results ON students.rollno=results.rollno;"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
    print(x)

The output of the above code is as follows:

(1, 'Ramesh', 'CSE', '149 Indirapuram', 'Pass') (2, 'Peter', 'ME', 'Noida', 'Fail') (3, 'Navin', 'CE', 'New Delhi', 'Pass')

Here is the snapshot of the actual output:

And with this we have covered all the basics of Python MySQL. If you have to develop an application in which you want to have a database with multipl tables, in which you want to store data and retrieve data from the tables, then we hope these tutorials help you.