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	Karmen	Kasa	Los Angels - USA	New Orleans
2	Jacob	Mary	Los Angels - USA	New Orleans
3	Maxwell	Glen	Australia	Sydney

The "Purchases" table:

PU_ID	OrderNo	P_Id
1	I-1567	3
2	I-6008	3
3	I-1110	1
4	I-5616	1
5	I-9009	15

Now we want to list all the persons and their Purchases, and all the Purchases with their persons.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Purchases.OrderNo
FROM Persons
FULL JOIN Purchases
ON Persons.P_Id=Purchases.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Karmen	Kasa	I-1110
Karmen	Kasa	I-5616
Maxwell	Glen	I-1567
Maxwell	Glen	I-6008
Jacob	Mary
		I-9009

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Purchases). If there are rows in "Persons" that do not have matches in "Purchases", or if there are rows in "Purchases" that do not have matches in "Persons", those rows will be listed as well.

« Previous Chapter

Next Chapter »

Web Development

Java Tutorials

C Tutorials

Browser Scripting

Server Scripting

XML Tutorials

SQL FULL JOIN Keyword