SQL FULL JOIN Keyword
SQL FULL JOIN Keyword
The
FULL JOIN keyword return rows when there is a match in one of
the tables.
SQL FULL JOIN Syntax
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
|
SQL FULL JOIN Example
The "Persons" table:
P_Id |
LastName |
FirstName |
Address |
City |
1 |
Hansen |
Ola |
Timoteivn 10 |
Sandnes |
2 |
Svendson |
Tove |
Borgvn 23 |
Sandnes |
3 |
Pettersen |
Kari |
Storgt 20 |
Stavanger |
The "Orders" table:
O_Id |
OrderNo |
P_Id |
1 |
77895 |
3 |
2 |
44678 |
3 |
3 |
22456 |
1 |
4 |
24562 |
1 |
5 |
34764 |
15 |
Now we want to list all the persons and their orders, and all the orders with
their persons.
We use
the following SELECT
statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName |
The result-set will look like this:
LastName |
FirstName |
OrderNo |
Hansen |
Ola |
22456 |
Hansen |
Ola |
24562 |
Pettersen |
Kari |
77895 |
Pettersen |
Kari |
44678 |
Svendson |
Tove |
|
|
|
34764 |
The FULL JOIN keyword returns all the rows from the left table (Persons), and
all the rows from the right table (Orders). If there are rows in
"Persons" that do not have matches in "Orders", or if there
are rows in "Orders" that do not have matches in "Persons", those rows will be
listed as well.
|