SQL FULL OUTER JOIN
The SQL FULL OUTER JOIN keyword returns all records from the left side table (tableA) and from the right side table (tableB).
The SQL FULL OUTER JOIN keyword combines the result of both LEFT JOIN and RIGHT JOIN query result.
Related Links
SQL FULL OUTER JOIN Syntax
SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
FULL OUTER JOIN table_name2
ON table1.column_name1 = table2.column_name1;
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
3 | SQL Server 2008 Transact-Sql | 90 | 2014 | Security | Nirmala |
4 | Sql Server Cookbook | 155 | 2012 | Security | Chandra |
6 | SQL Server Fundamentals | 145 | 2008 | Optimization | Dharan |
7 | Natural language Query To SQL | 115 | 2011 | Programming | Balan |
9 | Programming With MySQL | 75 | 2008 | Administration | Hanumanthan |
Sample Database Table - BookOrder
BookID | OrderDate | Qty | DeliveryDate |
---|---|---|---|
2 | 18-06-1999 | 12 | 28-06-1999 |
3 | 25-03-2011 | 13 | 27-04-2011 |
7 | 14-06-1999 | 8 | 20-06-1999 |
9 | 11-05-2009 | 19 | 24-05-2009 |
10 | 12-01-2006 | 10 | 22-02-2006 |
11 | 23-09-2000 | 17 | 26-09-2000 |
Note:- There must be a common columns while selecting columns from multiple tables. In the "Books" and "BookOrder" both tables contains a common column called "BookID".
SQL FULL OUTER JOIN - Example
Notice that the "BookID" column in the "Books" table refers to the "BookID" in the "BookOrder" table. The relationship between the two tables above is the "BookID" column.
Then, if we run the following SQL statement (that contains an RIGHT JOIN):
SELECT
BookOrder.BookID AS 'Book Order ID', BookOrder.DeliveryDate,
Books.BookID AS 'Books ID', Books.BookName, Books.BookPrice
FROM BookOrder
FULL JOIN Books
ON Books.BookID = BookOrder.BookID;
The result of above query is:
Book Order ID | DeliveryDate | Books ID | BookName | BookPrice |
---|---|---|---|---|
2 | 28-06-1999 | |||
3 | 27-04-2011 | 3 | SQL Server 2008 Transact-Sql | 90 |
7 | 20-06-1999 | 7 | Natural language Query To SQL | 115 |
9 | 24-05-2009 | 9 | Programming With MySQL | 75 |
10 | 22-02-2006 | |||
11 | 26-09-2000 | |||
4 | Sql Server Cookbook | 155 | ||
6 | SQL Server Fundamentals | 145 |
Note: If there are records in "Books" that do not have matches in "BookOrder", or if there are records in "BookOrder" that do not have matches in "Books", those records will be listed as well.
Related Links