Python MySQL Join

In MySQL, the JOIN statement is used to combine rows from two or more tables, based on a related column between them. In Python, you can execute JOIN queries to combine data from multiple tables.

Types of JOIN:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. Unmatched rows from the right table will have NULL values.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. Unmatched rows from the left table will have NULL values.
• FULL JOIN: Returns all records when there is a match in either table (not supported directly by MySQL, you can use UNION of LEFT JOIN and RIGHT JOIN).

Let’s look at how to use JOIN with Python and MySQL.

1. Install MySQL Connector

If you haven’t installed the MySQL connector yet, use this command:

pip install mysql-connector-python

2. Example Tables

Let’s say you have two tables: customers and orders.

customers table:

orders table:

3. Python Script with INNER JOIN

Here’s an example of using INNER JOIN to retrieve customer names and their orders:

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query with INNER JOIN
sql = """
SELECT customers.name, orders.product 
FROM customers 
INNER JOIN orders ON customers.id = orders.customer_id
"""

# Execute the query
mycursor.execute(sql)

# Fetch all results
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

# Close the connection
mydb.close()

Explanation:

• INNER JOIN: Combines rows where the id in the customers table matches the customer_id in the orders table.
• SELECT customers.name, orders.product: Selects the name from the customers table and the product from the orders table.

4. Python Script with LEFT JOIN

If you want to include all customers, even those who haven’t placed any orders, you can use a LEFT JOIN:

sql = """
SELECT customers.name, orders.product 
FROM customers 
LEFT JOIN orders ON customers.id = orders.customer_id
"""

mycursor.execute(sql)

result = mycursor.fetchall()

for row in result:
    print(row)

This query will return all customers, including those who haven’t placed any orders, with NULL for the product column in the results.

5. Full Example: Using JOIN in Python MySQL Queries

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mynewdatabase"
)

# Create a cursor object
mycursor = mydb.cursor()

# SQL query to join customers and orders tables
sql = """
SELECT customers.name, orders.product 
FROM customers 
INNER JOIN orders ON customers.id = orders.customer_id
"""

# Execute the query
mycursor.execute(sql)

# Fetch all the results
result = mycursor.fetchall()

# Print the result
for row in result:
    print(row)

# Close the connection
mydb.close()

6. Using RIGHT JOIN

A RIGHT JOIN would include all records from the orders table and any matches from the customers table. If there are no matching customers, the result will have NULL for the customer data.

sql = """
SELECT customers.name, orders.product 
FROM customers 
RIGHT JOIN orders ON customers.id = orders.customer_id
"""

mycursor.execute(sql)

result = mycursor.fetchall()

for row in result:
    print(row)

7. Using JOIN with Multiple Conditions

You can also use a JOIN with additional conditions in the WHERE clause:

sql = """
SELECT customers.name, orders.product 
FROM customers 
INNER JOIN orders ON customers.id = orders.customer_id
WHERE customers.address LIKE '%Elm%'
"""

mycursor.execute(sql)

result = mycursor.fetchall()

for row in result:
    print(row)

This will return customers whose address contains “Elm” and their orders.

8. Closing the Connection

Always close the connection after you’re done:

mydb.close()

Summary:

• JOIN combines rows from two or more tables based on a related column.
• Use INNER JOIN for matching rows in both tables.
• Use LEFT JOIN to include all rows from the left table and matching rows from the right table.
• Use RIGHT JOIN to include all rows from the right table and matching rows from the left table.