MySQL FULL JOIN
In the SQL language, the FULL JOIN (or FULL OUTER JOIN) command allows making a join between two tables. The use of this command allows combining the results of the two tables, associating them with each other using a condition, and filling with NULL values if the condition is not respected.

Syntax:
To return the rows of table1 and table2, it is necessary to use an SQL query with a syntax like this one:
SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.fk_id
This query can also be written in this way:
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.fk_id
The condition shown here is to link the tables on an id, but the condition can be defined on other fields.
Example :
Let’s take the following example of a database that contains a “Users” table and “Orders” table that contains all the sales.
Users table:
> SELECT * FROM Users; +------------+-----------+--------+-------------------------------+ | UserID | Name | age | Address | +------------+-----------+--------+-------------------------------+ | 1 | Alex | 25 | 819 Saint Francis Way | | 2 | Emily | 15 | 171 Jarvisville Road Michigan | | 3 | Jean | 35 | 188 Clay Street Indiana | | 4 | Bob | 40 | 285 Java Lane Missouri | +------------+-----------+--------+-------------------------------+
Orders table:
> SELECT * FROM Orders; +---------+-----------+----------+-------------+ | OrderID | NumOrder | Total | UserID | +---------+-----------+----------+-------------+ | 1 | 9901 | 254.00 | 1 | | 2 | 9902 | 300.96 | 1 | | 3 | 9903 | 145.03 | 4 | | 4 | 9904 | 744.33 | 5 | +---------+-----------+----------+-------------+
It is possible to use FULL JOIN to list all users who have or have not made an order, and to list all orders that are associated or not with a user. The SQL query is as follows:
SELECT UserID, Name, NumOrder, Total FROM Users FULL JOIN Orders ON Users.UserID = Orders.UserID
Output:
+---------+-----------+----------+-------------+ | UserID | Name | NumOrder | Total | +---------+-----------+----------+-------------+ | 1 | Alex | 9901 | 254.00 | | 1 | Alex | 9902 | 300.96 | | 2 | Emily | NULL | NULL | | 3 | Jean | NULL | NULL | | 4 | Bob | 9903 | 145.03 | | NULL | NULL | 9904 | 744.33 | +---------+-----------+----------+-------------+
This result shows users number 2 and 3 who have not made any purchases. The result also returns the order 9904 which is associated with a user that does not exist (or no longer exists). In cases where there is no match with the other table, the column values are NULL.