Outer Join And Its Types
An Natural join finds and returns matching data from two tables, but an outer -join finds and returns some matching data and some dissimilar data as well, from tables.
Conditions for outer join are similar as in natural join where Common columns for joining must have the same name.
Types of Outer Join
- Left outer Join
- Right Outer Join
- Full Outer Join
Consider the following two tables (Employee and Department) and explain all three types
1. Left Outer Join
Left outer join finds and returns the all matching rows from both tables and also returns the rows which are present in the left table but not present in right table.
SQL Syntax:
SELECT * From Table1 LEFT OUTER JOIN TABLE2 ON <Condition> WHERE <CONDITION>.
Symbolic Representation
Consider the following SQL query and and its results in the following table
SQL
SELECT emp_no, emp_name, dept_name, loc From employee LEFT OUTER JOIN Department ON employee.dept_no =department.dept_no
Output of above SQL
2. RIGHT OUTER JOIN
Right outer join finds and returns the all matching rows from both tables and also returns the rows which are present in the Right table but not present in left table.
SQL Syntax:
SELECT * From Table1 RIGHT OUTER JOIN TABLE2 ON <Condition> WHERE <CONDITION>.
Symbolic Representation
Consider the following SQL query and and its results in the following table
SQL
SELECT emp_no, emp_name, dept_name, loc From employee RIGHT OUTER JOIN TABLE2 ON employee.dept_no =department.dept_no
Output of above SQL
3. FULL OUTER JOIN
Full outer join finds and returns the all matching and dismatching rows from both tables.
SQL Syntax:
SELECT * From Table1 FULL OUTER JOIN TABLE2 ON <Condition> WHERE <CONDITION>.
Symbolic Representation
Consider the following SQL query and and its results in the following table
SQL
SELECT emp_no, emp_name, dept_name, loc From employee FULL OUTER JOIN TABLE2 ON employee.dept_no =department.dept_no
Output of above SQL
