Outer Join And Its Types
A 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 to those 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 all matching rows from both tables and also returns the rows that are present in the left table but not present in the right table.
SQL Syntax:
SELECT * From Table1 LEFT OUTER JOIN TABLE2 ON <Condition> WHERE <CONDITION>.
Symbolic Representation
Consider the following SQL query 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 the above SQL
2. RIGHT OUTER JOIN
Right outer join finds and returns all matching rows from both tables and also returns the rows that are present in the Right table but not present in the left table.
SQL Syntax:
SELECT * From Table1 RIGHT OUTER JOIN TABLE2 ON <Condition> WHERE <CONDITION>.
Symbolic Representation
Consider the following SQL query 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 the above SQL
3. FULL OUTER JOIN
Full outer join finds and returns all matching and mismatching 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 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 the above SQL
