How can you perform left join on two tables using MySQL in Python?

We can join two tables in SQL based on a common column between them or based on some specified condition. There are different types of JOIN available to join two SQL tables.

Here, we will discuss about the LEFT join on two tables. The tables can be joined using the LEFT join. In the LEFT JOIN, all the records from the first table or the left table are always included in the result. From the right table, the matching records are joined to the records of the left table. If there is no matching record found for a row in left table, then None is joined with that record.

The tables are joined on basis of some condition. But all the records of the left table will always be included in the result irrespective of the condition.

Syntax

SELECT column1, column2...
FROM table_1
LEFT JOIN table_2 ON condition;

Let there be two tables, “Students” and “Department” as folllows −

Students

+----------+--------------+-----------+
|    id    | Student_name | Dept_id   |
+----------+--------------+-----------+
|    1     |    Rahul     |    120    |
|    2     |    Rohit     |    121    |
|    3     |    Kirat     |    125    |
|    4     |    Inder     |    123    |
+----------+--------------+-----------+

Department

+----------+-----------------+
| Dept_id  | Department_name |
+----------+-----------------+
|    120   |    CSE          |
|    121   |    Mathematics  |
|    122   |    Physics      |
+----------+-----------------+

We will perform left join on the above tables based on the dept_id which is common in both the tables.

Steps to perform left join on two tables using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• close the connection

Example

import mysql.connector
db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()
query="SELECT Students.Id,Students.Student_name,Department.Department_name
FROM Students LEFT JOIN Department ON Students.Dept_Id=Department.Dept_Id"
cursor.execute(query)
rows=cursor.fetchall()
for x in rows:
   print(x)

db.close()

Output

(1, ‘Rahul’, ‘CSE’)
(2, ‘Rohit’, ‘Mathematics’)
(3, ‘Kirat’, None)
(4, ‘Inder’, None)

Notice, all the records from the left table are included in the result even though there are no matching records for the last 2 rows.