How can you perform inner 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 inner join on two tables using MySQL in Python.

JOIN and INNER JOIN both work the same way. The INNER JOIN matches each row in one table with every row in other table and allows to combine the rows from both the tables which either have some common column or which satisfy some condition which is specified.

When applying join among two tables, we need to specify the condition based on which the tables will be joined.

Syntax

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

Let there be two tables, "Students" and "Department" as follows ?

Students

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

Department

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

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

Steps to Join 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

Here's how to perform an inner join on two tables using MySQL in Python ?

import mysql.connector

# Establish database connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# SQL query for inner join
query = """SELECT Students.Id, Students.Student_name, Department.Department_name
           FROM Students 
           INNER JOIN Department ON Students.Dept_Id = Department.Dept_Id"""

cursor.execute(query)
rows = cursor.fetchall()

for row in rows:
    print(row)

db.close()

Output

(1, 'Rahul', 'CSE')
(2, 'Rohit', 'Mathematics')
(3, 'Kirat', 'Physics')

Notice that the 4th row is not included in the result because there is no matching record for the 4th row of Students table in the Department table. This is how INNER JOIN works ? it only returns rows that have matching values in both tables.

How Inner Join Works

The INNER JOIN operation compares each row from the Students table with every row in the Department table. It only includes rows in the result where the join condition (Students.Dept_Id = Department.Dept_Id) is satisfied in both tables.

In our example, student "Inder" with Dept_Id = 125 is excluded because there's no department with ID 125 in the Department table.

Conclusion

INNER JOIN in MySQL with Python allows you to combine related data from multiple tables. Use mysql.connector to establish connection and execute join queries. Only matching records from both tables appear in the result set.