MySQL Outer Join

MySQL provides a lot of commands, which are needed while managing a database. For example, we often need to get some data from different tables based on some condition. MySQL then provides different types of joins to get the desired results. Let’s learn LEFT JOIN AND RIGHT JOIN of MySQL.

There is no such statement as FULL OUTER JOIN in SQL, but we can use a simple JOIN to get the same results or by simply using a SELECT statement over two different tables.

Otherwise, MySQL provides LEFT JOIN and RIGHT JOIN to get the records or rows from the right or left table, respectively. Let’s try a couple of different examples to get the desired results using appropriate joins.

Examples

Before we start learning the usage of LEFT and RIGHT JOIN. We will learn how to get all of the data from both of the tables (either common or uncommon) using the simple SELECT statement and using the CROSS JOIN with the SELECT statement. First, let’s try to get all of the data from both of the tables using the SELECT statement.

For instance, there are 2 tables that we got by the name of the author and books.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/01/echo/mysql-outer-join-01.png" data-lazy- height="331" src="data:image/svg xml,” width=”1124″>

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/01/echo/mysql-outer-join-02.png" data-lazy- height="330" src="data:image/svg xml,” width=”1119″>

If we want to get all the columns from both of the tables. The SELECT query will be used like this:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/01/echo/mysql-outer-join-03.png" data-lazy- height="408" src="data:image/svg xml,” width=”1518″>

As you can see, we have all the columns from both of the tables without even providing a condition.

If we use the JOIN or CROSS JOIN clause, both will bring us the same results. For example:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/01/echo/mysql-outer-join-04.png" data-lazy- height="447" src="data:image/svg xml,” width=”1501″>

Now, let’s try to apply the CROSS JOIN:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/01/echo/mysql-outer-join-05.png" data-lazy- height="445" src="data:image/svg xml,” width=”1510″>

As you can witness, all of these queries are bringing us the same results.

However, it is not good to have all the columns in such an abstract form. So, to get a few specific columns from the left or right table, there are two ways in which you could proceed; one way is that you use column names using SELECT statements or using joins that fit your requirement.

Alright, now let’s move forward to understand the LEFT JOIN and RIGHT JOIN.

LEFT JOIN

Suppose that we want to get some specific columns that are either from the books table or common between the books and authors table, based on some condition, the condition is actually provided by comparing two different tables. For example, we want to join two tables, books, and authors where the book’s ID is equal to the author’s ID. We can expect such a result by using LEFT Join with SELECT statement; SELECT query with the column names that you want to get from either the books table or authors. The SELECT query with the LEFT JOIN and condition would be like this:

Since we have mentioned the books table on the left side, the join will get the ID of one row from the books table and look for the same ID number in the author’s table. If it finds the same ID number, it will also show the given columns from the author’s table. Otherwise, it will show NULL in the columns of the author’s table. Let’s execute this query and witness the results.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/01/echo/mysql-outer-join-06.png" data-lazy- height="313" src="data:image/svg xml,” width=”1116″>

As you can see, we have the rows from both of the tables where the books table ID is equal to the author’s table ID. In the last row, we can also see that there is no ID number 4 in the author’s table, so it has returned NULL against it.

RIGHT JOIN

Similarly, if we want to get some data, either from the author’s table or common between the books and the author’s table, based on some conditions, this kind of results can be expected by using the RIGHT join and SELECT clause. The SELECT query with the RIGHT JOIN and condition would be like this:

This time, we know that the authors’ table is on the right side, so the join will get the ID of one row from the author’s table and look for the same ID number in the book’s table. If it finds the same ID number, it will show the given columns from the books table. Executing the query would result in this:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/01/echo/mysql-outer-join-07.png" data-lazy- height="265" src="data:image/svg xml,” width=”1540″>

As you can see, we have the rows from both of the tables where the author’s ID is equal to the book’s ID. We know that there was a fourth book in the books table, although we didn’t get it, this is because of the RIGHT JOIN.

So, this is how the LEFT JOIN and RIGHT JOIN truly works.

Conclusion

We have learned and understood the CROSS, LEFT, and RIGHT JOIN, as well as learned to use them to get the desired results in MySQL. We also have tried a couple of different examples of JOINS to understand the concepts in a better and profound way. So keep visiting our website linuxhint.com for more useful content like this.