When it comes to analyzing and managing huge datasets in databases, it becomes really difficult for the human mind to handle. Especially when it comes to comparing data from two different tables (having multiple columns); users have to move back and forth between those tables. To solve this problem, MySQL provides the concept of Joins, which provides a vital role in showing data fields from two tables simultaneously in a single table.

This article explains the following guidelines in depth to help in understanding the concept of JOINs in MySQL:

• What is a JOIN in MySQL?
• Types of Joins in MySQL
• How to Use Joins in MySQL?

Let’s dive into the details of the JOIN clause in MySQL from a very basic to a detailed understanding.

What is a JOIN in MySQL?

The “JOIN” is a clause used in MySQL to combine two tables based on the condition given to it. The JOINs collect data from two tables and show the result in a newly created table according to a condition. In MySQL, there are multiple types of Joins for fulfilling their respective purposes based on the needs of the user.

Let’s jump into the types of Joins in MySQL and their understanding along with their examples.

How Many Types of Joins are in MySQL?

There can be 4 types of needs that can occur while joining two or more tables:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN

However, before jumping into the details of these joins; let’s have a look and understand the basic and simple join between two tables in MySQL.

How to Use Joins in MySQL?

As the name depicts, the JOIN clause of MySQL is used to join two or more tables under a particular condition. To specify the condition, “ON”, “USING” or “WHERE” clauses can be utilized. Without practical examples of joins it will be difficult to understand the usage of joins in MySQL.

Suppose we have two tables:

TABLE 1: “lh_data” Table

Having 12 rows in it.

TABLE 2: “lh_customers” Table

Having 7 rows in it.

Let’s head straight toward the basic examples of simple JOIN.

Example 1: Basic Usage of JOIN in MySQL
This simple example will join two tables (“lh_data” and “lh_customers”), where the “id” of the “lh_data” table is equal to the “customer_id” of the “lh_customers” table:

Only 4 rows have identical IDs in both (“lh_data” and “lh_customers”) tables.

Example 2: How Does INNER JOIN Work in MySQL?

This example will show the same result as the above-given example, but using the INNER JOIN and ON clause.

The INNER JOIN selects all the rows having similar “ID” in both tables.

Only 4 rows have common IDs in both tables.

Example 3: How to Use CROSS JOIN in MYSQL?

This simple example will join all the rows of the two tables (“lh_data” and “lh_customers”), without any condition:

Example: How to Use LEFT JOIN in MySQL?

The LEFT JOIN will show all the data from the left table and similar rows from the right table.

The output shows that all values from the left table and the corresponding values from the right table that match the specified condition have been retrieved. However, values that are present in the left table but not in the right table, are considered as “NULL” values in the output.

Example: How Does the RIGHT JOIN Work in MYSQL?

The RIGHT JOIN will show all the records from the right/second table and similar records from the left/first table.

The RIGHT JOIN is applied as per the condition.

Conclusion

The JOINs in MySQL are utilized to combine/join data from two or more tables based on a condition. The tables can be combined using four different types of JOINs, INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN. INNER JOIN and JOIN are similar and are typically used with a specific condition, while the LEFT JOIN and RIGHT JOIN are used to retrieve data from one table along with matching data from another table.