Python MySQL - Join

JOIN clause is used to combine rows from two or more tables based on a related column. It allows you to query data from multiple tables and return the result as a single set. There are several types of joins in SQL, each providing different results based on how data is combined.

Tables used in this article:

Types of Joins:

1. INNER JOIN

Returns records that have matching values in both tables. If no match is found, no record is returned.

Syntax:

SELECT column1, column2...
FROM table1
JOIN table2 ON table1.column = table2.column;

Example: Using INNER JOIN

In this example, we are using the INNER JOIN to retrieve the names of students who are present in both the Student table and the Student table (you can change the table names based on your requirements). This query returns the records where the Roll_no matches in both tables.

import mysql.connector

# Connecting to the database
mydb = mysql.connector.connect(
  host='localhost',
  database='College',
  user='root',  # Replace with your MySQL username
  password='your_password'  # Replace with your MySQL password
)

cs = mydb.cursor()

# INNER JOIN: only students with matching details
statement = """SELECT s.Name, d.Branch 
               FROM Student s 
               INNER JOIN StudentDetails d ON s.Roll_no = d.Roll_no"""

cs.execute(statement)

# Fetch and print the results
result_set = cs.fetchall()
for x in result_set:
    print(x)

# Disconnecting from the database
mydb.close()

Output:

2. LEFT JOIN

Returns all records from the left table and the matched records from the right table. If no match is found, the result is NULL on the right side.

Syntax:

SELECT column1, column2...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Example: Using LEFT JOIN

In this example, we use LEFT JOIN to fetch all student names from the STUDENT table and any matching records from the Student table. If a record doesn't have a match, the result will display NULL for columns from the right table.

import mysql.connector
 
# Connecting to the database
mydb = mysql.connector.connect(
    host='localhost',
    database='College',
    user='root',
    password='your_password'
)
cs = mydb.cursor()

# Left join
statement = """SELECT s.Name, d.Section 
               FROM Student s 
               LEFT JOIN StudentDetails d ON s.Roll_no = d.Roll_no"""

cs.execute(statement)

for row in cs.fetchall():
    print(row)

mydb.close()

Output:

3. RIGHT JOIN

Returns all records from the right table and the matched records from the left table. If no match is found, the result is NULL on the left side.

Syntax:

SELECT column1, column2...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Example: Using RIGHT JOIN

In this example, we demonstrate the RIGHT JOIN, which returns all records from the Student table and the matching records from the STUDENT table. If no match is found, the result will show NULL for columns from the left table.

import mysql.connector

mydb = mysql.connector.connect(
    host='localhost',
    database='College',
    user='root',
    password='your_password'
)

cs = mydb.cursor()

#Right Join
statement = """SELECT d.Branch, s.Name 
               FROM Student s 
               RIGHT JOIN StudentDetails d ON s.Roll_no = d.Roll_no"""

cs.execute(statement)

for row in cs.fetchall():
    print(row)

mydb.close()

Output: