Explain and show the use of UNION in MySQL using Python?

The UNION statement combines results from two or more SELECT queries, automatically removing duplicate rows. This is useful when you need to retrieve data from multiple tables with similar structure.

Syntax

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2

Requirements for UNION

To perform UNION operations, the following conditions must be met ?

• Both SELECT statements must return the same number of columns

• Corresponding columns must have compatible data types

• Columns must be in the same order in both queries

Sample Tables

Let's work with two sample tables to demonstrate UNION operations ?

Students Table

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

Department Table

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

Using UNION with Python MySQL Connector

Here's how to perform a UNION operation to get all distinct department IDs from both tables ?

import mysql.connector

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

cursor = db.cursor()

# UNION query to get all distinct Dept_id values
query = """
SELECT Dept_id FROM Students 
UNION 
SELECT Dept_id FROM Department
"""

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

print("Distinct Department IDs:")
for row in rows:
    print(row[0])

db.close()

The output shows all unique department IDs from both tables ?

Distinct Department IDs:
120
121
125
123
122

UNION vs UNION ALL

You can also use UNION ALL to include duplicate rows ?

import mysql.connector

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

cursor = db.cursor()

# Using UNION ALL to include duplicates
query_all = """
SELECT Dept_id FROM Students 
UNION ALL 
SELECT Dept_id FROM Department
"""

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

print("All Department IDs (with duplicates):")
for row in rows:
    print(row[0])

db.close()

Key Points

Conclusion

UNION operations in MySQL allow you to combine results from multiple SELECT statements. Use UNION to eliminate duplicates or UNION ALL when you need all rows including duplicates.