Snippet Name: Self-join example and syntax
Description: A self-join is a way of joining a table to itself.
The example show could be the result of a query written to find all pairings of two employees in the same country, where all of the employee information is contained within a single large table.
Also see:
» ANSI Joins: FULL JOIN
» ANSI Joins: OUTER JOIN
» ANSI Joins: CROSS JOIN
» ANSI Joins: INNER JOIN
» FULL 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: PHP
Last Modified: March 07th, 2009
|
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
-- FOR this example, note that:
-- F and S are aliases FOR the first and second copies of the
-- employee table.
-- The condition F.Country = S.Country excludes pairings between
-- employees in different countries. The example question only
-- wanted pairs of employees in the same country.
-- The condition F.EmployeeID < S.EmployeeID excludes pairings
-- where the EmployeeIDs are the same.
-- F.EmployeeID < S.EmployeeID also excludes duplicate pairings.
-- The effect of outer joins can also be obtained using
-- correlated subqueries. FOR example:
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
-- this can also be written AS:
SELECT employee.LastName, employee.DepartmentID,
(SELECT department.DepartmentName
FROM department
WHERE employee.DepartmentID = department.DepartmentID )
FROM employee
|
