Use of UNION in MySQL with Python

The UNION statement is used to combine the results of the two SELECT queries without repeating the duplicate values. If both the SELECT queries return same row, it is listed only once.

To perform the UNION on two select statements,

• The number of columns returned must be same

• The datatypes of the columns must be same

• The columns must be returned in same order by both the select statements.

Syntax

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2

Steps to perform union of two select queries using MySQL in python

• import MySQL connector

• 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

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 select Dept_id from both the tables and perform union on the results. This will return us all the distinct dept_ids present in both the tables.

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 Dept_id FROM Students UNION SELECT Dept_id FROM Department"
cursor.execute(query)

rows=cursor.fetchall()
for row in rows:
   print(row)

db.close()

Output

120
121
125
123
122