Snippet Name: FULL JOIN example and syntax
Description: The FULL JOIN keyword return rows when there is a match in one of the tables.
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.
Also see:
» ANSI Joins: FULL JOIN
» ANSI Joins: OUTER JOIN
» ANSI Joins: CROSS JOIN
» ANSI Joins: INNER JOIN
» Self-join example and syntax
» RIGHT JOIN example and syntax
» LEFT JOIN example and syntax
» INNER JOIN example and syntax
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 07th, 2009
|
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
-- or
SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
-- for example:
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
FULL JOIN Sales
ON Person.P_Id=Sales.P_Id
ORDER BY Person.LastName
-- alternate syntax:
SELECT *
FROM employee
LEFT JOIN department
ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM employee
RIGHT JOIN department
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL
|
