Self 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 SELF join on two tables.

As the name signifies, SELF join is the join with the table itself. This join is performed between two copies of the same table. The rows of the table are matched with the other rows of the same table based on some condition.

Syntax

SELECT a.coulmn1 , b.column2
FROM table_name a, table_name b
WHERE condition;

a and b are the two aliases of the same table.

table_name is the name of the table on which self join is to be performed. The same table name is used at both places since we are performing self join.

Let there be a table named “Students” as folllows −

Students

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

We will perform self join on the above table based on the condition a.Dept_id<b.Dept_id. This means that each row in the table will be matched with the other rows in the table whose Dept_id are less than that particular row.

Steps to perform self 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 a.Dept_id,b.Student_name FROM Students a, Students b
WHERE a.Dept_id<b.Dept_id"
cursor.execute(query)
rows=cursor.fetchall()
for x in rows:
   print(x)

db.close()

Output