DBMS Notes

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

Outer Join tables

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

Help Other’s By Sharing…

Contact Us

Burewala, Vehari, Punjab, Pakistan

cstaleem1@gmail.com

Website: CStaleem.com

Pin It on Pinterest