FULL JOIN concept in MySQL is to return rows when there is a match in one of the tables. Further FULL JOIN or FULL OUTER JOIN is UNION of LEFT JOIN and the RIGHT JOIN of the both tables.
Consider that you have following two tables.
Table 1 : Persons
CREATE TABLE Persons (P_Id int, Name varchar(1)); INSERT INTO Persons (P_Id, Name) VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E');
Table 2 : Invoice
Eg : queries to create and populate Invoice table.
CREATE TABLE Invoice (Id int, P_Id int); INSERT INTO Invoice (Id, P_Id) VALUES (111, 3), (112, 3), (113, 1), (114, 1), (115, 15);
Now think that we want to join rows of both these tables on the condition of same P_Id. To perform that we need to get the UNION of RIGHT JOIN and the LEFT JOIN of both tables.
Here is the query to FULL JOIN operation.
SELECT Persons.Name, Persons.P_Id, Invoice.Id FROM Persons LEFT JOIN Invoice ON Persons.P_Id=Invoice.P_Id UNION SELECT Persons.Name, Persons.P_Id, Invoice.Id FROM Persons RIGHT JOIN Invoice ON Persons.P_Id=Invoice.P_Id
Resulting table :
I think that you can understand what happened here.
1. First result of LEFT OUTER JOIN of both tables.
2. Then result of RIGHT OUTER JOIN of both tables.
3. UNION of these two tables means rows when there is a match in one of the tables or FULL JOIN of these two tables.
If you understand the concept try to use following query to FULL JOIN these tables. Same concept and same result.
SELECT Persons.Name, Persons.P_Id, Invoice.Id FROM Persons LEFT JOIN Invoice ON Persons.P_Id=Invoice.P_Id UNION SELECT Persons.Name, Persons.P_Id, Invoice.Id FROM Invoice LEFT JOIN Persons ON Persons.P_Id=Invoice.P_Id
Thank you for watching. Always your ideas are welcome.