MySql Right & Full Join

In this article, I am going to discuss the MySql Right & Full Join query. Previously I have completed MySql Inner Join & Left Join. Today I am gonna complete Right Join and Full Join. So let’s get started.

In part-1 I have shared sample database structure for practicing MySql Join query. If you are not read it yet then please read part-1 first before reading this article part-2. I am talking about 3 tables in the previous part. Those are “customer”, “customer_order” & “order_status”. You can make a join query in two or more tables.

Right Join (Outer)

I am talking about 3 tables in the previous part. Those are “customer”, “customer_order” & “order_status”. MySql RIGHT JOIN keyword returns all records from the right table (customer_order), even if there are no matches in the left table (customer). Moreover, You can make a join query in two or more tables. MySql RIGHT JOIN for two tables, the structure is:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON
ON table1.column_name = table2.column_name;

In terms of our sample database the SQL Statement is:

As a result of the RIGHT JOIN resultset, there are a total of 12 rows of order details with the customer. Previously I have said that there are 12 rows records in the customer_order table. Therefore, as a condition of MySql RIGHT JOIN, it returns all the records of the Right table (customer_order) even there are no matches in the left table (customer). The result is NULL from the left side when there is no match. For sample database, you can change then customer_id to anything that does not exist in customer table then you will see the resultset is Left table (customer) is showing NULL for that particular record. RIGHT JOIN is the reverse of the LEFT JOIN. You can read this for more details about LEFT JOIN with a practical example.

FULL Join (Outer)

Mysql RIGHT & FULL JOIN has some difference. The FULL JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records. It can potentially returns a very large number of resultset. Therefore this keyword is take much time then other join’s. So see the FULL JOIN structure:

SELECT column_name(s)
FROM table1
FULL JOIN table2 ON
ON table1.column_name = table2.column_name;

FULL JOIN and FULL OUTER JOIN both are the same keywords. You can use as like this.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON
ON table1.column_name = table2.column_name;

In terms of our sample database the SQL Statement is:

FUll JOIN Warning MySql

You will get a warning message error code #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘OUTER JOIN customer_order ON Customers.id=customer_order.id LIMIT 0, 25’ at line 3.

Right?

Solution FULL JOIN

Coz There is no OUTER JOIN Keyword in MySQL. Actually MySql does not support OUTER JOIN or FULL OUTER JOIN syntax. See OUTER JOIN simplification. You have to use a combination of LEFT and RIGHT JOIN to obtain full joins. Finally, the structure is:

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON
ON table1.column_name = table2.column_name;
UNION
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON
ON table1.column_name = table2.column_name;

In the above query works for special cases where a FULL OUTER JOIN or FULL JOIN operation would not return any duplicate rows. When we use UNION, it will not return you any duplicate value and UNION ALL is for selected repeated values from the column

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON
ON table1.column_name = table2.column_name;
UNION ALL
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON
ON table1.column_name = table2.column_name;

Here you can see that all the results are returned. Getting rows from left table customers and getting a result from right table customer_order. Here you can see that there is no order information is available of Jon paul and Abidur Jon customers. The order information is NULL. Above all, One method to simulate a full join is to take the union of two outer joins.

In our next and final part, I will cover the final thing. Union, Cross & Self Join. Till then happy learning.

Thanks