Latest web development tutorials

SQL FULL OUTER JOIN keywords

SQL FULL OUTER JOIN keywords

FULL OUTER JOIN keywords as long as the left table (table1) and the right table (table2) one of the tables there is a match, it returns the row.

FULL OUTER JOIN keywords combined with LEFT JOIN and RIGHT JOIN results.

SQL FULL OUTER JOIN syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name ;

SQL FULL OUTER JOIN

The demo database

In this tutorial, we will use w3big sample database.

The following is a selected "Websites" table data: 

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

Here is the data "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 examples

The following SQL statement selects all sites access records.

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

Examples

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: FULL OUTER JOIN keyword returns the left (Websites) and the right table (access_log) in all rows. If no match "Websites" table row "access_log" or "access_log" table row "Websites" table does not match, also listed these lines.