SQL FULL OUTER JOIN
SQL FULL OUTER JOIN keyword
FULL OUTER JOIN keyword As long as there is a match in one of the left table (table1) and the right table (table2), it will be returned Row.
FULL OUTER JOIN keyword combines the results of LEFT JOIN and RIGHT JOIN.
SQL FULL OUTER JOIN syntax
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
Demo Database
In this tutorial, we will use the php sample database.
The following is the data selected from the "Websites" table:
| id | name | url --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | http://www.php.cn/ | 4689 | CN |
| 4 | Weibo | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+----------- ----------------+-------+---------+
The following is the data of the "access_log" website access record 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 following example in SQL Server.
Example
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 in the left table (Websites) and the right table (access_log). If there are rows in the "Websites" table that do not match in the "access_log" or rows in the "access_log" table that do not match in the "Websites" table, these are also listed.
