FULL OUTER JOIN keyword returns all records when there is a match in left (table 1) or right (table 2) table records.
NOTE
FULL OUTER JOIN and FULL JOIN are the same
FULL OUTER JOIN can be return very large result-sets. so it would be a good idea if you add limit or where clause or so
Here is the sample of Engineer table
| id | fullname | title | department | manager |
|---|---|---|---|---|
| 1 | Eric Brown | Software Engineer | IT | Frank Martin |
| 2 | Brandon Hart | DevOps Engineer | IT | Justin Martin |
| 3 | Connor Langdon | Software Engineer | IT | Lucas Scott |
Here is the sample of Certificate table
| id | engineerid | certificatename | certificatedate |
|---|---|---|---|
| 1 | 1 | Docker Certified Associate | 2020-05-05 |
| 2 | 2 | Certified Kubernetes Administrator | 2021-07-03 |
| 3 | 2 | Certified Jenkins Engineer | 2022-01-03 |
Examples / Outputs
1SELECT e.id, e.fullname "Full Name",
2c.certificatename "Certificate Name"
3FROM engineer AS e
4FULL OUTER JOIN certificate as c
5ON e.Id=c.engineerid;
| id | Full Name | Certificate Name |
|---|---|---|
| 1 | Eric Brown | Docker Certified Associate |
| 2 | Brandon Hart | Certified Kubernetes Administrator |
| 2 | Brandon Hart | Certified Jenkins Engineer |
| 3 | Connor Langdon |