SQL FULL JOIN Keyword

SQL FULL JOIN Keyword

The FULL JOIN keyword will return rows as long as there is a match in one of the tables.

FULL JOIN keyword syntax

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Note:In some databases, FULL JOIN is called FULL OUTER JOIN.

Original Table (used in the example):

"Persons" Table:

Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

"Orders" Table:

Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65

Full Join (FULL JOIN) Example

Now, we want to list all people, as well as their orders, and all orders, as well as the people who ordered them.

You can use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

Result Set:

LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
Bush George 　
　 　 34764

The FULL JOIN keyword will return all rows from the left table (Persons) and the right table (Orders). If there is no match for the row in the table 'Orders' in 'Persons', or if there is no match for the row in 'Orders' in 'Persons', these rows will also be listed.