MySQL - Full Join
The FULL JOIN keyword return rows when there is a match in one of the tables.
MySQL FULL JOIN Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
The "Persons" table:
| P_Id | FirstName | Last Name | Address | City |
|---|
| 1 | Putu | Sanjaya | Jl. Imam Bonjol 30 | Denpasar |
| 2 | Made | Sanjaya | Jl. Sudirman 08 | Denpasar |
| 3 | Haru | Sanjaya | Jl. Gadjah Mada 10 | Denpasar |
The "Orders" table:
| O_Id | OrderNo | P_Id |
|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:
| FirstName | LastName | OrderNo |
|---|
| Putu | Sanjaya | 22456 |
| Putu | Sanjaya | 24562 |
| Made | Sanjaya | 77895 |
| Made | Sanjaya | 44678 |
| Haru | Sanjaya | 34764 |
The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.
