Sql server - What is outer
join? - Nov 20, 2008 at 18:00 PM by Rajmeet Ghai
What is outer join? Explain Left outer join, Right outer join and Full outer
join.
OUTER JOIN: In An
outer join, rows are returned even when there are no matches through the JOIN
criteria on the second table.
LEFT OUTER JOIN: A left outer join or a left join returns
results from the table mentioned on the left of the join irrespective of
whether it finds matches or not. If the ON clause matches 0 records from table
on the right, it will still return a row in the result—but with NULL in each
column.
Example: To display employees irrespective of whether they have
got bonus.
Select * From employee LEFT OUTER JOIN bonus ON employee.bonusID=bonus.bonusID
RIGHT OUTER JOIN: A right outer join or a right join returns
results from the table mentioned on the right of the join irrespective of
whether it finds matches or not. If the ON clause matches 0 records from table
on the left, it will still return a row in the result—but with NULL in each
column.
Example: To display Bonus irrespective of whether they are an
employee or not.
Select * From employee RIGHT OUTER JOIN bonus ON employee.bonusID=bonus.bonusID
FULL OUTER JOIN: A full outer join will combine results of both
left and right outer join. Hence the records from both tables will be displayed
with a NULL for missing matches from either of the tables.
Example: To display employees who have a bonus and to display
bonus even if he is not an employee.
Select * From employee FULL OUTER JOIN bonus ON
employee.bonusID=bonus.bonusID
|
