WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL FULL OUTER JOIN Keyword


SQL FULL OUTER JOIN keyword

FULL OUTER JOIN keyword returns a row whenever there is a match in one of the left table (table1) and right table (table2).

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;

SQL FULL OUTER JOIN



Demo database

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

Here is the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.com/  | 1     | USA     |
| 2  | Indiatimes          | https://www.Indiatimes.com/ | 13    | IN      |
| 3  | zomato      | http://www.zomato.com/  | 4689  | IN      |
| 4  | indiamart          | http://indiamart.com/       | 20    | IN      |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/|   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Here is the data for the "access_log" site visit log:

+-----+---------+-------+------------+
| 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 instance

The following SQL statement selects all website visits.

FULL OUTER JOIN is not supported in MySQL. You can test the following examples 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 in the left table (Websites) and the right table (access_log). If the rows in the "Websites" table do not match in the "access_log" or the rows in the "access_log" table do not match in the "Websites" table, those rows are also listed.