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

PythonServer Side ProgrammingProgramming

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 FULL join on two tables. In the FULL JOIN, all the records from both the tables are included in the result. For the records for which no matching is found, NULL is inserted on the either sides.

Syntax

SELECT column1, column2...
FROM table_1
FULL 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     |    121    |
|    4     |    Inder     |    123    |
+----------+--------------+-----------+

Department

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

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

Steps to perform full 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="yourpassword",database="database_name")

cursor=db.cursor()
query="SELECT Students.Id,Students.Student_name,Department.Department_name
FROM Students FULL 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’, ‘Mathenatics’)
(4, ‘Inder’, None)
(None, ‘Physics’)

Notice, all the records from both the tables are included in the result even though there are no matching records for some of the records.

raja
Published on 10-Jun-2021 12:53:23
Advertisements