SQL Full JOIN keyword

SQL Full JOIN keyword

The full JOIN keyword returns a row whenever there is a match in one of the tables.

Full JOIN keyword syntax

SELECT column_name (s) from Table_name1full JOIN table_name2 on Table_name1.column_name=table_name2.column_name

Note: In some databases, full join is called full OUTER join.

The original table (used in the example):

"Persons" table:

City

id_p	LastName	FirstName	Address
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

Fully connected (full join) instances

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

You can use the following SELECT statement:

SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsfull JOIN Orderson persons.id_p=orders.id_ Porder 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 returns all rows from the left table (Persons) and the right table (Orders). If the rows in "Persons" do not match in the table "orders", or if the rows in "orders" do not have a match in the table "Persons", the rows are also listed.

SQL Full JOIN keyword