Alternatives
The effect of an outer join can also be obtained using a UNION ALL between an INNER JOIN and a SELECT of the rows in the "main" table that do not fulfill the join condition. For example
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;can also be written as
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID UNION ALL SELECT employee.LastName, employee.DepartmentID, CAST(NULL AS VARCHAR(20)) FROM employee WHERE NOT EXISTS (SELECT * FROM department WHERE employee.DepartmentID = department.DepartmentID)Read more about this topic: Join (SQL)
Famous quotes containing the word alternatives:
“The last alternatives they face
Of face, without the life to save,
Being from all salvation weaned
A stag charged both at heel and head:
Who would come back is turned a fiend
Instructed by the fiery dead.”
—Allen Tate (1899–1979)
“The literal alternatives to [abortion] are suicide, motherhood, and, some would add, madness. Consequently, there is some confusion, discomfort, and cynicism greeting efforts to “find” or “emphasize” or “identify” alternatives to abortion.”
—Connie J. Downey (b. 1934)
“Clearly, society has a tremendous stake in insisting on a woman’s natural fitness for the career of mother: the alternatives are all too expensive.”
—Ann Oakley (b. 1944)