YouTip

FULL OUTER JOIN is a type of join in SQL used to retain all records from both tables, even when there is no match in one of the tables. The result of a FULL OUTER JOIN includes records satisfying the join condition (the intersection part) as well as records not satisfying the condition (the non-intersection part of the union). If a record exists in one table but has no matching record in the other table, the missing columns for that record are filled with NULL. The FULL OUTER JOIN keyword returns rows if there is a match in either the left table (table1) or the right table (table2). The FULL OUTER JOIN keyword combines the results of LEFT JOIN and RIGHT JOIN. ### SQL FULL OUTER JOIN Syntax SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name; * **table1, table2**: The two tables to be joined. * **ON table1.column_name=table2.column_name**: Specifies the join condition, usually a common field between the two tables. * **column_name(s)**: The fields selected from both tables. ![Image 1: SQL FULL OUTER JOIN]( ### Characteristics * **Returns the union of both tables**: Includes all matched and unmatched records. * **Unmatched records are filled with `NULL`**: If a record has no match in one table, its corresponding fields are represented as `NULL`. * **Symmetry**: `FULL OUTER JOIN` includes the results of both `LEFT JOIN` and `RIGHT JOIN`. Assume we have two tables: `Students` and `Courses`. **Students table**: | StudentID | Name | | --- | --- | | 1 | Alice | | 2 | Bob | | 3 | Charlie | **Courses table**: | CourseID | StudentID | CourseName | | --- | --- | --- | | 101 | 1 | Math | | 102 | 2 | Science | | 103 | 4 | History | * * * #### Using FULL OUTER JOIN for Querying SELECT Students.StudentID, Students.Name, Courses.CourseName FROM Students FULL OUTER JOIN Courses ON Students.StudentID = Courses.StudentID; **Query output result**: | StudentID | Name | CourseName | | --- | --- | --- | | 1 | Alice | Math | | 2 | Bob | Science | | 3 | Charlie | NULL | | 4 | NULL | History | **Explanation**: * `StudentID = 1` and `2` constitute the intersection part, where data exists in both tables. * `StudentID = 3` exists in the `Students` table but has no match in the `Courses` table; thus, the `CourseName` column is `NULL`. * `StudentID = 4` exists in the `Courses` table but has no match in the `Students` table; thus, the `Name` column is `NULL`. ### Summary | JOIN Type | Returned Content | | --- | --- | | `INNER JOIN` | Intersection of both tables — only matching records | | `LEFT JOIN` | All records from the left table, plus matching records from the right table | | `RIGHT JOIN` | All records from the right table, plus matching records from the left table | | `FULL OUTER JOIN` | Union of both tables — includes both matching and unmatched records | * * * ## Sample Database In this tutorial, we will use the sample database. Below is data selected from the "Websites" table: +----+--------------+---------------------------+-------+---------+| id | name | url | alexa | country |+----+--------------+---------------------------+-------+---------+| 1 | Google | https://www.google.cm/ | 1 | USA || 2 | Taobao | https://www.taobao.com/ | 13 | CN || 3 | | | 4689 | CN || 4 | Weibo | http://weibo.com/ | 20 | CN || 5 | Facebook | https://www.facebook.com/ | 3 | USA || 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |+----+---------------+---------------------------+-------+---------+ Below is data from the "access_log" website access log table: +-----+---------+-------+------------+| aid | site_id | count | date |+-----+---------+-------+------------+| 1 | 1 | 45 | 2016-05-10 || 2 | 3 | 100 | 2016-05-13 || 3 | 1 | 230 | 2016-05-14 || 4 | 2 | 10 | 2016-05-14 || 5 | 5 | 205 | 2016-05-14 || 6 | 4 | 13 | 2016-05-15 || 7 | 3 | 220 | 2016-05-15 || 8 | 5 | 545 | 2016-05-16 || 9 | 3 | 201 | 2016-05-17 |+-----+---------+-------+------------+9 rows in set (0.00 sec) * * * ## SQL FULL OUTER JOIN Example The following SQL statement selects all website access records. FULL OUTER JOIN is not supported in MySQL; you can test the example below in SQL Server. ## Example SELECT Websites.name, access_log.count, access_log.date FROM Websites FULL OUTER JOIN access_log ON Websites.id=access_log.site_id ORDER BY access_log.count DESC; **Note:** The FULL OUTER JOIN keyword returns all rows from both the left table (Websites) and the right table (access_log). Rows from the "Websites" table that have no match in "access_log", or rows from the "access_log" table that have no match in "Websites", are also included in the result.