SQL FULL OUTER JOINKeywords

FULL OUTER JOIN is a type of join in SQL that retains all records from both tables, even if there is no match in one of them.

The FULL OUTER JOIN result includes records that meet the condition in both tables (the intersection part) as well as records that do not meet the condition (the non-intersection part of the union). If a record exists in one table but has no match in the other table, the missing columns of that record will be filled with NULL.

The FULL OUTER JOIN keyword returns rows as long as 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 that need to be joined.
• ON table1.column_name=table2.column_name: Specifies the join condition, usually the common field of the two tables.
• column_name(s) : Select the required fields from the two tables.

Features

• Returns the union of the two tables: Contains all matched and unmatched records.
• Unmatched records are filledNULL: If a record has no match in one table, its corresponding fields are represented asNULL.
• Equivalence：FULL OUTER JOINContainsLEFT JOINandRIGHT JOINthe results of.

Suppose there are two tables:StudentsandCourses。

Students Table：

Courses Table：

Query Using FULL OUTER JOIN

SELECT Students.StudentID, Students.Name, Courses.CourseName
FROM Students
FULL OUTER JOIN Courses
ON Students.StudentID = Courses.StudentID;

Query Output Result：

Explanation：

• StudentID = 1and2Is the intersection part, with data in both tables.
• StudentID = 3Exists inStudentstable, but inCoursestable there is no match,CourseNamethe column isNULL。
• StudentID = 4Exists inCoursestable, but inStudentstable there is no match,Namethe column isNULL。

Summary

Demo Database

In this tutorial, we will use the EXAMPLE sample database.

Below is the data selected from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | Example      | http://www.example.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Below is the data from the "access_log" website access records 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.

MySQL does not support FULL OUTER JOIN; you can test the following example in SQL Server.

Note:The FULL OUTER JOIN keyword returns all rows from the left table (Websites) and the right table (access_log). If a row in the "Websites" table has no match in "access_log", or a row in the "access_log" table has no match in the "Websites" table, those rows will also be listed.