Python MySQL Join Operation

In SQL, the JOIN statement allows us to combine the columns from two or more tables by using a common column among them. There are three types of joins in SQL Each type provides distinct results based on how data is stored.

MySQL Join Operation Example

Let's suppose we have two tables, the "User" table and the "Vehicle" table:

Table "User"

Table "Vehicle"

We will create these two tables in Python and insert the values in them.

Output:

It would create the User and Vehicle table.

These two tables can be joint by using the User's fav field and the Vehicles' id field.

The MySQL JOIN operation allows users to query data from different tables and return the output as a single set. SQL provides different types of joins, each of which yields different outputs based on how the data is combined.

Let's understand each one of the Joins in detail.

Types of Joins

There are three types of join operations in Python MySQL, let's see them one by one with examples.

1. INNER JOIN

It shows records that have matching values in both tables. If it doesn't find any match in the table, it returns no record.

Syntax of Inner Join

It has the following syntax:

Inner Join Example

In the given example, we are using the INNER JOIN to fetch the user names that are present in both the User table and the Vehicle table. We will write an INNER JOIN query that will return an output where the fav matches the id in both the tables.

Output:

('Reema', 'Royal Enfield')
('Ayon', 'Royal Enfield')
('Anshul', 'Honda City')

Explanation:

In the above program, we have connected Python to the MySQL database using the mysql.connector library. Established a connection and created a cursor object.

We have written a SQL Query where we have used the INNER JOIN to include all the common values from two tables, users and products. The statement users.fav = products.id will fetch values that have an exact match on both the tables. We will execute this query and retrieve all rows and store them in the myresult variable.

At last, we will print each record by using the For loop and will return the record present in both tables.

2. LEFT JOIN

In the above output, Megha and Heena were excluded, which is because INNER JOIN only returns records where there is a match, meaning it retrieves the common values from two tables.

If you want to show all values from the user table, even if they do not have a favourite product, use the LEFT JOIN statement.

Left Join Syntax

It has the following syntax:

Left Join Example

In this example, we are using the LEFT JOIN to retrieve the names of users from the User table along with their matching records from the Vehicle table. This LEFT JOIN query will return all the values from the User table and the matched records from the Vehicle table, and if there is no match in the Vehicle table, NONE will be returned for Vehicle values.

Output:

('Reema', 'Royal Enfield')
('Ayon', 'Royal Enfield')
('Anshul', 'Honda City')
('Heena', None)
('Megha', None)

Explanation:

In this, we have written a SQL query where we have selected data from two tables, users and products. Then we have used a LEFT JOIN where we have included all users, even if they don't have an exact matching value in the favorite product.

The join is made using the statement users.fav = products.id. After implementing this query, it will return all the values from the left table and the common values from the right table.

3. Right Join

Right join shows all the columns of the right-hand side table, as we have two tables in the database. If it doesn't find any match in the table, it returns none on the left side.

Right Join Syntax

It has the following syntax:

Execute the following query on the MySQL server.

Right Join Example

In this example, we are using the RIGHT JOIN to retrieve the names of vehicles from the Vehicle table along with their matching records from the User table. This query returns all records from the Vehicle table and the matched records from the User table, and if there is no match, as a result, None will be returned for User values.

Output:

('Ayon', 'Royal Enfield')
('Reema', 'Royal Enfield')
('Anshul', 'Honda City')
(None, 'Tesla Model 3')

Explanation:

The SQL query uses RIGHT JOIN, meaning all rows from the Vehicle table will be returned, along with matching rows from the User table. If no match is found, None will appear for the User columns. The join condition User.fav = Vehicle.id links users' favorite vehicle IDs with vehicle IDs in the Vehicle table.

Conclusion

The Python MySQL JOIN is an important operation that is used to combine the columns from two or more tables by using a common column among them. By using joins such as INNER JOIN, LEFT JOIN, and RIGHT JOIN, we can fetch different sets of outputs. For instance, if we want only matching records, all records from one table, or all records from another, it is possible using JOIN.