Joins in MySQL

MySQL Supports the Following Types of Joins

Following are the different types of Joins in MySQL.

Inner Join

Inner Join in MySQL is defined as the type of join that is used to get results by joining two tables having one or more than the same column between them. To explain, an inner join query looks for all pairs of rows that satisfy the join predicate by comparing each row of the first table with each row of the second table. It retrieves only the rows that have matching values in both tables, excluding any rows that do not have a match in either of the tables.

In Inner join, the foreign key of one table is used with the related column of another table to get combined data as a result of the query. Inner join is a default join in MySQL. If we simply write JOIN in the query, then it will automatically take as the inner join for performing the query. Inner join is the most frequently used type of join clause in MySQL. The syntax of the Inner Join in MySQL is as follows:

In the syntax, first, we have joined the two tables using the INNER JOIN query. Here we are using the SELECT query to get some specific data from the joined tables. If you want to get from all the columns of the table, you can use the asterisk (*).

Left Join

Left join in MySQL is used to get results from multiple tables. Left join in MySQL is similar to INNER JOIN. It is also used with the SELECT clause and after the FROM keyword.

LEFT JOIN in MySQL is used to retrieve data from all columns of the left table, along with any matching rows from the right table based on the specified join condition. Non-matching rows from the right table are also included in the result, but with NULL values for the columns of the right table. LEFT JOIN in MySQL is also known as a left outer join. Therefore, the optional keyword for Left Join is Outer. It will work in the same way in both cases.

Left joins return all records from the left-hand table and just the matching records from the right-hand table, as seen in the accompanying visual representation.

The syntax of LEFT JOIN in MySQL is as follows:

In the above syntax, two tables are the right table and left table namely table1 and table2 respectively. Here we are using the SELECT query to get some specific data from the joined tables based on the join condition. If you want to get from all the columns of the table, you can use the asterisk (*). Then we used to FROM keyword to instruct the query on how it would work.

Right Join

Right join in MySQL is used to get results from multiple tables. Right join in MySQL is similar to INNER JOIN. It is also used with the SELECT clause and after the FROM keyword. Right JOIN in MySQL is used to retrieve data from all columns of the right table, along with any matching rows from the left table based on the specified join condition. If there are no matching records from the left table, the right join returns NULL. Right JOIN in MySQL is also known as a right outer join. Therefore, the optional keyword for Right Join is Outer. It will work in the same way in both cases.

Right joins return all records from the right-hand table and just the matching records from the left-hand table, as seen in the accompanying visual representation.

The syntax of RIGHT JOIN in MySQL is as follows:

In the above syntax, two tables are the right table and left table namely table1 and table2 respectively. Here we are using the SELECT query to get some specific data from the joined tables based on the join condition. If you want to get from all the columns of the table, you can use the asterisk (*). Then we used to FROM keyword to instruct the query on how it would work.

Cross Join

Cross joins in MySQL are defined as the type of JOIN clause that is used to return the data from all possibilities from two or more two tables by combining all the rows that are contributing to the provided tables. Cross-join in MySQL is also known as CARTESIAN JOIN in MySQL. All the rows in the first table multiplied by all the rows in the second table equals the Cartesian product. The join condition is not possible with this clause, comparable to the Inner Join.

The following visual illustration demonstrates how CROSS JOIN in MySQL works. CROSS JOIN returns all the data from tables 1 and 2, and each row is made up of data from both tables.

The syntax of CROSS JOIN in MySQL is as follows:

In the above syntax, two tables are the right table and left table namely table1 and table2 respectively. Here we are using the SELECT query to get some specific data from the joined tables. If you want to get from all the columns of the table, you can use the asterisk (*). Then the keyword CROSS JOIN tells that the table2 just after this clause needs to be joined together by the property of CROSS JOIN in MySQL.

Setting Up Sample Tables

Now you will see all four types of Joins in MySQL using the examples. All the examples are explained below with output and explanation for better understanding. Before moving ahead to the examples, first, let us create a sample table and add some data to the rows and columns of the table. This table will be used further to perform JOIN operations on it. Here we are creating two tables. The name of the first table is member and the name of the second table is committee. To create the table and define the schema of the table, we will execute the query given below.

Then we need to insert some rows into both the tables that are member and committee. To insert the data, we will execute the query given below:

Now, the data are inserted into the rows of the respective table. Now, we will run the given query to show whether the data are inserted into the table or not. To check this we will use the command given below:

Output:

Output:

As we can see the names of some members are on the committee and some of the names are not on the committee. Because they are not mentioned as committee members in the query above.

MySQL INNER JOIN Clause

The inner join clause looks for all pairs of rows that satisfy the join predicate by comparing each row of the first table with each row of the second table. It joins the two mentioned tables member and committee and then gives all the rows that satisfy the given conditions. Column values for each matched pair of rows from both tables are combined into a result row when the join-predicate is satisfied.

The syntax to join two tables by INNER JOIN using the ON clause is as follows:

The syntax to join two tables by INNER JOIN by the use of the USING clause is as follows:

Now, let us see if we want to see the names of members who are also committee members, we will use the query given below using the INNER JOIN clause.

Output:

Since there are some same columns in both tables, you can also use the INNER JOIN using the USING clause to get the result. To perform this, we will execute the code given below:

Output:

MySQL LEFT JOIN Clause

When we execute a query of LEFT JOIN in MySQL, it starts to match the rows and columns for the right table. For every row in the left table, it compares it with the columns in the right table and returns the matched rows. If there is not any matching data from the right table, then the left join in MySQL returns NULL. In other words, the Left Join clause either returns NULL if there were no matching records discovered, or it returns all the rows from the left table and any matching records from the right table.

You can also say that, whether or not there are matching rows in the right table, the left join retrieves all data from the left table. The left join utilizes NULLs for the columns of the row from the right table in the result set if no matching rows from the right table were found.

Given below is an example that shows how we can use the LEFT JOIN in MySQL to join the tables member and committee. Execute the query given below:

Output:

If you want to see the names of members who are not committee members, use the WHERE clause and IS NULL operator. You need to execute the query given below:

Output:

MySQL RIGHT JOIN Clause

Right JOIN in MySQL is used to get the data from all the columns of a right table and non-common columns from the left table. When we execute a query of LEFT JOIN in MySQL, it starts to match the rows and columns for the left table. For every row in the left table, it compares it with the columns in the right table and returns the matched rows. If there is not any matching data from the left table, then the left join in MySQL returns NULL. In other words, the Right Join clause either returns Null if there were no matching records discovered, or it returns all the rows from the right table and any matching records from the left table. RIGHT JOIN in MySQL also supports the USING clause.

Given below is an example that shows how we can use the RIGHT JOIN in MySQL to join the tables member and committee. Execute the query given below:

Output:

Here, we are using the same example of RIGHT JOIN in MySQL with the USING clause. To get the result using the USING syntax, execute the query given below:

Output:

Now, if you want to see the names of committee members that are not in the member table, execute the command given below where we have used the IS NULL clause.

Output:

MySQL CROSS JOIN Clause

CROSS Joins simply extracts the results from all the rows of both tables. For example, if there are n number of rows in a table and m number of rows in the second table. Then CROSS JOIN in MySQL will make the output by combining all these rows of both the tables (n multiplied by m). Cross-join is useful when we want to get combined data from two different data sheets that are somehow related to each other. Given below is the query to get the combined result of two tables namely member and committee.

Output: