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 reverse function

postgresql datediff in minutes

sql not exists

right and left outer join in sql

where and having in sql

sql any

sql insert data into table

sql server get day name

mysql distinct

sql server ceiling

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.

Related Links

sql delete statement

sql length

drop column sql

create table in sql with primary key

wildcard operator in sql

charindex in mysql

create a table sql

sum two columns in sql

logical operators in sql

sql get day from date

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.