In this post, we will learn How can I do a FULL OUTER JOIN in MySQL where joins are being used for filtering the data or making some restrictions over the data which helps in restricting the data during the display of the data from two different tables in case of Relational database management System( RDBMS).
Here we need to understand that there are different kinds oj joins that we used for restricting or filtering the data as per our choice where we simply use set theory for deciding which data we need to display and which we can bypass as per our choices. Here we will discuss different ways to do so and different kinds of joins.
Types Of Joins
There are different kinds of joins that we encounter in our SQL learning process which are as followed. Here we have taken an example where we have six elements and when we apply differently joins a different number of records as result will be displayed, Here we have given how many elements would be displayed under different joins.
- INNER JOINS – 3
- FULL JOINS – 6
- LEFT JOINS – 4
- RIGHT JOINS – 1
As here we can see full join is nothing but all the elements of both tables or in other words, simply all the elements of both tables will be displayed with a small change.
And that small change in the result would be like it will ignoring the elements which are common and the common elements would be displayed just once. To understand it in a better way we have given an example where we have applied full join for two different tables to get the data.
In general, we achieve full outer join using left join and right join as in case of MySQL does not support the full outer join so here we have given an example of code where we have used right join and left join for achieving full outer join.
SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1. id = table2. id UNION ALL SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1. id = table2. id WHERE table1. id IS NULL;
Here we have taken table1 and first table and table2 for the second to perform full outer join and as mentioned using right outer join and left outer join we achieved full outer join which we can also prove using set theory in case you want to know how does it get the result as full outer join.
To learn more about How can I do a FULL OUTER JOIN in MySQL You may visit: by stack over flow MySQL.
To learn more about MySQL tutorials on how to get the data from databases in different forms using different functions, Clauses, and commands and get the concepts clear you may visit: Which is MySQL Data Type To Use For Storing Boolean Values?.