For Beginner

MySQL FULL JOIN

Here is a quick example on FULL JOIN in MySQL database.
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

Eg : queries to create and populate the Persons table.

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.
Happy coding!