Full Outer Join in SQL
? FULL OUTER JOIN in SQL
FULL OUTER JOIN returns all records when there is a match in either the left table or the right table.
It combines the results of both LEFT JOIN and RIGHT JOIN.
? Syntax:
SELECT columnsFROM table1FULL OUTER JOIN table2ON table1.common_column = table2.common_column;? How it works:
| Table1 Value | Table2 Value | Result |
|---|---|---|
| Matches | Matches | Row with combined columns |
| Matches | No match | Row from table1, NULLs in table2 columns |
| No match | Matches | Row from table2, NULLs in table1 columns |
? Example:
Table: Students
| StudentID | Name |
|---|---|
| 1 | John |
| 2 | Sarah |
| 3 | Mike |
Table: Exams
| ExamID | StudentID | Score |
|---|---|---|
| 101 | 2 | 90 |
| 102 | 4 | 85 |
Query:
SELECT s.StudentID, s.Name, e.ScoreFROM Students sFULL OUTER JOIN Exams e ON s.StudentID = e.StudentID;Result:
| StudentID | Name | Score |
|---|---|---|
| 1 | John | NULL |
| 2 | Sarah | 90 |
| 3 | Mike | NULL |
| NULL | NULL | 85 |
? Notes:
Supported in SQL Server, PostgreSQL, Oracle, etc.
MySQL does NOT support
FULL OUTER JOINdirectly. You can simulate it using aUNIONofLEFT JOINandRIGHT JOIN.
? Simulate FULL OUTER JOIN in MySQL:
SELECT s.StudentID, s.Name, e.ScoreFROM Students sLEFT JOIN Exams e ON s.StudentID = e.StudentIDUNIONSELECT s.StudentID, s.Name, e.ScoreFROM Students sRIGHT JOIN Exams e ON s.StudentID = e.StudentID;If you'd like, I can help you write or optimize your FULL OUTER JOIN queries!
