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.

You can also search these topics using sql full outer join, full join example, t sql full outer join, sql server full outer join, full outer join 3 tables, sql full outer join example, sql full outer join syntax, sql full outer join 3 tables.

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;

SQL FULL OUTER JOIN

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.

You can also search these topics using full outer join t sql, sql joins with examples, sql joins tutorial, sql joins explained, sql inner and outer joins with examples, explain sql joins with example, joins in sql server with examples, sql tutorial joins.