In SQL, the JOIN clause enables the merging of columns from two or more tables by utilizing a shared column that exists among them. There are three primary categories of joins in SQL, with each category yielding different outcomes depending on the manner in which the data is organized.
MySQL Join Operation Example
Imagine that we possess two tables, namely the "User" table and the "Vehicle" table:
Table "User"
{ id: 1, name: 'Reema', fav: 154},
{ id: 2, name: 'Ayon', fav: 154},
{ id: 3, name: 'Anshul', fav: 155},
{ id: 4, name: 'Heena', fav:},
{ id: 5, name: 'Megha', fav:}
Table "Vehicle"
{ id: 154, name: 'Royal Enfield' },
{ id: 155, name: 'Honda City' },
{ id: 156, name: 'Tesla Model 3' }
We are going to establish these two tables using Python and populate them with the corresponding values.
import mysql.connector
# Connecting to MySQL server
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
db_cursor = db_connection.cursor()
# Drop old tables if they exist
db_cursor.execute("DROP TABLE IF EXISTS User")
db_cursor.execute("DROP TABLE IF EXISTS Vehicle")
# Create User table
db_cursor.execute("""
CREATE TABLE User (
id INT PRIMARY KEY,
name VARCHAR(50),
fav INT
)
""")
# Create Vehicle table
db_cursor.execute("""
CREATE TABLE Vehicle (
id INT PRIMARY KEY,
name VARCHAR(50)
)
""")
# Insert sample data into User
user_values = [
(1, 'Reema', 154),
(2, 'Ayon', 154),
(3, 'Anshul', 155),
(4, 'Heena', None),
(5, 'Megha', None)
]
db_cursor.executemany("INSERT INTO User (id, name, fav) VALUES (%s, %s, %s)", user_values)
# Insert sample data into Vehicle
vehicle_values = [
(154, 'Royal Enfield'),
(155, 'Honda City'),
(156, 'Tesla Model 3')
]
db_cursor.executemany("INSERT INTO Vehicle (id, name) VALUES (%s, %s)", vehicle_values)
db_connection.commit()
print("Tables created and data inserted successfully!")
db_cursor.close()
db_connection.close()
Output:
It would create the User and Vehicle table.
The two tables can be connected through the User's favorite field and the id field of the Vehicles.
The JOIN operation in MySQL enables users to retrieve data from multiple tables and present the results as a unified dataset. SQL offers various types of joins, each producing distinct outputs depending on the method used to combine the data.
Let's understand each one of the Joins in detail.
Types of Joins
In Python MySQL, there are three categories of join operations. Let’s explore each type along with examples.
1. INNER JOIN
It displays entries that have corresponding values present in both tables. If a match is not located within the table, it yields no results.
Syntax of Inner Join
It has the following syntax:
SELECT col1, col2...
FROM table1
JOIN table2 ON table1.col = table2.col;
Inner Join Example
In the example provided, we are utilizing the INNER JOIN clause to retrieve the user names that exist in both the User table and the Vehicle table. We will construct an INNER JOIN query that will produce results where the fav field corresponds to the id in both tables.
import mysql.connector
# Connecting to MySQL database
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
db_cursor = db_connection.cursor()
# Writing an INNER JOIN query
sql = """
SELECT User.name AS user, Vehicle.name AS favourite
FROM User
INNER JOIN Vehicle ON User.fav = Vehicle.id
"""
db_cursor.execute(sql)
results = db_cursor.fetchall()
# for loop to print the fetched output
for row in results:
print(row)
# closing all the connections
db_cursor.close()
db_connection.close()
Output:
('Reema', 'Royal Enfield')
('Ayon', 'Royal Enfield')
('Anshul', 'Honda City')
Explanation:
In the preceding program, we established a connection between Python and the MySQL database by utilizing the mysql.connector library. We initiated a connection and generated a cursor object.
We have constructed a SQL query utilizing the INNER JOIN clause to merge common entries from the users and products tables. The condition users.fav = products.id will return records that match exactly across both tables. Upon executing this query, we will obtain all relevant rows and assign the results to the myresult variable.
Finally, we will utilize a For loop to print each entry, allowing us to retrieve the records that exist in both tables.
2. LEFT JOIN
In the previously mentioned output, Megha and Heena were omitted. This exclusion occurs because INNER JOIN exclusively returns entries that have corresponding matches, signifying that it fetches the shared values from two distinct tables.
To display all entries from the user table, regardless of whether they have a favorite product or not, utilize the LEFT JOIN statement.
Left Join Syntax
It has the following syntax:
SELECT col1, col2...
FROM table1
LEFT JOIN table2 ON table1.col = table2.col;
Left Join Example
In this illustration, we employ the LEFT JOIN to obtain the names of users contained in the User table alongside their corresponding entries from the Vehicle table. This LEFT JOIN operation will yield every entry from the User table along with the associated records from the Vehicle table; if there are no corresponding entries in the Vehicle table, then NULL will be displayed for the Vehicle values.
import mysql.connector
# Establishing connection to the MySQL database
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
# Create a cursor object
db_cursor = db_connection.cursor()
# Writing the SQL SELECT query using LEFT JOIN
sql_query = """
SELECT
users.name AS user,
products.name AS favorite
FROM
users
LEFT JOIN
products
ON
users.fav = products.id
"""
# Executing the SQL query
db_cursor.execute(sql_query)
# Fetching all results from the executed query
result_set = db_cursor.fetchall()
# Printing each record
for record in result_set:
print(record)
Output:
('Reema', 'Royal Enfield')
('Ayon', 'Royal Enfield')
('Anshul', 'Honda City')
('Heena', None)
('Megha', None)
Explanation:
In this example, we have constructed a SQL query that retrieves data from two tables: users and products. Subsequently, we utilized a LEFT JOIN to ensure that all users are included in the results, regardless of whether they have a corresponding entry in the favorite product.
The join is established with the expression users.fav = products.id. Upon executing this query, it will yield all entries from the left table alongside the matching entries from the right table.
3. Right Join
A right join displays all the columns from the table located on the right-hand side, assuming we have two tables within the database. In instances where no corresponding match is found in the left-hand table, it will return NULL values on that side.
Right Join Syntax
It has the following syntax:
SELECT col1, col2...
FROM table1
LEFT JOIN table_name2 ON table_name1.col = table2.col;
Execute the following query on the MySQL server.
Right Join Example
In this instance, we employ the RIGHT JOIN to fetch the names of vehicles from the Vehicle table in conjunction with their corresponding entries from the User table. This query yields every entry from the Vehicle table along with the associated records from the User table. If there are no corresponding entries, the User values will return as None.
import mysql.connector
# Establishing connection to the MySQL database
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
# Create a cursor object
db_cursor = db_connection.cursor()
# Writing the SQL SELECT query using RIGHT JOIN
sql_query = """
SELECT
User.name AS user,
Vehicle.name AS favorite
FROM
User
RIGHT JOIN
Vehicle
ON
User.fav = Vehicle.id
"""
# Executing the SQL query
db_cursor.execute(sql_query)
# Fetching all results
result_set = db_cursor.fetchall()
# Printing each record using for loop
for record in result_set:
print(record)
# closing all the connections
db_cursor.close()
db_connection.close()
Output:
('Ayon', 'Royal Enfield')
('Reema', 'Royal Enfield')
('Anshul', 'Honda City')
(None, 'Tesla Model 3')
Explanation:
The SQL query employs a RIGHT JOIN, which indicates that every row from the Vehicle table will be retrieved, accompanied by corresponding rows from the User table. In instances where a match is absent, None will be displayed for the columns related to the User. The join condition User.fav = Vehicle.id establishes a connection between the favorite vehicle IDs of users and the vehicle IDs present in the Vehicle table.
Conclusion
The MySQL JOIN functionality in Python is a crucial operation that enables the merging of columns from multiple tables through a shared column. By utilizing various types of joins, including INNER JOIN, LEFT JOIN, and RIGHT JOIN, we can retrieve distinct sets of results. For example, if we aim to obtain solely the matching entries, all entries from one specific table, or every entry from another table, this can be accomplished through the use of JOIN.
Python MYSQL Join Operation FAQs
1. How to perform a JOIN operation in Python?
To execute a JOIN operation while linking Python with MySQL, you can utilize the mysql.connector library. First, you will need to establish a cursor. Following that, you should formulate a SQL JOIN query, run it by employing the cursor.execute method, and subsequently retrieve the results using either fetchall or fetchone.
2. What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?
| S.NO | INNER JOIN | LEFT JOIN | RIGHT JOIN |
|---|---|---|---|
1 |
An INNER JOIN only shows the records where there is a match in both tables. | LEFT JOIN returns all records from the left table and common records from the right table. | Right join returns all the records of the right-hand side table, as we have two tables in the database. |
3. What does a LEFT JOIN in MySQL return?
The LEFT JOIN operation retrieves every record from the left table along with any corresponding entries from the right table.
4. What will be the result when no matching rows are found in a LEFT JOIN in MySQL?
In scenarios where there are no corresponding rows in the right table, a LEFT JOIN will still provide all entries from the left table, while the columns associated with the right table will contain NULL values.